possible bug in eager loading

[Mage]

Hello,

        @users = User.find(:all, :include => {:user_data => :user_data_field}, :order => "username = 'someone@somewhere.org' 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, <lots of more fields>, user_data."id" AS t1_r0, user_data."entered_when" AS t1_r1, <lots of more fields> 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 (<sniffed a lots of ids>) ORDER BY username = 'someone@somewhere.org' desc

Which is, I think, very bad.

As I see the eager loading uses "username" instead of "username = 'someone@somewhere.org'" at odering. This will produce different results as expected, even won't include the user 'someone@somewhere.org' 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

[Phillip_Koebbe]

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='someone@somewhere.org' 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

Phillip Koebbe wrote: