Specifying Find Conditions on Multiple Tables

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!

On 8/29/07, Matt W. [email protected] wrote:

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!

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

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

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?

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’;
But I can’t do:
User.find(:all, :conditions => {:first_name => ‘Joe’, :city => ‘New
York’}, :include => ‘user_info’);
It tries to do users.“city” in this case. I don’t suppose there is any
way to get it done this way?

On 8/29/07, Matt W. [email protected] wrote:

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

did you not see my response?

Adam

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:

Matt W. wrote:

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!

Is there any chance that you’ve really got one model split into two
here? In my opinion, if you have a has_one relationship, what you’ve
really got is one model split (incorrectly) into two tables. Do you have
some reason for this division? If not, the correct thing to do is to
merge the two tables. Then, all of your problems of screening users by
user_info columns becomes moot.

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.

On Aug 29, 6:53 pm, Bryan D. [email protected]

Adam,

Sorry for the confusion. You are the man. Worked like a charm.

Matt

On 8/30/07, Matt W. [email protected] wrote:

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.

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

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

On Aug 30, 2007, at 4:26 PM, Matt W. wrote:

u = User.find(:all, :conditions => ['firstname = ? and state

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 B. in
http://weblog.jamisbuck.org/2006/10/18/skinny-controller-fat-model

-Rob

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

then to do the find with just one query, there would have to be a

works for me…

Adam

Rob B. http://agileconsultingllc.com
[email protected]

On 8/30/07, Matt W. [email protected] wrote:

When I use your method, Adam, I get:
It wouldn’t be too hard to generate the condition string, but a hash
would be prettier and easier. Any ideas?

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

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

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

params[:search] # => { :state => ‘NY’, … }

Since it uses an instance of hash as search parameter the keys

should be

properly quoted and prefixed with user_info.

After the search is performed it maps the user_info back to their

users.
def search_in_userinfo
@users = UserInfo.find(:all,
:include => :user,
:conditions => params[:search]
).map(&:user)
end

This method search in the user table with the given search params.

params[:search] # => { :firstname => ‘Beth’, … }

Since it uses an instance of hash as search parameter the keys

should be

properly quoted and prefixed with user.

def search_in_user
@users = User.find :all, :include => :user_info,
:conditions => params[:search]
end

This method searches both tables, the users and the user_info table

with

the given search params.

So, what we need to do is to properly quote and prefix the

attributes we

are searching for.

def search_in_general
# this gives us the relevant column names in the users table…
user_column_names = User.content_columns.map(&:name)
# …same for user_info table
user_info_column_names = UserInfo.content_columns.map(&:name)

# since a Hash isn't ordered we're doing right by getting the keys 

into
# an Array
keys = params[:search].keys

# now we are building our query array with properly quoted and 

prefixed
# column names that’s joined later on
query = keys.map do |key|
# choose table
if user_column_names.include? key
# prefix and quote this key with the User specific methods
‘%s.%s = ?’ % [User.table_name, User.connection.quote(key)]
elsif user_info_column_names.include? key
# prefix and quote this key with the UserInfo specific methods
‘%s.%s = ?’ % [UserInfo.table_name,
UserInfo.connection.quote(key)]
else
# drop invalid search attributes…
nil
end
end.compact.join ’ AND ’ # remove NILs and glue with AND

# Last but not least we perform the search:
@users = User.find :all, :include => :user_info,
  :conditions => [query] + params[:search.values_at(keys)]

end
end
=begin
As a last refactoring step I’d put the whole search_in_general method
into a User class method called find_by_params unless it already exist
and the the search_in_general method just do one query into @users

Regards
Florian
=end

Small BuFi:

  •   :conditions => [query] + params[:search.values_at(keys)]
    
  •   :conditions => [query] + params[:search].values_at(keys)
    

Oyasumi nasai…
Florian

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs