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?
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.
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.