I studied Squeel a bit, the best I could do was this:
Person.joins{services}.where{
id.in(Person.joins{services}.where{services.type == 1}.select{id})
& id.in(Person.joins{services}.where{services.type == 2}.select{id})}
which is both unefficient and very ugly.
I think it would be much better if & was implemented in Relation class. It
seems like & used to be an alias to merge and that was dropped. That's nice
because & and merge are indeed different. But why not properly implement &
using DBMS's INTERSECTION (or doing some workaround such
as http://www.bitbybit.dk/carsten/blog/?p=71 on those dbms that don't
support INTERSECTION)?
If we had such implementation, then what I want would be as simple (and
pretty) as:
Person.joins(:services).where('services.type' => 1)
& Person.joins(:services).where('services.type' => 2)
that works today, but as I said, it returns an Array and not a Relation.
I'm not good with arel yet (I haven't found much documentation on the topic
yet). So I'm not sure if it can be done with it. I recall that, while I was
playing with it a while back, I was able to do make it generate the query
with INTERSECT, but it didn't work with mysql. So maybe there's still work
to do in arel's side.
On Wednesday, August 8, 2012 2:27:14 PM UTC-3, Pedro Nascimento wrote:
>
> Take a look at ARel and Squeel gem. Squeel gem supports something like
> this already.
>
> On Wed, Aug 8, 2012 at 12:05 PM, Rafael Almeida
> <almei...@gmail.com<javascript:>
> > wrote:
>
>> Hello.
>>
>> Let's say I have the following schema
>>
>> People <---> people_services <---> services
>>
>> That is, people has a many-to-many relationship with services. Say I want
>> to find all people who has services of types 1 and 2. How can we do it on
>> activerecord today?
>>
>> My best solution so far is this
>>
>> people_query = Person.joins(:services)
>> people = []
>> [1,2].each do |t|
>> people << people_query.where(:type => 1)
>> end
>> people = people.reduce(:&)
>>
>> That's not so good because by doing reduce(:&) you're exiting
>> activerecord domain. So you wouldn't be able to do something like
>> people.reduce(:&).limit(10). Also, that way I'm loading a lot more data
>> than I actually need in my application memory. So, certainly not optimal
>> solution.
>>
>> It's possible to make such query in SQL alone, if the dbms supports
>> INTERSECTION then doing it is straightforward. If it doesn't, it's still
>> possible using joins on subqueries.
>>
>> I think activerecord ought support such query and I'm willing to take my
>> time and write the code for it. However, I'd like someone to help me out
>> with it a bit. I don't understand rails or activerecord code base so well.
>> Anyway, it would be best if I did a gem that introduces that feature into
>> activerecord. Can anyone tell me what should I look up? A high level
>> description of what I must do would be great. Something like "You'll need
>> to create a gem, monkey patch this activerecord class, probably use this
>> and this function to help you writing your SQL" would be nice.
>>
>> Cheers
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Ruby on Rails: Core" group.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msg/rubyonrails-core/-/jz3f6lJrXEkJ.
>> To post to this group, send email to
>> rubyonra...@googlegroups.com<javascript:>
>> .
>> To unsubscribe from this group, send email to
>> rubyonrails-co...@googlegroups.com <javascript:>.
>> For more options, visit this group at
>> http://groups.google.com/group/rubyonrails-core?hl=en.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-core/-/Wwe1Y5Z7P3AJ.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
