Can anyone solve this sql query

rubyonrails-talk
21

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.

22

Rob Biedenharn 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.

23

you may have to review your domain model then

24

You 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@AgileConsultingLLC.com

25

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:

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.