JOIN conditions

Rails
1

Hi,

how can I get Rails to generate SQL queries with conditions in the JOIN
clause?

What I would want is:

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id AND j.salary > 9999999)

I tried

People.find(:all, :include => ‘jobs’, :conditions => ‘jobs.salary >
9999999’)

but that generates

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id)
WHERE j.salary > 9999999

Thanks.

Cheers,
Marco

2

how can I get Rails to generate SQL queries with conditions in the JOIN
clause?
You can specify joins in the find method:

People.find(:all,
:joins => ‘LEFT OUTER JOIN jobs j (p.job_id = j.id AND j.salary >
9999999)’)

Steve

3

On 02/07/06, Stephen B. [email protected] wrote:

how can I get Rails to generate SQL queries with conditions in the JOIN
clause?
You can specify joins in the find method:

People.find(:all,
:joins => ‘LEFT OUTER JOIN jobs j (p.job_id = j.id AND j.salary >
9999999)’)

Thanks Steve.

I was wondering if there wouldn’t be something like

People.find(:all,
:include => ‘jobs’,
:include_conditions => “jobs.salary > 9999999”)

I will go for the :joins option. : )

Cheers,
Marco

4

I was wondering if there wouldn’t be something like

People.find(:all,
:include => ‘jobs’,
:include_conditions => “jobs.salary > 9999999”)
There’s not really any need to abstract that functionaility IMO.
Generally, :joins isn’t used a lot and when you do need it, it’s easy
and ultimately more flexible to use the SQL.

Cheers,
Steve

5

Also, I would think the SQL query engine would optimize these two
queries to the same thing:

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id AND j.salary > 9999999)

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id) WHERE j.salary > 9999999)

It isn’t often possible to influence how the query is actually performed
by the way you structure the SQL statement.

Therefore, just put :conditions => “j.salary > 9999999”

Julian

6

On 3-jul-2006, at 14:10, harper wrote:

i actually just posted a very simallar message a short while ago
(under
‘help!’)
dealing with the same subject…i am having a diffucult (and long)
time
trying to add :conditions to a statement that has an :include
method in
the find(…) clause

This is a result of how eager loading works. The problem is that it
basically makes a list
of all tables which are going to be involved in the query and aliases
them sequentially, like so:

things → t0
people → t1
people_things → t2

and so on. So it’s not possible to find out how the table you want to
reference in a condition is named.
I don’t know what the solution might be, especially considering that
all the associations in such a “megajoin” might have name clashes.

You can (somewhat) see how the whole shebang works here:

http://blog.caboo.se/articles/2006/02/21/eager-loading-with-cascaded-
associations

The problem that eager loading does not honor conditions and ordering
is a long known one, but dealing with it seems
extremely convoluted as I see it (to do it properly you really need a
PROPER database that doesn’t die on you when
you do nested SELECTs).

Julian ‘Julik’ Tarkhanov
please send all personal mail to
me at julik.nl

7

Julian G. wrote:

Also, I would think the SQL query engine would optimize these two
queries to the same thing:

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id AND j.salary > 9999999)

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id) WHERE j.salary > 9999999)

It isn’t often possible to influence how the query is actually performed
by the way you structure the SQL statement.

Therefore, just put :conditions => “j.salary > 9999999”

Julian

Hi,

i actually just posted a very simallar message a short while ago (under
‘help!’)
dealing with the same subject…i am having a diffucult (and long) time
trying to add :conditions to a statement that has an :include method in
the find(…) clause
i keep getting really really really annoying mysql syntax errors. really
annoying.
i looked in api.rubyonrails.com\rubydoc under ::Base and i came across
something about not being able to use :conditions in a habtam
relashinship.
is this true? ? if so, how do i get around it? if not, how can i be
wasting so many hours trying to solve something so simple??

Thankyou Greatly,

harper

8

Nope.

You would be right if that was a simple JOIN. But, IF the false
condition is in the JOIN clause, an OUTER JOIN returns records from
‘people’ table even if no associated records from ‘jobs’ are found. If
the false condition is in the WHERE clause, that will NOT return
records.

Let’s assume that the ‘people’ table contains some records while the
‘jobs’ table is empty.

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id AND j.salary > 9999999)

=> some people records

SELECT * FROM people p
LEFT OUTER JOIN jobs j (p.job_id = j.id) WHERE j.salary > 9999999)

=> empty set

Cheers,
Marco

9

!

ok…so i’m sitting here, first of all relieved that i wasn’t wasting
big amounts of time because of some stupid issue, i mean, i was looking
for an answer that didn’t exist; so i should be happier, i guess,
except…
what am i supposed to do now?

is there a personal email i could contact u at to avoid overloading the
forum or a reference you could shoot me towards?
thank you very much…

–
Julian ‘Julik’ Tarkhanov
please send all personal mail to
me at julik.nl

10

On 3-jul-2006, at 17:05, harper wrote:

the
forum or a reference you could shoot me towards?
thank you very much…

The reference is in the association code itself. If you want to have
conditions on HABTM (looks like I was mistaken)
use :through - that is, plop a condition in the join association.

–
Julian ‘Julik’ Tarkhanov
please send all personal mail to
me at julik.nl

11

Having read your post at Help! - Rails - Ruby-Forum, I am not
convinced that you need things to be this complicated.

If you have M1 has_and_belongs_to_many M2s, you can say:

myM1 = M1.find(:first, :condition => “animal = ‘dog’”)
myResult = M1.M2s

This follows the habtm link by issuing a second SQL query for the second
line of code.

If you want eager loading, you can say:

myM1ArrayWithM2s = M1.find(:all, :include => :m2s, :condition =>
“m1s.animal = ‘dog’”)

