Bad query?

ruby-talk
1

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.

2

Hmm, looks like a Rails problem, although there are a lot of nice people
around who might be willing to help, sorry I cannot :(, I think you should
post to the Rails Mailing List.

Cheers
Robert

···

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.

--
Deux choses sont infinies : l'univers et la bêtise humaine ; en ce qui
concerne l'univers, je n'en ai pas acquis la certitude absolue.

- Albert Einstein

3

Try to fiddle with the condition... what are your course numbers like?
J.

···

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.

4

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...

···

On 7/24/06, raffir@gmail.com <raffir@gmail.com> wrote: