Try this:
Project.joins(:services).where(:services => { :id => [1,2]
}).group(:id).having("count(*) = 2").all
This produces to me:
"SELECT `projects`.* FROM `projects` INNER JOIN `services` ON
`services`.`project_id` = `projects`.`id` WHERE `services`.`id` IN (1, 2)
GROUP BY id HAVING count(*) = 2"
Which returns me only the projects who are associated to both services 1
and 2.
On Fri, Aug 10, 2012 at 1:16 AM, Rafael Almeida <[email protected]>wrote:
> 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<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<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 rubyonrails-co...@**
>>>> googlegroups.com.
>>>> For more options, visit this group at http://groups.google.com/**
>>>> group/rubyonrails-core?hl=en<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.
>
--
Att,
Everton
http://www.evertonmoreth.com.br
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
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.
