# Can anyone solve this sql query **URL:** \ **Category:** Rails\ **Created:** [February 22, 2008, 1:35pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895 "2008-02-22T13:35:28Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 1:35pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/1 "2008-02-22T13:35:28Z")
Hello, Please help me solve the sql query. people and relations are two models having has\_and\_belongs\_to\_many association. here is ansample table called people\_relations person\_id relation\_id 11 200 15 160 11 201 11 160 15 200 16 160 16 201 To find all people who has relation\_ids[160,200]i.e relation\_id=160 and relation\_id =200, Note that: the above table is just an example. There can be any number of relation\_idsto be searched for. I get the relation\_ids to be searched in an array from the user. The user wants to find all the people who has all the relation\_ids he has specified. Can anyone solve this Query. Thank you Thank you ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 2:52pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/2 "2008-02-22T14:52:59Z")
something like SELECT DISTINCT people.\* FROM people INNER JOIN people\_relations ON people\_relations.person\_id = people.id WHERE people\_relations.relation\_id IN (160, 200); would do the job not sure if you you really need it, tho ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 3:54pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/3 "2008-02-22T15:54:04Z")
lq wrote: > something like > SELECT DISTINCT people.\* FROM people INNER JOIN people\_relations ON > people\_relations.person\_id = people.id WHERE > people\_relations.relation\_id IN (160, 200); > would do the job > not sure if you you really need it, tho The problem here is you are using IN(160,200) this is a condition for OR i want a condition for and people who have relation\_id=160 AND relation\_id=200. ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:11pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/4 "2008-02-22T16:11:27Z")
On 22 Feb 2008, at 15:54, Ank Ag wrote: > The problem here is you are using IN(160,200) this is a condition > for OR > i want a condition for and people who have relation\_id=160 AND > relation\_id=200. How can one record have two ids at the same time is what I wonder (missed the original message)? Best regards Peter De Berdt ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:18pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/5 "2008-02-22T16:18:04Z")
i think the basic trick will be a combination of an join with a count you take something like the SQL Iq has proposed and count the result of returned relations. take only those which equal the size of the array with relation\_ids. this can be done within a single SQL statement, (maybe even with pure rails) sorry, don’t have the time right now to work this out in detail but maybe this hint will still bring you on the right way ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:18pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/6 "2008-02-22T16:18:36Z")
On Feb 22, 2008, at 8:54 AM, Ank Ag wrote: > The problem here is you are using IN(160,200) this is a condition > for OR > i want a condition for and people who have relation\_id=160 AND > relation\_id=200. Neat problem. Now that I think about, I’m not sure I’ve ever had to tackle it. So my idea here is notably without proper testing: The solution appears to lie in needing to intersect the two results. A quick review of PostgreSQL documentation shows an INTERSECT operator that is similar to UNION. SELECT DISTINCT people.\* FROM people INNER JOIN people\_relations ON people\_relations.person\_id = people.id AND people\_relations.relation\_id = 160 INTERSECT SELECT DISTINCT people.\* FROM people INNER JOIN people\_relations ON people\_relations.person\_id = people.id AND people\_relations.relation\_id = 200 Now, the obvious thought here is that this would become unwieldy with more than two relation\_ids. But that seems to be one way to attack it. Peace, Phillip ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:33pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/7 "2008-02-22T16:33:59Z")
On Feb 22, 2008, at 9:18 AM, Thorsten M. wrote: > i think the basic trick will be a combination of an join with a count > you take something like the SQL Iq has proposed and count the result > of returned relations. take only those which equal the size of the > array with relation\_ids. > this can be done within a single SQL statement, (maybe even with pure > rails) I thought about that too, but wasn’t sure how cumbersome it would be. Another possibility is do the normal relation\_id IN (100, 200), get the result set back in rails, the have a process to filter out what you don’t want. In terms of code simplicity, that might be the easiest to understand. Granted, it would probably involve more processor cycles, but that’s a trade off the OP will have evaluate. > sorry, don’t have the time right now to work this out in detail Doesn’t ever seem to be enough time, does there? Peace, Phillip ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:50pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/8 "2008-02-22T16:50:31Z")
> models having has\_and\_belongs\_to\_many association. > > here is ansample table called people\_relations > > person\_id relation\_id > 11 200 > 15 160 > 11 201 > 11 160 > 15 200 > 16 160 > 16 201 > > To find all people who has relation\_ids[160,200]i.e relation\_id=160 and > relation\_id =200, People.find\_by\_sql([“SELECT persons.id as human FROM people, people\_relation, relation WHERE people.people\_id = people\_relation.people\_id AND people\_relation.relation\_id = relation.relation\_id AND relation.relation\_id IN (?)”, %w(params[:key\_word])"]).uniq ~_~_~_~_~_~_~_~~_~_~_~_~_~_~_~ Reinhart Ariando YM:Booking2Heaven WEB:teapoci.blogspot.com ~_~_~_~_~_~_~_~~_~_~_~_~_~_~_~ ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:53pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/9 "2008-02-22T16:53:22Z")
> People.find\_by\_sql(["SELECT persons.id as human FROM people, sorry not persons.id but people.id Reinhart Ariando ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 4:56pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/10 "2008-02-22T16:56:57Z")
Correction: People.find\_by\_sql([" SELECT people.id as human FROM people, people\_relation, relation WHERE people.persons\_id = people\_relation.persons\_id AND people\_relation.relation\_id = relation.relation\_id AND relation.relation\_id IN (?)", %w(params[:key\_word])"]).uniq Reinhart Ariando ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:10pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/11 "2008-02-22T17:10:07Z")
On 22 Feb 2008, at 15:18, Thorsten M. wrote: > i think the basic trick will be a combination of an join with a count > you take something like the SQL Iq has proposed and count the result > of returned relations. take only those which equal the size of the > array with relation\_ids. > this can be done within a single SQL statement, (maybe even with pure > rails) Does SELECT people\_relations.people\_id FROM people\_relations INNER JOIN people\_relations AS other\_relations ON people\_relations.people\_id = other\_relations.people\_id WHERE people\_relations.relation\_id = 160 AND other\_relations.relation\_id = 200 not do the job ? Fred ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:02pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/12 "2008-02-22T17:02:25Z")
Visit Indonesia 2008 wrote: > Correction: > > People.find\_by\_sql([" > > SELECT people.id as human FROM people, people\_relation, relation > WHERE people.persons\_id = people\_relation.persons\_id > AND people\_relation.relation\_id = relation.relation\_id > AND relation.relation\_id IN (?)", %w(params[:key\_word])"]).uniq > > Reinhart Ariando hi ok i am going to try that. in addidtion i have another idea as well as sql1 = “select person\_id from people\_relations where (relation\_id IN (#{%w(#{params[:relation\_ids])}} group by person\_id having count(\*) \>= #{params[:relation\_ids].length}” ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:10pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/13 "2008-02-22T17:10:42Z")
In case you want to do it in Ruby 1. do a find and get all people with relation\_id = 160, convert the resulting Array to a Set 2. do the same for people with relation\_id = 200 3. get the intersection of the two sets You’ll end up with more database calls but the code will be easier to deal with. Just my 2 cents. ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:16pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/14 "2008-02-22T17:16:09Z")
Frederick C. wrote: > On 22 Feb 2008, at 15:18, Thorsten M. wrote: > > > i think the basic trick will be a combination of an join with a count > > you take something like the SQL Iq has proposed and count the result > > of returned relations. take only those which equal the size of the > > array with relation\_ids. > > this can be done within a single SQL statement, (maybe even with pure > > rails) > > Does > > SELECT people\_relations.people\_id FROM people\_relations > INNER JOIN people\_relations AS other\_relations ON > people\_relations.people\_id = other\_relations.people\_id > WHERE people\_relations.relation\_id = 160 AND > other\_relations.relation\_id = 200 > > not do the job ? > > Fred I believe Fred’s Solution wont work if i have to to find people with relation\_ids[160,200,201] that is if there are more than two relations Thank you. ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:32pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/15 "2008-02-22T17:32:03Z")
On Feb 22, 2008, at 7:35 AM, Ank Ag wrote: > 11 160 > I get the relation\_ids to be searched in an array from the user. The > user wants to find all the people who has all the relation\_ids he has > specified. > Can anyone solve this Query. > Thank you > > Thank you my\_relation\_ids = [160, 200] people\_ids = Person.find( Person.connection.select\_values( Person.sanitize\_sql( [“SELECT person\_id FROM people\_relations” + " WHERE relation\_id IN (?)" + " GROUP BY person\_id HAVING count(\*) = ?", my\_relation\_ids, my\_relation\_ids.size]))) Similar to the idea that you posted before I could get this message out. -Rob Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com) removed\_email\_address@domain.invalid ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:33pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/16 "2008-02-22T17:33:14Z")
one of the people, used to work with plain sql, told me once: Don’t worry about the querie, worry about query planner. such kind of “ease to deal with” is not worth trading the speed and readability of pretty simple sql query, like Ank’s one. ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:26pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/17 "2008-02-22T17:26:07Z")
> Visit Indonesia 2008 wrote: > in addidtion i have another idea as well as > > sql1 = “select person\_id from people\_relations where (relation\_id IN > (#{%w(#{params[:relation\_ids])}} group by person\_id having count(\*) \>= > #{params[:relation\_ids].length}” came to almost the same, but having count(\*) = length, because i thought there is nowhere to came from for additional people\_relations to sum up more than provided array’s lenght. ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 6:31pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/18 "2008-02-22T18:31:14Z")
Rob B. wrote: > On Feb 22, 2008, at 7:35 AM, Ank Ag wrote: > > > 11 160 > > I get the relation\_ids to be searched in an array from the user. The > > user wants to find all the people who has all the relation\_ids he has > > specified. > > Can anyone solve this Query. > > Thank you > > > > Thank you > > my\_relation\_ids = [160, 200] > > people\_ids = Person.find( > Person.connection.select\_values( > Person.sanitize\_sql( > sql1,params[:relation\_ids] > > Similar to the idea that you posted before I could get this message out. > > -Rob > > Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com) > removed\_email\_address@domain.invalid Hello, The only thing i need to know is and am sure is pretty simple is if i just write sql1 = “select person\_id from people\_relations where (relation\_id IN (#{params[relation\_ids]} group by person\_id having count(\*) \>= #{params[:relation\_ids].length}” This is just a plain string that contains the sql syntax which i will use in find\_by\_sql. For some reason i want the query in the string. But the problem is when i run the query the array is not seperated by comma i.e when i say relation\_id IN({#params[:realtion\_id]}) then i ger IN(160200) but i want it as IN(160,200) note the comma in between the values. PLease tell me how can i get that keeping the query in the string which i will later use in find\_by\_sql ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 5:39pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/19 "2008-02-22T17:39:48Z")
Ank Ag wrote: > I get the relation\_ids to be searched in an array from the user. The > user wants to find all the people who has all the relation\_ids he has > specified. > Can anyone solve this Query. I’m sure, depending on your database, there are various ways of writing queries using sub-queries, etc which will enable you to use find\_by\_sql() to do what you want and they will all be incomprehensible the next day (I expect). Not ideal, but if the user is providing the list can I assume that it is unlikely to be tooooo big? If this is the case, then you are best starting with relations as otherwise you must scan all people in case they have that relation. So, if the array from the user is users\_array and these will be IDs from the relation table, then: people\_arrays = [] users\_array.each do |n| people\_arrays \<\< Relation.find(n).people end This gives people\_arrays as an array of arrays of people. Now the problem is which people are in each sub-array which is just the intersection of arrays. Less efficient than doing everything in one big SQL perhaps, but much more readable and maintainable. Alternatively, get all the relations in one go: relation\_array = Relation.find(:all, :conditions =\> “relations.id in (#{users\_array.join ‘,’})”, :include =\> :people) unless users\_array.blank? Now you have everything in memory that you need. This time, you need to find the intersection of the arrays: relation\_array[n].people Either way, you reduce the problem of finding the intersection from the database to one of finding the intersection of an array of arrays which is much easier. ---
**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\ **Post date:** [February 22, 2008, 6:49pm UTC](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895/20 "2008-02-22T18:49:25Z")
> i.e when i say relation\_id IN({#params[:realtion\_id]}) then i ger > IN(160200) but i want it as IN(160,200) note the comma in between the > values. PLease tell me how can i get that keeping the query in the > string which i will later use in find\_by\_sql if you read my corrected script, you will get answer of your question now. Correction: People.find\_by\_sql([" SELECT people.id as human FROM people, people\_relation, relation WHERE people.persons\_id = people\_relation.persons\_id AND people\_relation.relation\_id = relation.relation\_id AND relation.relation\_id IN (?)", %w(params[:key\_word])"]).uniq your script now: > if i just write > sql1 = “select person\_id from people\_relations where (relation\_id IN > (#{params[relation\_ids]} group by person\_id having count(\*) \>= > #{params[:relation\_ids].length}” My Code for your new script : find\_by\_sql([“SELECT person\_id from people\_relations WHERE (relation\_id IN %w(params[:relation\_ids]) GROUP by person\_id HAVING count(\*) \>= %w(params[:relation\_ids]).length}” ~_~_~_~_~_~_~_~~_~_~_~_~_~_~_~ Reinhart Ariando YM:Booking2Heaven WEB:teapoci.blogspot.com ~_~_~_~_~_~_~_~~_~_~_~_~_~_~_~ [Next page](https://www.ruby-forum.com/t/can-anyone-solve-this-sql-query/129895.md?page=2)