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.
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))
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.