This will create a single SQL statement somethihg like:

SELECT m1s.id AS t0_r0, m1s.animal AS t0_r1, m2s.id AS t1_r0,
m2s.another_field AS t1_r1 FROM m1s LEFT OUTER JOIN m1s_m2s ON
m1s_m2s.m1_id = m1s.id LEFT OUTER JOIN m2s ON m2s.id = m1s_m2s.m2_id
WHERE (animal = ‘dog’)

If the field names in the :condition are unique to one of the tables,
you’re ok. If you have a field with the same name in more than one table
(e.g. status), just prefix it with the table name.

I don’t know when habtm stops you using conditions. It works for me when
I’ve used it. Perhaps Julik can explain.

Also, if you (harper) would like to submit exactly what you are wanting
to do, I can have a look.

Julian G.

12

Hi Julian,

Thank you for your reply…i have been using up a very big part of my
time trying to better understand all of the eager loading issues, it is
obviously important, and so i’ll take as much time as i need to fully
understand it - - thanks for helping me along the way.

as i have a has_and_belongs_to_many relashinship between groups and
users (and groups_users, accordingly), the issue at hand is this:

i need to select an array of users including all of the groups
associated with them, determined by (the condition part of the story)

  1. a query string entered in a searchbar
  2. groups that were chosen
    ( i have a searchbar that you can choose one or many groups to look
    under, and under the users in those groups, perform a search query
    […like…%%…])

so…as i’ve been trying to do this for a while, i tried implementing
the search for users while only looking according to the groups
chosen(without the search string query) and it worked fine (like a
charm!) but when i tried adding an AND users.title like ? , #{query}
statment to the conditions it failed on me.

…this is according to what you’ve shown me(and it works great):

groups = @groups.join(’,’)
@users = User.find(:all, :conditions => “group_id IN (#{groups})”,
:include => :groups")

…this is what i’ve added(and it fails):

@users = User.find(:all, :conditions => [“group_id IN (#{groups} AND
USERS.TITLE LIKE ?”, #{PARAMS[:QUERY]}], :include => :groups)

it outputs this error:

“SELECT * FROM as t0_r0…LEFT OUTER JOIN users on…LEFT OUTER JOIN
groups ON groups.id = groups_users.group_id WHERE (group_id IN (10) AND
users.title like %something%)”

…stating the problem was on the where clause. it must be a stupid
mistake or something, but i’ve been spending so much time on it already,
it’s a task of survival of sanity here…so how do i change the “…and
users.title” to go into the users on LEFT OUTER JOIN where clause? i
even tried to play around with a through clause (admitably, i don’t know
how to use, but tried at the least) but it errored a “unknown key
through”…
it doesn’t seem that rails wouldn’t have a very simple solution, but it
obviously was thrown way over my head.

i am in great debt,
thanks again,

harp

13

all good. there were a couple of other small things i had to change (in
the mysql definitions of the table column i was searching in - - that
was also part of the error) but all in all, i think i’ve got it pretty
much down.

Thank you everyone for helping…i would say rails is sufficient enough,
but a great community adds a hell of a lot more.

thanks again,
and again,

harp

14

The problem with you sql query is that %something% needs quotes. rails
will
auto quote it if you feed it to the find properly, otherwise youll have
to
do it manually. I recomment you let rails do it. Something like this…

@users = User.find(:all, :conditions => [“group_id IN (#{groups}) AND
users.title LIKE ?”, params[:QUERY]], :include => :groups)

This is very similar to the one you showed, however the one you showed
and
the output you gave done match. make sure you didnt miss anything.

mark

15

[ “…user.title like :query or user.nickname like :query or user.email
like
:query or
user.anothercolumn”, { :query => params[:QUERY] } ]

that should do it a little simpler

mark

16

…last but and probably least,
if the user table has five or six columns i want to perform the search
on (like %…) is there a more simple clean way to use

["…user.title like ? or user.nickname like ? or user.email like ? or
user.anothercolumn", params[:QUERY],
params[:QUERY],params[:QUERY],params[:QUERY]]

something like

["…user.all_columns like ? ", params[:QUERY]]

or do i have to specify each question mark for each column with a
corresponding value after the clause?

thank you all,

harp

17

Or…

[“CONCAT(user.title, user.nickname, user.email, user.anothercolumn) LIKE
?”, params[:QUERY]]

If you are concerned about queries going from the end of one field to
the beginning of the next, use CONCAT_WS with a separator that is not
likely to be entered in the PARAM. You could also use TRIM on the fields
if they might have large numbers of trailing spaces. If they are
VARCHAR, this is unlikely to be a problem.

Julian

18

you guys are great…
thanks for everything for the last and definately not least time,

harp

19

On Jul 4, 2006, at 12:18 AM, harper wrote:

["…user.all_columns like ? ", params[:QUERY]]

or do i have to specify each question mark for each column with a
corresponding value after the clause?

thank you all,

harp

If you want a nicer(IMHO) way to do multi params searches like that
you could use my ez_where[1] plugin. Then your query would look like
this:

params[:query] = “rails”

@user = User.find_where :all do |user|
user.any_of(:title, :nickname, :email, :name) =~ “%#{params
[:query]]}%”
end

What that find_where does is create the sql for the :conditions param
of a normal find. Then it passes those along to AR::Base.find. So the
above query basically turns into this:

=> [“(users.title LIKE ? OR users.nickname LIKE ? OR users.email
LIKE ? OR users.name LIKE ?)”,
“%rails%”, “%rails%”, “%rails%”, “%rails%”]

[1] http://brainspl.at/articles/2006/06/30/new-release-of-ez_where-
plugin

Cheers-
-Ezra