select distinct not working

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

: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

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

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

use find_by_sql

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