when to use find_by_sql

I have a bunch of queries to compute some counters, like

   find_by_sql(<<-SQL
     SELECT p.*, c.nfavourites
     FROM people p, (
       SELECT fp.person_id, COUNT(fp.user_id) as nfavourites
       FROM favourite_people fp, users u
       WHERE fp.user_id = u.id
         AND u.cluster = '#{in_cluster_of_user.cluster}'
       GROUP BY fp.person_id
     ) as c
     WHERE p.user_id IS NOT NULL
       AND p.id = c.person_id
     ORDER BY c.nfavourites DESC
     LIMIT #{limit}
     SQL
   )

They do not map directly to find parameters and some convoluted Ruby+AR would be needed to emulate them, are they what find_by_sql is for? Am I using it appropriately?

-- fxn

I believe with find_by_sql you need to get only the fields pertinent to the given object that your after. I havn’t had much experience with it. I usually work around sql. Not because there’s something wrong with it, but because it’s sometimes difficult to use. If you really need to you can bypass AR and use it’s connection to the DB for you own evil purposes. Then you parse the results as though you were just using the db connector.

I’m sorry but I can’t give you an example. I havn’t done it for a while.

Hope that helps

I have a bunch of queries to compute some counters, like

   find_by_sql(<<-SQL
     SELECT p.*, c.nfavourites
     FROM people p, (
       SELECT fp.person_id, COUNT(fp.user_id) as nfavourites
       FROM favourite_people fp, users u
       WHERE fp.user_id = u.id
         AND u.cluster = '#{in_cluster_of_user.cluster}'
       GROUP BY fp.person_id
     ) as c
     WHERE p.user_id IS NOT NULL
       AND p.id = c.person_id
     ORDER BY c.nfavourites DESC
     LIMIT #{limit}
     SQL
   )

They do not map directly to find parameters and some convoluted Ruby
+AR would be needed to emulate them, are they what find_by_sql is
for? Am I using it appropriately?

-- fxn

I believe with find_by_sql you need to get only the fields pertinent to the given object that your after.

Ah yes thank you, that is not related to the main question in fact, in the sense that if the query hadn't additional columns I would like to confirm the idiomatic use of find_by_sql anyway.

It is documented that additional columns end up being new attributes of the objects. In my application there's a contract that says that for those particular queries the counter comes attached. That's an isolated hack to avoid the need of counting again given the array of people, since I need to display the person and his counters for a handful of person which are the top ten something.

I havn't had much experience with it. I usually work around sql. Not because there's something wrong with it, but because it's sometimes difficult to use. If you really need to you can bypass AR and use it's connection to the DB for you own evil purposes. Then you parse the results as though you were just using the db connector.

I think that is a step beyond find_by_sql.

-- fxn