Subquery support and `raw` objects

[moskytw]

Subquery support and `raw` objects

[moskytw]

It indeed is a problem. I will try to fix it. Thanks for reporting it. :)

[moskytw]

It is super crazy if you build subquery with the current stuff:

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from mosql.util import raw, paren, identifier
from mosql.query import select

def as_(a, b):
    return '%s AS %s' % (identifier(a), identifier(b))

print select(
    select = 'name',
    table = raw(as_(
        raw(paren(
            select('person', select='name')
        )),
        'name'
    ))
)
# -> SELECT "name" FROM (SELECT "name" FROM "person") AS "name"

I will find the best way to integrate this feature into MoSQL.

[uranusjr]

With #12 and #14, the "person" example can now be written with a slightly different (simpler) syntax:

print select(select='name', table=as_(paren(select('person', select='name')), 'name'))

And the funky example I provided becomes

x = select('fruits', select=('type', as_(min('price'), 'minprice')), group_by=('type',))
join_x = join(as_(paren(x), 'x'), {'fruits.type': 'x.type', 'fruits.price': 'x.minprice'})
select('fruits', select=('fruits.type', 'fruits.variety', 'fruits.price'), joins=(join_x,))

resulting

SELECT "fruits"."type", "fruits"."variety", "fruits"."price" FROM "fruits"
    INNER JOIN (SELECT "type", MIN("price") AS "minprice" FROM "fruits" GROUP BY "type") AS "x" 
    ON "fruits"."price" = "x"."minprice" AND "fruits"."type" = "x"."type"

Notice that you'll need to provide the paren calls for yourself. I'll look into how we can simplify these calls. At least the as_ functions should have the ability to add parentheses automatically.

[moskytw]

Thanks @uranusjr. The commits are merged in dev branch now. :D