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 rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
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 <almeidaraf@gmail.com>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 rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com. > 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 post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Isn''t the case where a simple "group by X having count(*) = 2" could handle the problem ? Arel can achieve this query easily. On Wed, Aug 8, 2012 at 2:27 PM, Pedro Nascimento <pnascimento@gmail.com>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 <almeidaraf@gmail.com>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 rubyonrails-core@googlegroups.com. >> To unsubscribe from this group, send email to >> rubyonrails-core+unsubscribe@googlegroups.com. >> 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 post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com. > 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
Unless I misunderstood you, I think that would only work if there were only 2 possible service types and I grouped by person_id. I have something a little more general in mind. On Thursday, August 9, 2012 9:26:24 AM UTC-3, EMoreth wrote:> > Isn''t the case where a simple "group by X having count(*) = 2" could > handle the problem ? > > Arel can achieve this query easily. > > On Wed, Aug 8, 2012 at 2:27 PM, Pedro Nascimento <pnasc...@gmail.com<javascript:> > > 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 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. >> > > > > -- > 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 view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/7eI-cbUHGKoJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
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
<almei...@gmail.com>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.
>>> 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.
>>>
>>
>>
--
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 rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
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
<almeidaraf@gmail.com>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
<almei...@gmail.com>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
rubyonra...@googlegroups.com.
>>>> 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 rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-core+unsubscribe@googlegroups.com.
> 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 rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
On Friday, August 10, 2012 5:50:58 PM UTC-3, EMoreth wrote:> > 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. >Indeed that does the trick. Nice thinking. It''s probably even better than the intersection approach from SQL point of view. Anyway, the reason we don''t have & and | operators in activerecord is that it''s believed that there''s always a better way to write a query not using them? -- 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/-/rwPktzz3Kt8J. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On Aug 11, 2012, at 6:23 PM, Rafael Almeida wrote:> On Friday, August 10, 2012 5:50:58 PM UTC-3, EMoreth wrote: > 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. > > Indeed that does the trick. Nice thinking. It''s probably even better than the intersection approach from SQL point of view. > > Anyway, the reason we don''t have & and | operators in activerecord is that it''s believed that there''s always a better way to write a query not using them?I''d say it''s more that it''s phenomenally difficult to come up with a *generic* method that will transform (for instance) your two queries: Person.joins(:services).where(''services.type'' => 1) Person.joins(:services).where(''services.type'' => 2) into the final query. Note that the suggestion above is only correct if you never have Project with two links to the same service. Based on the what you''ve described about your domain, this is probably a sensible assumption - but the general case wouldn''t necessarily be able to assume that. Full support for ''or'' is a similar problem - there''s a first-draft here: https://github.com/rails/rails/pull/6817 but that code isn''t going to work correctly for anything but fairly similar queries; joins, selects, etc all go out the window. --Matt jones -- 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
> > On Aug 11, 2012, at 6:23 PM, Rafael Almeida wrote: > >> On Friday, August 10, 2012 5:50:58 PM UTC-3, EMoreth wrote: >> 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. >> >> Indeed that does the trick. Nice thinking. It''s probably even better than the intersection approach from SQL point of view. >> >> Anyway, the reason we don''t have & and | operators in activerecord is that it''s believed that there''s always a better way to write a query not using them? > > I''d say it''s more that it''s phenomenally difficult to come up with a *generic* method that will transform (for instance) your two queries: > > Person.joins(:services).where(''services.type'' => 1) > Person.joins(:services).where(''services.type'' => 2) > > into the final query. Note that the suggestion above is only correct if you never have Project with two links to the same service. Based on the what you''ve described about your domain, this is probably a sensible assumption - but the general case wouldn''t necessarily be able to assume that. > > Full support for ''or'' is a similar problem - there''s a first-draft here: > > https://github.com/rails/rails/pull/6817 > > but that code isn''t going to work correctly for anything but fairly similar queries; joins, selects, etc all go out the window. >I wrote that pull request. I''d hoped to get some input into making it not loose the selects and joins. It should be possible for it to make something which is more useful, I just couldn''t work out a way to rewrite only part of a scope internally. (i.e. the where clauses orred only) -- 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On Sunday, August 12, 2012 12:06:00 AM UTC-3, Matt jones wrote:> > > On Aug 11, 2012, at 6:23 PM, Rafael Almeida wrote: > > > On Friday, August 10, 2012 5:50:58 PM UTC-3, EMoreth wrote: > > 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. > > > > Indeed that does the trick. Nice thinking. It''s probably even better > than the intersection approach from SQL point of view. > > > > Anyway, the reason we don''t have & and | operators in activerecord is > that it''s believed that there''s always a better way to write a query not > using them? > > I''d say it''s more that it''s phenomenally difficult to come up with a > *generic* method that will transform (for instance) your two queries: > > Person.joins(:services).where(''services.type'' => 1) > Person.joins(:services).where(''services.type'' => 2) > > into the final query. Note that the suggestion above is only correct if > you never have Project with two links to the same service. Based on the > what you''ve described about your domain, this is probably a sensible > assumption - but the general case wouldn''t necessarily be able to assume > that. >I get you now. I have studied activerecord internals a little bit more and I figure there''d need to be a major rewrite to support it. For one, if you have (Person.joins(:services).where(''services.type'' => 1) & Person.joins(:services).where(''services.type'' => 2)).where(:name => ''John'') we''d need to keep track that "where(:name => ''John'')" should be applied to the subquery, while the other where''s should be used to create the subqueries. Right now it seems like arel doesn''t even support "where" on unions and intersections. q1.intersect(q2) returns an Intersection object which doesn''t have any query methods. For activerecord to ever have such a feature work has to be done in arel first. If anyone else is interested, people seem to already be working on that issue, at least for union: https://github.com/rails/arel/issues/98 -- 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/-/gUWWCZ5cLi4J. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Rodrigo Rosenfeld Rosas
2012-Aug-14 11:34 UTC
Re: Activerecord: how to write a new feature?
Em 13-08-2012 21:49, Rafael Almeida escreveu:> > > On Sunday, August 12, 2012 12:06:00 AM UTC-3, Matt jones wrote: > > > On Aug 11, 2012, at 6:23 PM, Rafael Almeida wrote: > > > On Friday, August 10, 2012 5:50:58 PM UTC-3, EMoreth wrote: > > 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. > > > > Indeed that does the trick. Nice thinking. It''s probably even > better than the intersection approach from SQL point of view. > > > > Anyway, the reason we don''t have & and | operators in > activerecord is that it''s believed that there''s always a better > way to write a query not using them? > > I''d say it''s more that it''s phenomenally difficult to come up with > a *generic* method that will transform (for instance) your two > queries: > > Person.joins(:services).where(''services.type'' => 1) > Person.joins(:services).where(''services.type'' => 2) > > into the final query. Note that the suggestion above is only > correct if you never have Project with two links to the same > service. Based on the what you''ve described about your domain, > this is probably a sensible assumption - but the general case > wouldn''t necessarily be able to assume that. > > > I get you now. I have studied activerecord internals a little bit more > and I figure there''d need to be a major rewrite to support it. For > one, if you have > > (Person.joins(:services).where(''services.type'' => 1) > & Person.joins(:services).where(''services.type'' => 2)).where(:name => > ''John'') > > we''d need to keep track that "where(:name => ''John'')" should be > applied to the subquery, while the other where''s should be used to > create the subqueries. Right now it seems like arel doesn''t even > support "where" on unions and intersections. q1.intersect(q2) returns > an Intersection object which doesn''t have any query methods. >I''d really urge you to evaluate Sequel instead of rewriting Arel. Sequel already supports queries like those and much more complex ones and it is well tested. Maybe it would be much easier to adapt Sequel to AR API than to try to rewrite the Sequel advanced features in Arel... -- 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 rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.