Using RowNumber

[Hendrik Brandes]

Hello,

I am using python-sql within tryton und want to create a query, which selects data across several tables using joins.

My problem is, that in tryton the ID-field is necessary and I cannot create a proper-ID field, which corresponds to the group by clause, e.g. which will not generate double entries.

I thought about using the RowNumber-function to create a individual ID for each line. But RowNumber is a WindowsFunction and I cannot see, how to use this. 

My Select-Statements sounds like this:

line.join(product, 'LEFT', condition=line.product == product.id
                ).join(product_template, 'LEFT', condition=product.template == product_template.id
                ).join(category, 'LEFT', condition=product_template.category == category.id
                ).join(sale, 'LEFT', condition=line.sale == sale.id
                ).join(prop, 'LEFT', condition=product_template.id == ToNumber( Substring(prop.res,18), '999999' )
                ).select(
                  #Max( product_template.category * 1000000 ).as_("id"),
                Max(line.create_uid).as_('create_uid'),
                Max(line.create_date).as_('create_date'),
                Max(line.write_uid).as_('write_uid'),
                Max(line.write_date).as_('write_date'),
                year_column,
                month_column,
                product_template.category,
                Sum(line.quantity).as_('quantity'),
                Sum(line.unit_price * line.quantity).as_('untaxed_amount'),
                Sum( line.unit_price * line.quantity - line.quantity * ToNumber( prop.value, '9999.999') ).as_('rendite'),
                where=(prop.field==778),
                group_by=(year_column, month_column, product_template.category))

At the point Max( product_template.category ), I want to have the RowNumber Function.

Thank you and best regards,

Hendrik Brandes

[Sergi Almacellas Abellana]

El 20/11/15 a les 09:37, Hendrik Brandes ha escrit:

> Hello,
>
> I am using python-sql within tryton und want to create a query, which
> selects data across several tables using joins.
>
> My problem is, that in tryton the ID-field is necessary and I cannot
> create a proper-ID field, which corresponds to the group by clause, e.g.
> which will not generate double entries.
>
> I thought about using the RowNumber-function to create a individual ID
> for each line. But RowNumber is a WindowsFunction and I cannot see, how
> to use this.
>
> My Select-Statements sounds like this:
>
> |
> line.join(product,'LEFT',condition=line.product ==product.id
> ).join(product_template,'LEFT',condition=product.template==product_template.id
> ).join(category,'LEFT',condition=product_template.category ==category.id
> ).join(sale,'LEFT',condition=line.sale ==sale.id
> ).join(prop,'LEFT',condition=product_template.id

> ==ToNumber(Substring(prop.res,18),'999999')

> ).select(
> #Max( product_template.category * 1000000 ).as_("id"),
> Max(line.create_uid).as_('create_uid'),
> Max(line.create_date).as_('create_date'),
> Max(line.write_uid).as_('write_uid'),
> Max(line.write_date).as_('write_date'),
> year_column,
> month_column,
> product_template.category,
> Sum(line.quantity).as_('quantity'),
> Sum(line.unit_price *line.quantity).as_('untaxed_amount'),
> Sum(line.unit_price *line.quantity -line.quantity
> *ToNumber(prop.value,'9999.999')).as_('rendite'),
> where=(prop.field==778),
>
> group_by=(year_column,month_column,product_template.category))
> |
>
>
>
> At the point Max( product_template.category ), I want to have the
> RowNumber Function.
>

You can create your own functions with:

from sql.functions import Function

class RowNumber(Function):
__slots__ ()
_function = 'ROW_NUMBER'


and then call it normaly:

table.select(RowNumber())

will produce:

SELECT ROW_NUMBER() FROM table;

Hope it helps!

> Thank you and best regards,
> Hendrik Brandes

--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Cédric Krier]

On 2015-11-20 00:37, Hendrik Brandes wrote:
> At the point Max( product_template.category ), I want to have the RowNumber
> Function.

Since 0.7, python-sql has window function and in other the row_number
function.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Hendrik Brandes]

Am Freitag, 20. November 2015 13:50:03 UTC+1 schrieb Cédric Krier:

On 2015-11-20 00:37, Hendrik Brandes wrote:
> At the point Max( product_template.category ), I want to have the RowNumber
> Function.

Since 0.7, python-sql has window function and in other the row_number
function.

Can you please post a line of code, where I can understand, how to use this window function?
Actual, I cannot explain to myself, how to work with row_number().
 

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com

 

Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Cédric Krier]

On 2015-11-23 06:43, Hendrik Brandes wrote:
>
>
> Am Freitag, 20. November 2015 13:50:03 UTC+1 schrieb Cédric Krier:
> >
> > On 2015-11-20 00:37, Hendrik Brandes wrote:
> > > At the point Max( product_template.category ), I want to have the
> > RowNumber
> > > Function.
> >
> > Since 0.7, python-sql has window function and in other the row_number
> > function.
> >
>
> Can you please post a line of code, where I can understand, how to use this
> window function?
> Actual, I cannot explain to myself, how to work with row_number().

You can look at the Window tests:
http://hg.tryton.org/python-sql/file/1d5a05fdbd6c/sql/tests/test_select.py#l356