HI all,
@users = Node.find(:all, :select => "DISTINCT users.id",
:conditions
=> [" lft >= ? AND rgt <= ? AND nodes.statistics = 1",
@event.node.lft, @event.node.rgt], :include => { :users
=> :registrations }, :order => "users.first_name ASC" )
I want to select users from multiple nodes (groups that contain
users). Since 1 user can be a member of multiple nodes I only want to
have the user returned once
Eg:
Node 1
- user 1
- user 2
Node 2
- user 2
- user 3
This should return
Node 1
- user 1
- user 2
Node 2
- user 3
The sql results in:
SELECT `nodes`.`id` AS t0_r0, `nodes`.`parent_id` AS t0_r1,
`nodes`.`lft` AS t0_r2, `nodes`.`rgt` AS t0_r3, `nodes`.`name` AS
t0_r4, `nodes`.`node_type_id` AS t0_r5, `nodes`.`registration` AS
t0_r6, `nodes`.`statistics` AS t0_r7, `users`.`id` AS t1_r0,
`users`.`activation_key` AS t1_r1, `users`.`username` AS t1_r2,
`users`.`password` AS t1_r3, `users`.`first_name` AS t1_r4,
`users`.`last_name` AS t1_r5, `users`.`email` AS t1_r6,
`registrations`.`id` AS t2_r0, `registrations`.`event_id` AS t2_r1,
`registrations`.`user_id` AS t2_r2, `registrations`.`present` AS
t2_r3, `registrations`.`created_at` AS t2_r4,
`registrations`.`updated_at` AS t2_r5 FROM `nodes` LEFT OUTER JOIN
`nodes_users` ON `nodes_users`.node_id = `nodes`.id LEFT OUTER JOIN
`users` ON `users`.id = `nodes_users`.user_id LEFT OUTER JOIN
`registrations` ON registrations.user_id = users.id WHERE ( lft >= 1
AND rgt <= 6 AND nodes.statistics = 1) ORDER BY users.first_name ASC
I don''t see the Distinct in the SQL an this results in duplicate
users.
Someone knows why?
Thanks
Stijn
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
On 2 May 2008, at 16:18, Tarscher wrote:> > HI all, > > @users = Node.find(:all, :select => "DISTINCT users.id", :conditions > => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", > @event.node.lft, @event.node.rgt], :include => { :users > => :registrations }, :order => "users.first_name ASC" ) > > > I don''t see the Distinct in the SQL an this results in duplicate > users. >:include overwrites your :select. Do you actually want to use :joins ? Fred --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I do it so I don''t have to use a find in a iteration (performance). I guess I have to filter the duplicates out afterwards. Thanks Stijn On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 2 May 2008, at 16:18, Tarscher wrote: > > > > > HI all, > > > @users = Node.find(:all, :select => "DISTINCT users.id", :conditions > > => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", > > @event.node.lft, @event.node.rgt], :include => { :users > > => :registrations }, :order => "users.first_name ASC" ) > > > I don''t see the Distinct in the SQL an this results in duplicate > > users. > > :include overwrites your :select. Do you actually want to use :joins ? > > Fred--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On 2 May 2008, at 16:43, Tarscher wrote:> > I do it so I don''t have to use a find in a iteration (performance). > > I guess I have to filter the duplicates out afterwards. >You may find it easier to first select those users you''re interested in and then do User.find user_ids, :include => :registrations Fred> Thanks > Stijn > > On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 2 May 2008, at 16:18, Tarscher wrote: >> >> >> >>> HI all, >> >>> @users = Node.find(:all, :select => "DISTINCT >>> users.id", :conditions >>> => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", >>> @event.node.lft, @event.node.rgt], :include => { :users >>> => :registrations }, :order => "users.first_name ASC" ) >> >>> I don''t see the Distinct in the SQL an this results in duplicate >>> users. >> >> :include overwrites your :select. Do you actually want to >> use :joins ? >> >> Fred > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thank you for the help. Can you please elaborate on ''first select those users you''re interested in'' . Regards, Stijn On May 2, 5:45 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 2 May 2008, at 16:43, Tarscher wrote: > > > > > I do it so I don''t have to use a find in a iteration (performance). > > > I guess I have to filter the duplicates out afterwards. > > You may find it easier to first select those users you''re interested > in and then do > > User.find user_ids, :include => :registrations > > Fred > > > Thanks > > Stijn > > > On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > >> On 2 May 2008, at 16:18, Tarscher wrote: > > >>> HI all, > > >>> @users = Node.find(:all, :select => "DISTINCT > >>> users.id", :conditions > >>> => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", > >>> @event.node.lft, @event.node.rgt], :include => { :users > >>> => :registrations }, :order => "users.first_name ASC" ) > > >>> I don''t see the Distinct in the SQL an this results in duplicate > >>> users. > > >> :include overwrites your :select. Do you actually want to > >> use :joins ? > > >> Fred--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On 2 May 2008, at 16:54, Tarscher wrote:> > Thank you for the help. > > Can you please elaborate on ''first select those users you''re > interested in'' .users = Users.find :all, ... (without using include, possibly using :joins User.find users.collect(&:id), :include => :registrations Fred> > > > Regards, > Stijn > > On May 2, 5:45 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 2 May 2008, at 16:43, Tarscher wrote: >> >> >> >>> I do it so I don''t have to use a find in a iteration (performance). >> >>> I guess I have to filter the duplicates out afterwards. >> >> You may find it easier to first select those users you''re interested >> in and then do >> >> User.find user_ids, :include => :registrations >> >> Fred >> >>> Thanks >>> Stijn >> >>> On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >>> wrote: >>>> On 2 May 2008, at 16:18, Tarscher wrote: >> >>>>> HI all, >> >>>>> @users = Node.find(:all, :select => "DISTINCT >>>>> users.id", :conditions >>>>> => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", >>>>> @event.node.lft, @event.node.rgt], :include => { :users >>>>> => :registrations }, :order => "users.first_name ASC" ) >> >>>>> I don''t see the Distinct in the SQL an this results in duplicate >>>>> users. >> >>>> :include overwrites your :select. Do you actually want to >>>> use :joins ? >> >>>> Fred > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Many thanks. The user id select works. I want to get the users with a specific id and the joined registration table for a specific event. I also want to get the user when he did not register for an event When I do : players = User.find(users.collect(&:id), :include => :registrations, :conditions => ["registrations.event_id = ?", event.id ] ) I only get the users thar registered to the event I try: players = User.find(users.collect(&:id), :include => :registrations, :conditions => ["registrations.event_id = ? OR registrations.id = NULL", event.id ] ) but I get no result back SQL: SELECT `users`.`id` AS t0_r0, `users`.`activation_key` AS t0_r1, `users`.`username` AS t0_r2, `users`.`password` AS t0_r3, `users`.`first_name` AS t0_r4, `users`.`last_name` AS t0_r5, `users`.`email` AS t0_r6, `registrations`.`id` AS t1_r0, `registrations`.`event_id` AS t1_r1, `registrations`.`user_id` AS t1_r2, `registrations`.`present` AS t1_r3, `registrations`.`created_at` AS t1_r4, `registrations`.`updated_at` AS t1_r5 FROM `users` LEFT OUTER JOIN `registrations` ON registrations.user_id = users.id WHERE (`users`.`id` IN (1,3,2) AND (registrations.event_id = 3 OR registrations.id = null)) Someone has a solution? thanks Stin On May 2, 5:57 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 2 May 2008, at 16:54, Tarscher wrote: > > > > > Thank you for the help. > > > Can you please elaborate on ''first select those users you''re > > interested in'' . > > users = Users.find :all, ... (without using include, possibly > using :joins > User.find users.collect(&:id), :include => :registrations > > Fred > > > > > Regards, > > Stijn > > > On May 2, 5:45 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > >> On 2 May 2008, at 16:43, Tarscher wrote: > > >>> I do it so I don''t have to use a find in a iteration (performance). > > >>> I guess I have to filter the duplicates out afterwards. > > >> You may find it easier to first select those users you''re interested > >> in and then do > > >> User.find user_ids, :include => :registrations > > >> Fred > > >>> Thanks > >>> Stijn > > >>> On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > >>> wrote: > >>>> On 2 May 2008, at 16:18, Tarscher wrote: > > >>>>> HI all, > > >>>>> @users = Node.find(:all, :select => "DISTINCT > >>>>> users.id", :conditions > >>>>> => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", > >>>>> @event.node.lft, @event.node.rgt], :include => { :users > >>>>> => :registrations }, :order => "users.first_name ASC" ) > > >>>>> I don''t see the Distinct in the SQL an this results in duplicate > >>>>> users. > > >>>> :include overwrites your :select. Do you actually want to > >>>> use :joins ? > > >>>> Fred--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
use find_by_sql On Fri, May 2, 2008 at 11:10 AM, Tarscher <stijngoris-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Many thanks. The user id select works. > > I want to get the users with a specific id and the joined registration > table for a specific event. I also want to get the user when he did > not register for an event > > When I do : > players = User.find(users.collect(&:id), :include > => :registrations, :conditions => ["registrations.event_id = ?", > event.id ] ) > I only get the users thar registered to the event > > I try: > players = User.find(users.collect(&:id), :include > => :registrations, :conditions => ["registrations.event_id = ? OR > registrations.id = NULL", event.id ] ) > but I get no result back > > SQL: SELECT `users`.`id` AS t0_r0, `users`.`activation_key` AS t0_r1, > `users`.`username` AS t0_r2, `users`.`password` AS t0_r3, > `users`.`first_name` AS t0_r4, `users`.`last_name` AS t0_r5, > `users`.`email` AS t0_r6, `registrations`.`id` AS t1_r0, > `registrations`.`event_id` AS t1_r1, `registrations`.`user_id` AS > t1_r2, `registrations`.`present` AS t1_r3, > `registrations`.`created_at` AS t1_r4, `registrations`.`updated_at` AS > t1_r5 FROM `users` LEFT OUTER JOIN `registrations` ON > registrations.user_id = users.id WHERE (`users`.`id` IN (1,3,2) AND > (registrations.event_id = 3 OR registrations.id = null)) > > Someone has a solution? > > thanks > Stin > > > > > > > > > > On May 2, 5:57 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > wrote: > > On 2 May 2008, at 16:54, Tarscher wrote: > > > > > > > > > Thank you for the help. > > > > > Can you please elaborate on ''first select those users you''re > > > interested in'' . > > > > users = Users.find :all, ... (without using include, possibly > > using :joins > > User.find users.collect(&:id), :include => :registrations > > > > Fred > > > > > > > > > Regards, > > > Stijn > > > > > On May 2, 5:45 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > wrote: > > >> On 2 May 2008, at 16:43, Tarscher wrote: > > > > >>> I do it so I don''t have to use a find in a iteration (performance). > > > > >>> I guess I have to filter the duplicates out afterwards. > > > > >> You may find it easier to first select those users you''re interested > > >> in and then do > > > > >> User.find user_ids, :include => :registrations > > > > >> Fred > > > > >>> Thanks > > >>> Stijn > > > > >>> On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > >>> wrote: > > >>>> On 2 May 2008, at 16:18, Tarscher wrote: > > > > >>>>> HI all, > > > > >>>>> @users = Node.find(:all, :select => "DISTINCT > > >>>>> users.id", :conditions > > >>>>> => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", > > >>>>> @event.node.lft, @event.node.rgt], :include => { :users > > >>>>> => :registrations }, :order => "users.first_name ASC" ) > > > > >>>>> I don''t see the Distinct in the SQL an this results in duplicate > > >>>>> users. > > > > >>>> :include overwrites your :select. Do you actually want to > > >>>> use :joins ? > > > > >>>> Fred > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On 2 May 2008, at 18:10, Tarscher wrote:> > Many thanks. The user id select works. > > I want to get the users with a specific id and the joined registration > table for a specific event. I also want to get the user when he did > not register for an event > > When I do : > players = User.find(users.collect(&:id), :include > => :registrations, :conditions => ["registrations.event_id = ?", > event.id ] ) > I only get the users thar registered to the event > > I try: > players = User.find(users.collect(&:id), :include > => :registrations, :conditions => ["registrations.event_id = ? OR > registrations.id = NULL", event.id ] )NULL is special in sql (so NULL = NULL is false, NULL != NULL is false etc...). You need to use IS NULL. Fred> > > On May 2, 5:57 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 2 May 2008, at 16:54, Tarscher wrote: >> >> >> >>> Thank you for the help. >> >>> Can you please elaborate on ''first select those users you''re >>> interested in'' . >> >> users = Users.find :all, ... (without using include, possibly >> using :joins >> User.find users.collect(&:id), :include => :registrations >> >> Fred >> >> >> >>> Regards, >>> Stijn >> >>> On May 2, 5:45 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >>> wrote: >>>> On 2 May 2008, at 16:43, Tarscher wrote: >> >>>>> I do it so I don''t have to use a find in a iteration >>>>> (performance). >> >>>>> I guess I have to filter the duplicates out afterwards. >> >>>> You may find it easier to first select those users you''re >>>> interested >>>> in and then do >> >>>> User.find user_ids, :include => :registrations >> >>>> Fred >> >>>>> Thanks >>>>> Stijn >> >>>>> On May 2, 5:32 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >>>>> wrote: >>>>>> On 2 May 2008, at 16:18, Tarscher wrote: >> >>>>>>> HI all, >> >>>>>>> @users = Node.find(:all, :select => "DISTINCT >>>>>>> users.id", :conditions >>>>>>> => [" lft >= ? AND rgt <= ? AND nodes.statistics = 1", >>>>>>> @event.node.lft, @event.node.rgt], :include => { :users >>>>>>> => :registrations }, :order => "users.first_name ASC" ) >> >>>>>>> I don''t see the Distinct in the SQL an this results in duplicate >>>>>>> users. >> >>>>>> :include overwrites your :select. Do you actually want to >>>>>> use :joins ? >> >>>>>> Fred > --~--~---------~--~----~------------~-------~--~----~ > 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@googleg--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---