Horacio J. Peña
2006-Aug-22 23:03 UTC
[Rails] bug on ez_where plugin when handling multiple associations to the same table
Hola! Don''t know if this is the right forum, if not please let me know where it is. I have something like: $ cat app/models/message.rb class Message < ActiveRecord::Base belongs_to :user_from, :class_name => ''User'', :foreign_key => ''from_user_id'' belongs_to :user_to, :class_name => ''User'', :foreign_key => ''to_user_id'' end $ cat app/models/user.rb class User < ActiveRecord::Base has_many :messages_sent, :class_name => ''messages'', :foreign_key => ''from_user_id'' has_many :messages_recv, :class_name => ''messages'', :foreign_key => ''to_user_id'' end>> Message.find(:all, :include => [:user_from, :user_to])=> [#<Message:0x40ae04e4 @attributes={"to_user_id"=>"2", "id"=>"1", "from_user_id"=>"1"}, @user_to=#<User:0x40adfd8c @attributes={"name"=>"Juan", "id"=>"2"}>, @user_from=#<User:0x40adff94 @attributes={"name"=>"Pepe", "id"=>"1"}>>]>> Message.ez_find(:all, :include => [:user_from, :user_to]) do |m,f,t| f.name == ''Pepe''; t.name == ''Juan'' end=> [] The SQL generated by ez_find is: SELECT messages.`id` AS t0_r0, messages.`from_user_id` AS t0_r1, messages.`to_user_id` AS t0_r2, users.`id` AS t1_r0, users.`name` AS t1_r1, user_tos_messages.`id` AS t2_r0, user_tos_messages.`name` AS t2_r1 FROM messages LEFT OUTER JOIN users ON users.id = messages.from_user_id LEFT OUTER JOIN users user_tos_messages ON user_tos_messages.id = messages.to_user_id WHERE ((users.name = ''Pepe'') AND (users.name = ''Juan'')) while it should be SELECT messages.`id` AS t0_r0, messages.`from_user_id` AS t0_r1, messages.`to_user_id` AS t0_r2, users.`id` AS t1_r0, users.`name` AS t1_r1, user_tos_messages.`id` AS t2_r0, user_tos_messages.`name` AS t2_r1 FROM messages LEFT OUTER JOIN users ON users.id = messages.from_user_id LEFT OUTER JOIN users user_tos_messages ON user_tos_messages.id = messages.to_user_id WHERE ((users.name = ''Pepe'') AND (user_tos_messages.name = ''Juan'')) (using revision 146 from http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where) Thanks! HoraPe --- Horacio J. Peña horape-Zlwz598M9fGnYQj17FrgxVAUjnlXr6A1@public.gmane.org horape-03kPrFAwsvU3uPMLIKxrzw@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Ezra Zygmuntowicz
2006-Aug-22 23:37 UTC
[Rails] Re: bug on ez_where plugin when handling multiple associations to the same table
Hi~ On Aug 22, 2006, at 4:03 PM, Horacio J. Peña wrote:> > Hola! > > Don''t know if this is the right forum, if not please let me know where > it is. > > I have something like: > > $ cat app/models/message.rb > class Message < ActiveRecord::Base > belongs_to :user_from, :class_name => ''User'', :foreign_key => > ''from_user_id'' > belongs_to :user_to, :class_name => ''User'', :foreign_key => > ''to_user_id'' > end > $ cat app/models/user.rb > class User < ActiveRecord::Base > has_many :messages_sent, :class_name => ''messages'', :foreign_key > => ''from_user_id'' > has_many :messages_recv, :class_name => ''messages'', :foreign_key > => ''to_user_id'' > end > >>> Message.find(:all, :include => [:user_from, :user_to]) > => [#<Message:0x40ae04e4 > @attributes={"to_user_id"=>"2", "id"=>"1", "from_user_id"=>"1"}, > @user_to=#<User:0x40adfd8c @attributes={"name"=>"Juan", "id"=>"2"}>, > @user_from=#<User:0x40adff94 @attributes={"name"=>"Pepe", > "id"=>"1"}>>] > >>> Message.ez_find(:all, :include => [:user_from, :user_to]) do | >>> m,f,t| f.name == ''Pepe''; t.name == ''Juan'' end > => [] > > The SQL generated by ez_find is: > > SELECT messages.`id` AS t0_r0, messages.`from_user_id` AS t0_r1, > messages.`to_user_id` AS t0_r2, users.`id` AS t1_r0, users.`name` > AS t1_r1, user_tos_messages.`id` AS t2_r0, user_tos_messages.`name` > AS t2_r1 > FROM messages > LEFT OUTER JOIN users ON users.id = messages.from_user_id > LEFT OUTER JOIN users user_tos_messages ON user_tos_messages.id > = messages.to_user_id > WHERE ((users.name = ''Pepe'') AND (users.name = ''Juan'')) > > while it should be > > SELECT messages.`id` AS t0_r0, messages.`from_user_id` AS t0_r1, > messages.`to_user_id` AS t0_r2, users.`id` AS t1_r0, users.`name` > AS t1_r1, user_tos_messages.`id` AS t2_r0, user_tos_messages.`name` > AS t2_r1 > FROM messages > LEFT OUTER JOIN users ON users.id = messages.from_user_id > LEFT OUTER JOIN users user_tos_messages ON user_tos_messages.id > = messages.to_user_id > WHERE ((users.name = ''Pepe'') AND (user_tos_messages.name = ''Juan'')) > > (using revision 146 from http://opensvn.csie.org/ezra/rails/plugins/ > dev/ez_where) > > Thanks! > HoraPe > --- > Horacio J. Peña > horape-Zlwz598M9fGnYQj17FrgxVAUjnlXr6A1@public.gmane.org > horape-03kPrFAwsvU3uPMLIKxrzw@public.gmane.orgCan you please try this with the new version? the one that you linked to is very old at this point. We improved the handling of :includ in the new one. I am still not certain if it will handle this correctly since self referential relationships are hard to handle in the correct way. But please try the latest version and let me know how it works for you. -Ezra svn://rubyforge.org/var/svn/ez-where --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Horacio J. Peña
2006-Aug-23 00:29 UTC
[Rails] Re: bug on ez_where plugin when handling multiple associations to the same table
Hola!> > The SQL generated by ez_find is:> > FROM messages > > LEFT OUTER JOIN users ON users.id = messages.from_user_id > > LEFT OUTER JOIN users user_tos_messages ON user_tos_messages.id > > = messages.to_user_id > > WHERE ((users.name = ''Pepe'') AND (users.name = ''Juan''))> > while it should be> > FROM messages > > LEFT OUTER JOIN users ON users.id = messages.from_user_id > > LEFT OUTER JOIN users user_tos_messages ON user_tos_messages.id > > = messages.to_user_id > > WHERE ((users.name = ''Pepe'') AND (user_tos_messages.name = ''Juan''))> Can you please try this with the new version? the one that you > linked to is very old at this point. We improved the handling > of :includ in the new one. I am still not certain if it will handle > this correctly since self referential relationships are hard to > handle in the correct way. But please try the latest version and let > me know how it works for you.Tried it and it''s the same. Notice that the relationship isn''t self referential, but instead there are two relations to the same table. Saludos! HoraPe --- Horacio J. Peña horape-Zlwz598M9fGnYQj17FrgxVAUjnlXr6A1@public.gmane.org horape-03kPrFAwsvU3uPMLIKxrzw@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---