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

On 10/2/06, Xavier N. [email protected] wrote:

     AND u.cluster = '#{in_cluster_of_user.cluster}'

+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

On Oct 2, 2006, at 1:03 PM, Daniel N wrote:

   GROUP BY fp.person_id

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs