Is there a proper way of doing inner selects in rails. Basically, I run in issues where I need the count of a query and end up using find_by_sql. I was wondering if there was any elegant way of doing such queries. Roland -- Posted via http://www.ruby-forum.com/.
On Monday, July 17, 2006, at 7:55 PM, Roland Mai wrote:>Is there a proper way of doing inner selects in rails. > >Basically, I run in issues where I need the count of a query and end up >using find_by_sql. I was wondering if there was any elegant way of doing >such queries. > >Roland > >-- >Posted via http://www.ruby-forum.com/. >_______________________________________________ >Rails mailing list >Rails@lists.rubyonrails.org >http://lists.rubyonrails.org/mailman/listinfo/railsCan you post an example of the kind of query you want to do? _Kevin www.sciwerks.com -- Posted with http://DevLists.com. Sign up and save your mailbox.
here you go:
select count(*)
from (select distinct field_id from my_table
left join my_other_table on my_other_table.forein_key_id =
my_table.id
where field_id < 5) as a
left join some_other_table on a.other_id = some_other_table.id
where some_other_table.created_at < getdate()
--
Posted via http://www.ruby-forum.com/.
Roland Mai wrote:> here you go: > > select count(*) > from (select distinct field_id from my_table > left join my_other_table on my_other_table.forein_key_id = > my_table.id > where field_id < 5) as a > left join some_other_table on a.other_id = some_other_table.id > where some_other_table.created_at < getdate()Hi, yahoo search brought me to this old topic, but I am running into the same problem. So is find_by_sql the only way to perform joins on inner-selects? -- Posted via http://www.ruby-forum.com/.
Depends on how you layout your models. Things like "left join my_other_table on my_other_table.forein_key_id = my_table.id" are taken care of by AR if you set up the associations properly. You could then do a Model.count(:joins=>..., :conditions=>....) On Jun 4, 12:38 am, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Roland Mai wrote: > > here you go: > > > select count(*) > > from (select distinct field_id from my_table > > left join my_other_table on my_other_table.forein_key_id > > my_table.id > > where field_id < 5) as a > > left join some_other_table on a.other_id = some_other_table.id > > where some_other_table.created_at < getdate() > > Hi, yahoo search brought me to this old topic, but I am running into the > same problem. So is find_by_sql the only way to perform joins on > inner-selects? > -- > Posted viahttp://www.ruby-forum.com/.