Can anyone solve this sql query

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

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

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.

How can one record have two ids at the same time is what I wonder (missed the original message)?

Best regards

Peter De Berdt

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

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

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

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

sorry not persons.id but people.id

Reinhart Ariando

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

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

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

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.

Frederick Cheung wrote:

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.

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 Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

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.

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.

Rob Biedenharn 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 Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

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

Using the sanitize_sql will take care of that. You could also do:

params[:relation_ids].join(',')

But why use find_by_sql? The select_values returns an array of the first column (the only column in this case which is where select_values id best). Model.find with an array of ids returns an array of the Model objects with those ids (but raises an exception unless *all* the ids are found). Unless you have people_relations.person_id values that lack a people.id, that shouldn't be a problem since you just got the ids from the database.

You should examine "For some reason i want..." because otherwise you may start reaching for find_by_sql too often when some other aspect of ActiveRecord may present a better option.

-Rob

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

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