Hello,
@users = User.find(:all, :include => {:user_data =>
:user_data_field}, :order => "username =
''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.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-oHC15RC7JGTNLxjTenLetw@public.gmane.org'' desc
Which is, I think, very bad.
As I see the eager loading uses "username" instead of "username =
''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.org''" at
odering. This will produce different results
as expected, even won''t include the user
''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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-oHC15RC7JGTNLxjTenLetw@public.gmane.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 On Dec 12, 2007, at 9:05 AM, Mage wrote:> > Hello, > > @users = User.find(:all, :include => {:user_data => > :user_data_field}, :order => "username = ''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.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-oHC15RC7JGTNLxjTenLetw@public.gmane.org'' desc > > > Which is, I think, very bad. > > As I see the eager loading uses "username" instead of "username > ''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.org''" at odering. This will produce different > results > as expected, even won''t include the user ''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.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 > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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:> 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-oHC15RC7JGTNLxjTenLetw@public.gmane.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
>
> On Dec 12, 2007, at 9:05 AM, Mage wrote:
>
>
>> Hello,
>>
>> @users = User.find(:all, :include => {:user_data =>
>> :user_data_field}, :order => "username =
''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.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-oHC15RC7JGTNLxjTenLetw@public.gmane.org'' desc
>>
>>
>> Which is, I think, very bad.
>>
>> As I see the eager loading uses "username" instead of
"username >>
''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.org''" at
odering. This will produce different
>> results
>> as expected, even won''t include the user
''someone-oHC15RC7JGTNLxjTenLetw@public.gmane.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
>>
>>
>>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---