Topic: ActiveRecord DISTINCT usage?

Hi,

I'd like to do this in ActiveRecord/my model but I'm wrapped around the axel on the find syntax with distinct. And I've googled for examples but no joy.

select distinct(message_flow_id), message_flow_version_id
from flow_completion_records
order by message_flow_id, message_flow_version_id

Can someone help with a find example that uses the :distinct ?

Thanks,

Mike

Re: ActiveRecord DISTINCT usage?

You can do a find_by_sql() and write your own sql command. :distinct doesn't show up in my books' index, and I haven't used it myself.

Re: ActiveRecord DISTINCT usage?

If you do a find on your model, it will return an array if there is more than one item being returned.

In ruby, all arrays have the built in method uniq which returns an array with no duplicate values which it compares using the eql? method.

I believe you could override the eql? method in your model so it only compares the names of your objects and then use find(:all, :conditions => {...}).uniq to get the distinct names.

a = [1,2,2,2,3,3,4]
a.uniq => [1,2,3,4]

Re: ActiveRecord DISTINCT usage?

Thanks for those suggestions. I am writing the SQL straight up now, I was hoping there was a portable way to do it (Oracle/MySQL) because I use dates as well, and those are different.

The .uniq is fine as long as there are not a lot of records. I've got thousands to deal with so I don't want to return them to the app, then have ruby do it. That is work best done by the RDBMS.

Re: ActiveRecord DISTINCT usage?

i know this is late but i thought i would add in case anyone else get stuck on this...

FlowCompletionRecord.find(:all, :group => :message_flow_id, :order => "message_flow_id, message_flow_version_id"

Last edited by superwhite (2008-06-17 02:16:08)

Re: ActiveRecord DISTINCT usage?

something like this:

returnREC = flow_completion_records.find(:all, :select = "distinct(message_flow_id), message_flow_version_id", :order => "message_flow_id, message_flow_version_id
")

Re: ActiveRecord DISTINCT usage?

As someone posted above, the :group parameter in a find seems to act like distinct.

My blog: www.StartBreakingFree.com
My first rails app: www.UniversityTutor.com
My second rails app: www.BuyersVote.com

Re: ActiveRecord DISTINCT usage?

I think this depends on the type of database you're on, but ActiveRecord may take care of it if your database doesnt support this...

Model.find(:all, :select => 'DISTINCT field')

that will give you the unique fields. I tested this using MySQL.

Re: ActiveRecord DISTINCT usage?

I am trying to view a list of all requests for a schedule, but in my view, it repeats the schedule information for every request.  I just want to view the schedule information at the top, then see a list of all users who requested that schedule below.  I thought I needed the DISTINCT condition, but that may not be correct, since I don't exactly want distinct, I want to view the schedule only once, but view all requests for it. 

I've tried both " :group => :id "  and " :select => 'DISTINCT id' "... I sorta get the desired results, but when I use :group, it
only shows the first request, instead of all requests (as it should).

Is there another way to accomplish what I need, without putting all the schedule information in the partial - which then lists the same schedule over and over for every request.....

Thanks for your help!

Method:

 def list_allRequests
    if request.get?
      schedule = Schedule.find(params[:id])
      @request = Request.find(:all, :group => :schedule_id, #:select => 'DISTINCT schedule_id', 
                                               :include => :schedule, :conditions => ["schedule_id = ?", schedule])
      @num = Request.find(:all, :conditions => ["state_id = ? AND schedule_id = ?", '3', params[:id]]).nitems
      @remaining = schedule.capacity - @num
    end
  if @request.empty?
    redirect_to :back
    flash[:alert] = "There are no requests for this class."
  end
 end

View:

<div>
<h3>All requests for:</h3>
<% for request in @request do %>
<b>Course:</b>&nbsp;&nbsp;&nbsp;<%= request.schedule.course.title %><br>
<b>Date:</b>&nbsp;&nbsp;&nbsp;<%= request.schedule.coursedate.strftime("%m/%d/%y") %><br>
<b>Location:</b>&nbsp;&nbsp;&nbsp;<%= request.schedule.classroom.room_number %><br>
<b>Number Enrolled:</b>&nbsp;&nbsp;&nbsp;<%= @num %></b><br> 
<b>Remaining Seats:</b>&nbsp;&nbsp;&nbsp;<%= @remaining %></b><br>
<% end %>
<p>
</div>
<%= render :partial => "list_allRequests" %>  
<p>

Parital:

<div>
  <table id="request_list">
    <tr>
      <th class="sortable-text">Student</th>
      <th class="sortable-text">Request Status</th>
      <th class="sortable-text">Actions</th>      
    </tr>
    <tr class="<%= cycle('odd', 'even') %>">
    <% for request in @request do %>        
      <td><%= request.user.fullname %></td>
      <td><%= request.state.disposition %></td>
      <td><%= link_to_if(current_user.role_id == 2, "Cancel This Request", {:controller => :admin, :action => 'cancel_request', :id => request}) %> </td>
    </tr>
    <% end %>
  </table>  
</div>