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

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

use find_by_sql

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs