Specifying Find Conditions on Multiple Tables

rubyonrails-talk
1

Hello,

I have two database tables, one called users and one called user_info. Every user has one record in user_info, so I use "has_one :user_info" in the user model and "belongs_to :users" in the user_info model. What I'd like to do is a find on the user model but be able to specify conditions for fields that exist in the user_info table. For example, I want to find all users named Joe that live in New York City. First names are stored in the users table and city names are stored in the user_info table. So essentially, I want to generate the query: SELECT * FROM users, user_info WHERE users."firstname" = 'Joe' AND user_info."city" = 'New York City'; What's the best way to go about this in Rails? Thanks!

2

You need to use the :include option to tell rails to join the two tables through their associations. Something like the following should work:

User.find(:all, :conditions => ['users.first_name = ? and user_info.city = ?' 'Joe', 'New York'], :include => 'user_info')

This will join the user_info and the users table using users.id = user_info.user_id.

Adam

3

Hi Matt,

SELECT * FROM users, user_info WHERE users."firstname" = 'Joe' AND user_info."city" = 'New York City';

Ever tried this query?

What's the best way to go about this in Rails? Thanks!

# something like this...

def find_all_by_firstname_and_city(firstname, city)   User.find :all,     :joins => 'user_info ON user_infos.user_id = users.id',     :conditions => {'users."firstname"' => firstname,                     'user_infos."city"' => city} end

# or this...

def find_all_by_firstname_and_city(firstname, city)   User.find :all,     :include => :user_info,     :conditions => {'users."firstname"' => firstname,                     'user_infos."city"' => city} end

Regards Florian

4

Ahh, yes, that does work. I suppose that the table name needs to be specified in the conditions every time, right? SQL lets you specify an attribute name without the table name as long as there is no ambiguity, so my original query could have been: SELECT * FROM users, user_info WHERE firstname = 'Joe' AND city = 'New York City' AND users.id = user_info.id; Is there a way to get Rails to perform this query?

5

did you not see my response?

Adam

6

Hi Matt,

SELECT * FROM users, user_info WHERE firstname = 'Joe' AND city = 'New York City' AND users.id = user_info.id;

I do not really like to put a relation between users.id and user_info.id in Rails, think about renaming user_info to user_infos and user_info.id to user_info.user_id if it's your foreign key... This way you less noise in your models and UserInfo has its OWN primary_key...

Is there a way to get Rails to perform this query?

You can always do User.select_sql('SELECT users.* FROM users...')

def find_all_by_firstname_and_city(firstname, city)

should be    def self.find_all_by_firstname_and_city(firstname, city) to make it a class method...

Regards Florian

PS: Adam, I read you post and I think Matt also did - not to intently though... :slight_smile:

7

I didn't get to choose how the tables in the DB are set up, I just have to deal with them the way they are. I agree though - I'm still trying to get them to merge the tables but that's easier said than done.

Adam, of course I read your posts. Did you read mine? You had to specify a table name in the query conditions in the example you gave (aside from the fields where the join takes place). The problem is that in the past the search was performed on fields from one table or the other or both. So if a search is performed for all people named Joe (stored ONLY in the users table) OR a user wants to search for all people living in New York City (stored ONLY in the user_info) table, then to do the find with just one query, there would have to be a join done on the tables so that you can specify field names without having to specify table names. That's why I was saying in SQL, you don't need to specify table names on a join query. But from all the examples I've seen here, there isn't a way to do that in Rails unless I specify the SQL myself.

8

I didn't get to choose how the tables in the DB are set up, I just have to deal with them the way they are. I agree though - I'm still trying to get them to merge the tables but that's easier said than done.

Adam, of course I read your posts. Did you read mine?

I saw two posts from you which looked like duplicates, I only read the second one thinking that they were the same message. I wanted to make sure you didn't miss my post.

You had to specify a table name in the query conditions in the example you gave (aside from the fields where the join takes place).

I specified a table name, but you don't have to. I only provided it in case there were ambiguous column names.

The problem is that in the past the search was performed on fields from one table or the other or both. So if a search is performed for all people named Joe (stored ONLY in the users table) OR a user wants to search for all people living in New York City (stored ONLY in the user_info) table, then to do the find with just one query, there would have to be a join done on the tables so that you can specify field names without having to specify table names. That's why I was saying in SQL, you don't need to specify table names on a join query. But from all the examples I've seen here, there isn't a way to do that in Rails unless I specify the SQL myself.

try this:

user.rb class User < ActiveRecord::Base   has_one :info end

info.rb class Info < ActiveRecord::Base   set_table_name "info"

  belongs_to :user end

script/console User.find(:all, :conditions => ['login = ? and state = ?', 'johndoe', 'New York'], :include => :info)

login is an attribute of the User class and state is an attribute of Info

works for me..

Adam

9

Adam,

Sorry for the confusion. That worked. You are the man. Now, is there a way to do the same query without knowing what fields you are going to get in advance? It would be nice to just pass in a hash of attributes instead of listing out the conditions. Something like: User.find(:all, :conditions => attributes_hash, :include => :user_info)

When I use your method, Adam, I get:

u = User.find(:all, :conditions => ['firstname = ? and state = ?', 'Beth', 'NY'], :include => :user_info)

=> [#<User:0x4895504 ...

But if I try the way I just suggested, I get:

attributes = {:firstname => 'Beth', :state => 'NY'}

=> {:firstname=>"Beth", :state=>"NY"}

u = User.find(:all, :conditions => attributes, :include => :user_info)

ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703 Mcolumn users.state does not exist

It wouldn't be too hard to generate the condition string, but a hash would be prettier and easier. Any ideas?

Matt

10

Adam,

Sorry for the confusion. That worked. You are the man. Now, is there a way to do the same query without knowing what fields you are going to get in advance? It would be nice to just pass in a hash of attributes instead of listing out the conditions. Something like: User.find(:all, :conditions => attributes_hash, :include => :user_info)

When I use your method, Adam, I get:

u = User.find(:all, :conditions => ['firstname = ? and state = ?', 'Beth', 'NY'], :include => :user_info)

=> [#<User:0x4895504 ...

But if I try the way I just suggested, I get:

attributes = {:firstname => 'Beth', :state => 'NY'}

=> {:firstname=>"Beth", :state=>"NY"}

u = User.find(:all, :conditions => attributes, :include => :user_info)

ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703 Mcolumn users.state does not exist

It wouldn't be too hard to generate the condition string, but a hash would be prettier and easier. Any ideas?

Matt

In general, you need to specify the condition like:

:conditions => [ 'users.firstname = ? and info.state = ?', 'Beth', 'NY' ]

Because you could have columns with the same name:

User.find_by_firstname('Matt').update_attribute(:state, 'confusion')

If you have this kind of thing in a controller, you might find it better to push the logic into the model:

class User    def self.all_with_name_and_state(firstname, state)      find(:all, :conditions => [ 'users.firstname = ? and info.state = ?', firstname, state ],           :include => :info)    end end

This is discussed by Jamis Buck in http://weblog.jamisbuck.org/2006/10/18/skinny-controller-fat-model

-Rob

11

looks like when using a hash as your find conditions, rails doesn't resolve the correct tables, or at least it automatically attempts to find everything through the User model.. Maybe file a bug report about this? I don't know any solution other than to manually build the query string as you proposed.

Adam

12

Adam,

Yes, you're right. Thanks for everyone's help. For those that may be wondering, this method works wonderfully (and feel free to improve on it if you don't like it):

  def search_customers(contact_info)     user_query = ""     values =     errors =

    if contact_info.empty?       errors << "Please specify one or more fields."       redirect_to :action => :customer_search       return     end

    contact_info.each { |k, v|       user_query << "#{k} = ? AND "       values << v     }     user_query = user_query[/(.+)\sAND\s$/, 1]     @search_results = User.find(       :all,       :conditions => [user_query] + values,       :include => :user_info     )   end

13

=begin Hi Matt,

this seems harsh but when I looked at your code I thought: SQL-INJECTION! next I thought: Where does this Regexp come from? then I thought: Too much noise in your controller! finally I thought: Still ambiguous...

Since you're dealing with legacy tables I'll stop suggesting you to change your table layout. =end class UsersController < ApplicationController

  # This method searches in the user_info table with the given search params.

14

Small BuFi: - :conditions => [query] + params[:search.values_at(keys)] + :conditions => [query] + params[:search].values_at(keys)

Oyasumi nasai... Florian