Can anyone solve this sql query

Rails
1

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

2

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

3

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.

4

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

5

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

6

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

7

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

8

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
~~~~~~~~~~~~~~~~

9

People.find_by_sql(["SELECT persons.id as human FROM people,

sorry not persons.id but people.id

Reinhart Ariando

10

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

11

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

12

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}”

13

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.

14

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.

15

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
removed_email_address@domain.invalid

16

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.

17

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.

18

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

19

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.

20

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
~~~~~~~~~~~~~~~~