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