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
On 2 May 2008, at 16:18, Tarscher wrote:
users.
:include overwrites your :select. Do you actually want to use :joins ?
Fred
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 C. [email protected]
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
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 C. [email protected]
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
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 C. [email protected]
On 2 May 2008, at 18:10, Tarscher wrote:
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