SelectResults, counts and one-to-many relationships

[King Simon-NFHD78]

Hi again,

I'm using the SelectResults mod, and I think there may be a bug with the
way one-to-many relationships are handled. The attached file should show
the problem.

In the example, I have two mapped classes, Department and Employee, such
that a department has many employees. In the 'check' function I am
trying to select all the departments which have an employee whose name
starts with 'J'. I am also ordering the results by Department name,
descending.

If I pass distinct=False, then the result of the count() is wrong - I
assume that is the expected behaviour, and not a bug. However, if I pass
distinct=True, the order_by does not appear in the nested
('tbl_row_count') query, so the wrong row is returned.

In case it makes a difference, I've tested this with revisions 2283 and
2300 of the trunk.

Cheers,

Simon

[sdo...@sistechnology.com]

hey i tried this, and looking at the src:
- u missed one .split() on the dept2 string of employess
- print list(d) then doing d[0] - isn't queryresult supposed to be
generator, so what is d[0] after wholly iterating it..

Except that d.count() != len(d) in the use_distinct=False case, all
else seems ok - i do have order_by on both, sizes are ok, order is
ok...
rev 2291/2300

ciao
svil

[Michael Bayer]

the "non-distinct" parts of this test fail on postgres, because its
not doing the nesting thing:

sqlalchemy.exceptions.SQLError: (ProgrammingError) column
"departments.name" must appear in the GROUP BY clause or be used in an
aggregate function
'SELECT count(departments.department_id) \nFROM departments,
employees \nWHERE departments.department_id = employees.department_id
AND employees.name LIKE %(employees_name)s ORDER BY departments.name
DESC' {'employees_name': 'J%'}

generally, the combination youre doing here just barely works. if you
really want to count from a very specific query, you should be
counting from the query itself, not using the ORM.

for the order by getting removed during the select, that seemed to be
an optimization that got stuck in there and since this is a really
fringe use case its never come up, so i removed it and added your test
case (only with distinct=True) in rev 2301.

On Feb 5, 12:46 pm, "King Simon-NFHD78" <simon.k...@motorola.com>
wrote:

> one_to_many_test.py
> 3KDownload

[Michael Bayer]

I added distinct() to selectresults as a method and made the unit test
a little clearer (since i dont like relying on the selectresults
"mod")...

q = sess.query(Department)
d = SelectResults(q)
d =
d.join_to('employees').filter(Employee.c.name.startswith('J'))
d = d.distinct()
d = d.order_by([desc(Department.c.name)])

[King Simon-NFHD78]

Michael Bayer wrote:
>
> I added distinct() to selectresults as a method and made the
> unit test a little clearer (since i dont like relying on the
> selectresults "mod")...
>
> q = sess.query(Department)
> d = SelectResults(q)
> d =
> d.join_to('employees').filter(Employee.c.name.startswith('J'))
> d = d.distinct()
> d = d.order_by([desc(Department.c.name)])
>
>

...and...

>
> for the order by getting removed during the select, that
> seemed to be an optimization that got stuck in there and
> since this is a really fringe use case its never come up, so
> i removed it and added your test case (only with
> distinct=True) in rev 2301.
>

I think you're slipping - I had to wait a whole three and a half hours
for this fix ;-) Seriously, thanks again,

Simon