theKindOfMe

November 10, 2010

Retrieving DISTINCT results with Active Record on Complex Queries

Filed under: Uncategorized — Tags: — yasi8h @ 8:53 am

If you want get distinct results with Active Record, there are some ways to go about it. The simplest is to just use SQL (ie: by using find_by_sql…).

But if you have a query like the following and do not want to switch to pure SQL. You could try the following.

Tables

User,
Publications,
PublicationsUsers – This helps maintain a ‘Users can have one or more Publications’ relationship between Publications and Users.

The following query gets all the users associated with all the publications of a given user.

@my_publication_ids is a array of publication ids like [0,5,1,21] and role is a string like ‘Admin’.

But the following statement will give you duplicate results. As in the collection returned can include the same user more than once. This is not the expected behavior.

User.all(:joins => :publications, :conditions => {:publications_users => {:publication_id => @my_publication_ids}, :users => {:role => role}})

Adding a :select => ‘DISTINCT “users”.*’ clause will solve the problem. Here note that we are interested in retrieving distinct users. Hence we have to apply the select distinct on users columns. Which looks like “users”.*

User.all(:select => ‘DISTINCT “users”.*’, :joins => :publications, :conditions => {:publications_users => {:publication_id => @my_publication_ids}, :users => {:role => role}})

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: