We use func.count().over() in order to help support result pagination.
When attempting to limit the result set, I have found that if other tables
are being joined (for the where clause, but not selected), then I need to
add DISTINCT to the query or else the Cartesian result of my query messes
up LIMIT. (There are, say, 72 rows returned, where this only represents 17
distinct records, for example.)
For example, a query may look like this:
select distinct
count(*) over () as recordcount, tablea.colx, tableb.coly
from tablea, tableb
where ....
limit 100
This doesn't *quite* work because the analytical window function count(*)
over() is applied *before* the distinct, so the count returns the wrong
number (a Cartesian effect, returning 72 instead of 17, in this example).
I have two potential solutions:
Solution A:
================
Group by all columns (yielding the same effect as "distinct"), but which
makes the window analytical function process *after* the group by and
yields the correct count (17 instead of 72):
select count(*) over () as recordcount, tablea.colx, tableb.coly
from tablea, tableb
where ...
group by tablea.colx, tableb.coly *[all columns]*
limit 100
OR Solution B:
================
Put the "count(*) over ()" in an outer select, like this:
select count(*) over () as recordcount, anon.*
from (
select distinct tablea.colx, tableb.coly
from tablea, tableb
) as anon
limit 100
Either solution yields the correct answer I believe, but I'm having
difficulty translating the SQL that I know will work into sqlalchemy land.
For Solution A, in the case of wanting to group by, I don't know how to get
the full list of all selected columns to add to the group_by in such a way
that even joinedload() will be included in the group by:
q = Session.query(class).join(joins).filter(...).option(joinedload(...))
q = q.group_by(* ??? How to tell sqlalchemy to group by all selected
columns, even those which will be join loaded ???* )
q = q.add_column(func.count().over().label('recordcount'))
For Solution B, I don't know how to wrap my query in an outer select
(similar to the LIMIT implementation for Oracle) in a way that will still
allow sqlalchemy to extract rows into instances:
This renders the correct SQL, I think:
qry = Session.query(qry.with_labels().statement,
func.count().over().label('recordcount'))
But I'm using SQL statement here so sqlalchemy won't translate result rows
into object instances.
Can you point me in the right direction for one of these 2 solutions,
please?
Many thanks,
Kent
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
