Possible bug in eager loading

[Mage]

Hello,

    @users = User.find(:all, :include => {:user_data =>

:user_data_field}, :order => “username = ‘[email protected]’ desc”,
:limit => 50)

This produces:

User Load IDs For Limited Eager Loading (74.648762) SELECT * FROM
(SELECT DISTINCT ON (users.id) users.id, username AS alias_0 FROM users
LEFT OUTER JOIN user_data ON user_data.user_id = users.id LEFT OUTER
JOIN user_data_fields ON user_data_fields.id = user_data.field_id ) AS
id_list ORDER BY id_list.alias_0 DESC LIMIT 50

User Load Including Associations (0.026093) SELECT users.“id” AS
t0_r0, users.“username” AS t0_r1, users.“password” AS t0_r2, , user_data.“id” AS t1_r0, user_data.“entered_when” AS
t1_r1, FROM users LEFT OUTER JOIN user_data ON
user_data.user_id = users.id LEFT OUTER JOIN user_data_fields ON
user_data_fields.id = user_data.field_id WHERE users.id IN () ORDER BY username = ‘[email protected]’ desc

Which is, I think, very bad.

As I see the eager loading uses “username” instead of “username =
‘[email protected]’” at odering. This will produce different results
as expected, even won’t include the user ‘[email protected]’ which
was the primary goal of this ordering.

I think that the web-programmer shouldn’t check the log for query
modifications like this. Is there something I miss?

I am using Rails 1.2.6, Gentoo, Amd64.

   Mage

[Mage]

Hi Mage,

I’m not sure which db engine you’re using, but you might try
rewriting your order by clause to use a case. Something like

case when username=‘[email protected]’ then 1 else 0 end desc

I’m shooting from the hip on this and am not sure which databases
support using case in the order by clause. I’m pretty sure something
like this will work on SQL Server, but it may not anywhere else.

If that doesn’t work, you might need to resort to find_by_sql and
construct the query yourself.

Peace,
Phillip

[Mage]

Dear Phillip,

it even gets worst.

PGError: ERROR: syntax error at or near “AS”
LINE 1: …ROM (SELECT DISTINCT ON (users.id) users.id, case AS
alias_0…
^
: SELECT * FROM (SELECT DISTINCT ON (users.id) users.id, case AS alias_0
FROM users LEFT OUTER JOIN user_data ON user_data.user_id = users.id
LEFT OUTER JOIN user_data_fields ON user_data_fields.id =
user_data.field_id WHERE (exists (select id from
migration_active_job_offers where user_id = users.id)) ) AS id_list
ORDER BY id_list.alias_0 DESC LIMIT 50

Postgresql supports “ORDER BY case when username = ‘xxxxx’ then 1 else 0
end” format.

I still think it’s a bug and it really can generate invalid query
results.

   Mage