Python-sql > WindowsFunction > RowNumber

Support Developer
1

Hi,
Below is the python-sql query, how to user RowNumber with WindowFunction.
It need to use window as parameter. But how to define?

Please help

query = move.join(product,
condition=move.product == product.id
).join(template,
condition=product.template == template.id
).join(flocation,
condition=move.from_location == flocation.id
).join(tlocation,
condition=move.to_location == tlocation.id
).join(pabean, type_=‘LEFT’,
condition=move.document_pabean == pabean.id
).join(kpbc, type_=‘LEFT’,
condition=pabean.kpbc == kpbc.id
).select(
RowNumber(window=move.id).as_(‘row_number’),
move.id.as_(‘id’),
move.create_uid,
move.create_date,
move.write_uid,
move.write_date,
move.shipment.as_(‘shipment’),
move.state.as_(‘state’),
move.effective_date,
move.currency,
move.unit_price,
move.uom,
move.quantity,
move.product,
move.id.as_(‘move’),
template.name.as_(‘name’),
product.code.as_(‘product_code’),
template.name.as_(‘product_name’),
Coalesce(pabean.id, None).as_(‘document_pabean’),
Coalesce(pabean.number, None).as_(‘no_pengajuan’),
Coalesce(pabean.no_pendaftaran, None).as_(‘no_pendaftaran’),
Coalesce(pabean.tgl_daftar, None).as_(‘tgl_pengajuan’),
Coalesce(pabean.tgl_aju, None).as_(‘tgl_pengajuan’),
Coalesce(pabean.pb_type, None).as_(‘pb_type’),
Coalesce(kpbc.code, None).as_(‘kode_kppbc’),
where=(flocation.type == ‘supplier’) | (flocation.type == ‘customer’)
)

Error Message for Window:

Traceback (most recent call last):
File “/home/coder/project/env/lib/python3.6/site-packages/trytond/protocols/dispatcher.py”, line 186, in dispatch
result = rpc.result(meth(*c_args, **c_kwargs))
File “/home/coder/project/env/lib/python3.6/site-packages/trytond/model/modelsql.py”, line 1243, in search
tables, expression = cls.search_domain(domain)
File “/home/coder/project/env/lib/python3.6/site-packages/trytond/model/modelsql.py”, line 1409, in search_domain
tables[None] = (cls.table(), None)
File “/home/coder/project/env/lib/python3.6/site-packages/trytond/model/modelsql.py”, line 199, in table
return cls.table_query()
File “/home/coder/project/env/lib/python3.6/site-packages/trytond/modules/plb_laporan/lap_pemasukan_barang.py”, line 182, in table_query
RowNumber(WindowFunction(window=move.id)).as(‘row_number’),
File “/home/coder/project/env/lib/python3.6/site-packages/sql/functions.py”, line 497, in init
self.window = kwargs[‘window’]
File “/home/coder/project/env/lib/python3.6/site-packages/sql/functions.py”, line 518, in window
assert isinstance(value, Window)
AssertionError

2

You must use a Window object into RowNumber:

from sql import Window
...
RowNumber(window=Window([])).as_('row_number')
3

Normally row_number over [column_name].

Where to put the Column Name in “Window()”?

4

Check the function with window test: python-sql: e7018241e26d sql/tests/test_functions.py

What come after the OVER is not a column name but a partition. python-sql creates always an alias WINDOW which contains the PARTITION definition.
If you want to partition over the move.id, it will be:

RowNumber(window=Window([move.id])).as_('row_number')
1 Like
5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.