WITH statement example

[Nicolas Évrard]

Hello,

I have started working on support for the WITH statement.
Although my first draft is working and generating the query I expect I
am not that happy with the python code I had to write to reach the
desired result.

So here is my example, maybe people here will have better ideas on how
to make this more pythonic / readable.

products = sql.Table('products')
sales = sql.Table('sales')
categories = sql.Table('categories')

c_sum = products.join(sales, condition=products.id==sales.product)\
.join(categories, type_='RIGHT',
condition=categories.id==products.category)\
.select(categories.id, categories.parent,
sql.aggregate.Sum(sales.quantity).as_('quantity'),
group_by=[categories.id, categories.parent])
c_sum_table = sql.Table('c_sum')

category_sum_table = sql.Table('category_sum')
category_sum = c_sum_table.select(c_sum_table.id, c_sum_table.parent,
c_sum_table.quantity) | category_sum_table.join(c_sum_table,
condition=category_sum_table.parent==c_sum_table.id)\
.select(c_sum_table.id, c_sum_table.parent,
category_sum_table.quantity)
category_sum.all_ = True

with_ = category_sum_table.select(
category_sum_table.id, sql.aggregate.Sum(category_sum_table.quantity),
group_by=[category_sum_table.id])\
.with_(recursive=True, c_sum=c_sum, category_sum=category_sum)

q = with_.join(categories,
condition=with_.id==categories.id).select()

--
Nicolas Évrard - B2CK SPRL
E-mail/Jabber: nicolas...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Cédric Krier]

It could be:


wq1 = With(Table(…).select(…))
wq2 = With(Table(…).select(…))

t = Table(…)
q3 = t.select(…,
where=t.id.in_(wq2),
with=[wq1, wq2],
with_recursive=True)


`With` will generate an alias automatically and will be of type
`FromItem`.
I'm wondering if the with_recursive could not be automatically deduced
from the sets of queries used but I think it will be very complicate and
expensive.
Don't forget that `With` could take any `Query` type.
Also I don't think we need a fast accessor on `Query` to return a `With`
instance.

Maybe a crazy idea but it could also be implemented to support this
form:

q1 = Table(…).select(…)
q2 = Table(…).select(…)
t = Table(…)

with q1 as wq1, q2 as wq2:
q3 = t.select(t.id.in_(wq2),
with=[wq1, wq2],
with_recursive=True)

Of course the with statement is kind of useless except for formatting.

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

[Nicolas Évrard]

* Cédric Krier [2014-10-13 11:28:15 +0200]

I don't see how this does any recursion. Can you elaborate further?

Because defining an identifier that makes a reference to itself is
AFAIK only possible with a function.

> t = Table(…)
> q3 = t.select(…,
> where=t.id.in_(wq2),
> with=[wq1, wq2],

It should be an ordereddict because with queries must be named and
their order is important.

But indeed this example is better than mine.

> with_recursive=True)
>
>
> `With` will generate an alias automatically and will be of type
> `FromItem`.

In fact I already took that into account.

> I'm wondering if the with_recursive could not be automatically deduced
> from the sets of queries used but I think it will be very complicate and
> expensive.

Since I do not understand your example I can not comment.

To display more beautifully the recursion I though about using
functions but it's always ugly.

def recursive_query(recursive=False):
if recursive:
return Query
else:
return InitialQuery | recursive_query(True)

> Don't forget that `With` could take any `Query` type.

I did not tested it but it should work.

> Also I don't think we need a fast accessor on `Query` to return a `With`
> instance.

OK.

> Maybe a crazy idea but it could also be implemented to support this
> form:
>
> q1 = Table(…).select(…)
> q2 = Table(…).select(…)
> t = Table(…)
>
> with q1 as wq1, q2 as wq2:
> q3 = t.select(t.id.in_(wq2),
> with=[wq1, wq2],
> with_recursive=True)
>
> Of course the with statement is kind of useless except for formatting.

I don't see the point in the with neither.

--
Nicolas Évrard - B2CK SPRL
E-mail/Jabber: nicolas...@b2ck.com

[Nicolas Évrard]

* Cédric Krier [2014-10-13 11:28:15 +0200]

Maybe indeed it could work:

wq1 = With()
wq2 = With()
wq2 |= Query(… wq2 …)

I'll work on that.

--
Nicolas Évrard - B2CK SPRL
E-mail/Jabber: nicolas...@b2ck.com

[Cédric Krier]

On 13 Oct 11:46, Nicolas Évrard wrote:
> * Cédric Krier [2014-10-13 11:28:15 +0200]

> > It could be:
> >
> >
> > wq1 = With(Table(…).select(…))
> > wq2 = With(Table(…).select(…))
>
> I don't see how this does any recursion. Can you elaborate further?
>
> Because defining an identifier that makes a reference to itself is
> AFAIK only possible with a function.

Recursive will be done:


t = With()
t.query = Values(1) | t.select(t.n + 1, where=t.n < 100)

where 'n' should be defined as a paramter of the With how is detail
implementation:
- could be the only fact it is used
- by analysing the selected columns

or

t = With('n')
t.query = Values(1) | t.select(t.n + 1, where=t.n < 100)

> > t = Table(…)
> > q3 = t.select(…,
> > where=t.id.in_(wq2),
> > with=[wq1, wq2],
>
> It should be an ordereddict because with queries must be named and
> their order is important.

Only order is important, name is given by alias manager.

> But indeed this example is better than mine.

> > I'm wondering if the with_recursive could not be automatically deduced
> > from the sets of queries used but I think it will be very complicate and
> > expensive.
>
> Since I do not understand your example I can not comment.

> > Maybe a crazy idea but it could also be implemented to support this
> > form:
> >
> > q1 = Table(…).select(…)
> > q2 = Table(…).select(…)
> > t = Table(…)
> >
> > with q1 as wq1, q2 as wq2:
> > q3 = t.select(t.id.in_(wq2),
> > with=[wq1, wq2],
> > with_recursive=True)
> >
> > Of course the with statement is kind of useless except for formatting.
>
> I don't see the point in the with neither.

formatting but of course it will be better if they could be adding to
select instance automatically. Maybe with a global WithManager like for
Alias.

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

[Cédric Krier]

On 13 Oct 12:20, Cédric Krier wrote:
> On 13 Oct 11:46, Nicolas Évrard wrote:
> > * Cédric Krier [2014-10-13 11:28:15 +0200]
> > > It could be:
> > >
> > >
> > > wq1 = With(Table(…).select(…))
> > > wq2 = With(Table(…).select(…))
> >
> > I don't see how this does any recursion. Can you elaborate further?
> >
> > Because defining an identifier that makes a reference to itself is
> > AFAIK only possible with a function.
>
> Recursive will be done:
>
>
> t = With()
> t.query = Values(1) | t.select(t.n + 1, where=t.n < 100)

I made a quick patch to implement the missing Values:
https://code.google.com/p/python-sql/issues/detail?id=23

I still have problem with the unittest to test the warning.