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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
On 10/2/06, Xavier Noria <fxn-xlncskNFVEJBDgjK7y7TUQ@public.gmane.org> wrote:> > > 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? > > -- fxnI 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On Oct 2, 2006, at 1:03 PM, Daniel N wrote:> On 10/2/06, Xavier Noria <fxn-xlncskNFVEJBDgjK7y7TUQ@public.gmane.org> wrote: > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---