Sorry for the double post, but I came up with arel:
people.project(:id).join(services).on(services[:person_id].eq(people[:id])).where(services[:type].eq(1)).intersect(
people.project(:id).join(services).on(services[:person_id].eq(people[:id])).where(services[:type].eq(2)))
calling to_sql on that yields the following:
( SELECT id
FROM `people` INNER JOIN `services` ON `services`.`person_id` =
`people`.`id`
WHERE `services`.`type` = 1
INTERSECT
SELECT id
FROM `people` INNER JOIN `services` ON `services`.`person_id` =
`people`.`id`
WHERE `services`.`type` = 2 )
that won't work on mysql. Maybe it's an arel bug, I don't know. Anyway, I
think active record should implement & and | methods.
PS: I used a one-to-many relationship when writing arel code, only for
simplicity sake (it saves me a lot of typing).
On Friday, August 10, 2012 12:42:23 AM UTC-3, Rafael Almeida wrote:
>
> 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 <[email protected]>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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> 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/-/9t0fM-NiDdEJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
