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!
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:
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?
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…
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.
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
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 …
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
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.
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):
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
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…
