Can anyone solve this sql query

Rails
21

On 22 ÆÅ×, 18:39, Mark B. [email protected] 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.

22

Rob B. 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 B. http://agileconsultingllc.com
[email protected]

23

On 22 ÆÅ×, 19:54, Ank Ag [email protected] 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

24

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

Rob B. http://agileconsultingllc.com
[email protected]

25

On Feb 22, 2008, at 12:54 PM, Ank Ag wrote:

But why use find_by_sql? The select_values returns an array of the
ActiveRecord may present a better option.
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 B. http://agileconsultingllc.com
[email protected]

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 B. http://agileconsultingllc.com
[email protected]

26

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.