From: Daniel N <has.sox@...>
Date: 2006-07-24T20:08:46+09:00
Subject: Re: Bad query?

------=_Part_152838_5669910.1153739322288
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/24/06, raffir@gmail.com <raffir@gmail.com> wrote:
>
> I have a find() query inside a for loop. Basically, I want the loop to
> go through all elements in User.accounts and find all elements in
> Accounts that have the same name as each element (to see other users
> who have accounts with the same name).
>
> Right now my code is as follows:
>
>         <table>
>         <%@session['user'].accounts.each do |i| %>
>         <%Accounts.find(:all, :conditions => ["coursenumber LIKE ?",
> "%#{i.coursenumber}%"]).each do |account| %>
>
>                 <tr>
>                 <td><%= account.coursename %></td>
>                 <td><%= account.coursenumber %></td>
>                 <td><%= account.user_id%></td>
>                         <td><%= account.first_name%> <%=
> account.last_name%></td>
>                         <td><%= link_to 'Show', :controller=>'user',
> :action=>'userdetails',
> :id=> account.user_id %></td>
>                 </tr>
>
>                 <%end%>
>         <% end %>
> </table>
>
> This has the effect of outputting every single account in the Accounts
> table. I'd like to restrict it to just outputting those accounts with
> the same course name/number as each one of the User's.
>
>
> Thanks a lot,
>
>    - Raffi.


You could simplify this a lot by using a db IN condition.

Does this need to be a LIKE condition or are the course numbers in fact the
same.

Assuming that they are the same

<% course_numbers = session['user'].accounts.map { |a| a.coursenumber }.uniq
%>
<% Account.find( :all, :conditions => [ "coursenumber IN (?)",
course_numbers] ).each do |acc| %>
.....
<% end %>

If they really do need a LIKE condition then I would only want to hit the db
once.  Who knows how many accounts there may be.  You may have a better time
with the ez_where plugin by Ezra ( http://brainspl.at ) but one way could
be.

<% course_numbers = session['user'].accounts.map { |a| a.coursenumber }.uniq
%>
<% course_query = course_numbers.inject([]) { |arr,cn| arr << "coursenumber
LIKE  '%#{coursenumber}%' "} %>
<% Account.find(:all, :conditions => course_query.join( " OR " ) ).each do
|acc| %>
....
<% end %>

This is probably not too good to have this logic in your view anyway tho.
Perhaps you should make this into a class method for account

in account.rb

def Account.find_similar_accounts( *accounts )
  accounts = accounts.flatten
  course_numbers = accounts.map { |a| a.coursenumber }.uniq
  Account.find(:all, :conditions => [ "coursenumber IN (?)", course_numbers]
)
end

Then in your view

<% Account.find_similar_accounts( session['user'].accounts ).each do |acc|
%>
....
<% end %>

I hope I haven't misunderstood your problem too badly...

------=_Part_152838_5669910.1153739322288--