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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On 22 фев, 14:35, Ank Ag <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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 > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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, thoThe 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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 22, 2008, at 8:54 AM, Ank Ag wrote:> > 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. >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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 22, 2008, at 9:18 AM, Thorsten Mueller 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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 ~*~*~*~*~*~*~*~~*~*~*~*~*~*~*~ -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > People.find_by_sql(["SELECT persons.id as human FROM people,sorry not persons.id but people.id Reinhart Ariando -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Ariandohi 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}" -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 22 Feb 2008, at 15:18, Thorsten Mueller 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> 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 > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> On 22 Feb 2008, at 15:18, Thorsten Mueller 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 ? > > FredI 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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 22, 2008, at 7:35 AM, Ank Ag wrote:> 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 youmy_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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. On 22 фев, 18:08, "Franz Strebel" <franz.stre...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Rob Biedenharn 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 Biedenharn http://agileconsultingllc.com > Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.orgHello, 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 22, 2008, at 12:31 PM, Ank Ag wrote:> Rob Biedenharn 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 Biedenharn http://agileconsultingllc.com >> Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org > > 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_sqlUsing 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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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_sqlif 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 ~*~*~*~*~*~*~*~~*~*~*~*~*~*~*~ -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 22 фев, 18:39, Mark Bush <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> they will all be incomprehensible > the next day (I expect).how class Person def self.find_by_relation_ids(*relation_ids) find(:all, :select => ''people.title, people.id'', :joins => ''INNER JOIN people_relations ON people_relations.person_id people.id'', :conditions => ["people_relations.relation_id IN (?)", relation_ids], :group => "people.id, people.title HAVING count(*) #{relation_ids.length}") end end would become incomprehensible next day?> 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.i thought we always solved the problem, but you''ve got another one. check your direction then> > Less efficient than doing everything in one big SQL perhaps, but much > more readable and maintainable.while agree on efficiency, let me disagree on readability. do you really think the Person#find_by_relation_ids is less readable, than quite a lot of lines of ruby code?> > 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.even more, than you need now you have in memory often fitting tight. don''t think i''m in premature optimisation and stuff. It''s just one more sliiiight tradeoff for nothing. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Rob Biedenharn wrote:> On Feb 22, 2008, at 12:31 PM, Ank Ag wrote: >>>> Thank you >>> >> (#{params[relation_ids]} group by person_id having count(*) >>> 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. > > -RobHi, Thanks a lot for your reply. The reason i want it in a string is, like the "relation" table i have two more tables as "functions" and "categories". The search filter is based on three criteria where the user may enter any combination of relations, functions and categories. i.e he might only enter relations and no functions & categories or he may enter functions and categories but no relations. So i have to build the sql query after checking the conditions what he has enetered and then execute. Thank you.> > Rob Biedenharn http://agileconsultingllc.com > Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 22 фев, 19:54, Ank Ag <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> The search filter is based on three criteria where the user may enter > any combination of relations, functions and categories. i.e he might > only enter relations and no functions & categories or he may enter > functions and categories but no relations. So i have to build the sql > query after checking the conditions what he has enetered and then > execute.you may have to review your domain model then --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 22, 2008, at 12:54 PM, Ank Ag wrote:> Rob Biedenharn wrote: >> On Feb 22, 2008, at 12:31 PM, Ank Ag wrote: >>>>> Thank you >>>> >>> (#{params[relation_ids]} group by person_id having count(*) >>>> 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 > > Hi, > Thanks a lot for your reply. The reason i want it in a string is, like > the "relation" table i have two more tables as "functions" and > "categories". > > The search filter is based on three criteria where the user may enter > any combination of relations, functions and categories. i.e he might > only enter relations and no functions & categories or he may enter > functions and categories but no relations. So i have to build the sql > query after checking the conditions what he has enetered and then > execute. > > Thank you. >> >> Rob Biedenharn http://agileconsultingllc.com >> Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.orgYou might consider a technique like: where_conditions = [[]] having_condition = [[]] unless params[:relations].blank? where_conditions[0] << ''relation_id IN (?)'' where_conditions << params[:relations] having_conditions[0] << ''count(*) = ?'' having_conditions << params[:relations].length end where_conditions[0] = where_conditions[0].join('' AND '') having_conditions[0] = having_conditions[0].join('' AND '') sql = [ ''SELECT person_id FROM people_relations WHERE'' connection.sanitize_sql(where_conditions), ''GROUP BY person_id'', connection.sanitize_sql(having_conditions) ].join('' '') Person.find(Person.connection.select_values sql) -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ank Ag wrote:> The search filter is based on three criteria where the user may enter > any combination of relations, functions and categories. i.e he might > only enter relations and no functions & categories or he may enter > functions and categories but no relations. So i have to build the sql > query after checking the conditions what he has enetered and then > execute.Iq''s solution: find(:all, :select => ''people.title, people.id'', :joins => ''INNER JOIN people_relations ON people_relations.person_id =people.id'', :conditions => ["people_relations.relation_id IN (?)",relation_ids], :group => "people.id, people.title HAVING count(*) = #{relation_ids.length}") is still perfect in this case, but personally, I prefer simpler statements. Especially at this time of night. For this case, there is the useful squirrel plugin from: http://thoughtbot.com/projects/squirrel I couldn''t install it as a plugin myself, but the following worked (from the rails application root directory): svn co https://svn.thoughtbot.com/plugins/squirrel/trunk/ vendor/plugins/squirrel (then restart the application) Then you can write Iq''s query as: Person.find(:all, :group => "people.id HAVING count(*) = #{relation_ids.length}") do relations.id === relations_ids end The great thing about this is that it is easily extended to your case, so to add categories from category_ids array (assuming all conditions must be met): amount = relation_ids.blank? ? 1 : relation_ids.length amount *= category_ids.blank? ? 1 : category_ids.length Person.find(:all, :group => "people.id HAVING count(*) = #{amount}") do all { relations.id === relation_ids unless relation_ids.blank? categories.id === category_ids unless category_ids.blank? } end (note that the lengths must be multiplied except where the array is empty or nil) I''m still not happy about the HAVING clause and it would be great if squirrel had an option for this, but it is reasonably readable, easily understandable and easy to maintain. Adding "functions" should be straightforward. I''ve tried testing this and it seems ok, but if anyone sees a hole in this, let me know. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---