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
> 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 -- Posted via http://www.ruby-forum.com/.
On 02/07/06, Stephen Bartholomew <steve@curve21.com> 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
> 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 -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.
Julian Gall 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" > > JulianHi, 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 -- Posted via http://www.ruby-forum.com/.
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(...) clauseThis 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
! 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-- Posted via http://www.ruby-forum.com/.
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 On 03/07/06, Julian Gall <julian.gall@gmail.com> 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 3-jul-2006, at 17:05, harper wrote:> ! > > 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...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
Having read your post at http://www.ruby-forum.com/topic/71573, 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 Gall -- Posted via http://www.ruby-forum.com/.
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
--
Posted via http://www.ruby-forum.com/.
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
On 7/4/06, harper <harper@chalice.ip> wrote:>
> 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
>
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
Mark Van Holstyn
mvette13@gmail.com
http://lotswholetime.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060704/7d3e1f42/attachment.html
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 -- Posted via http://www.ruby-forum.com/.
...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 -- Posted via http://www.ruby-forum.com/.
[ "...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
On 7/4/06, harper <harper@chalice.ip> wrote:>
> ...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
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
Mark Van Holstyn
mvette13@gmail.com
http://lotswholetime.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060704/c0b587aa/attachment.html
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 -- Posted via http://www.ruby-forum.com/.
you guys are great... thanks for everything for the last and definately not least time, harp -- Posted via http://www.ruby-forum.com/.
On Jul 4, 2006, at 12:18 AM, harper wrote:> ...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, > > harpIf 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