Re: [Rails-core] 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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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 almeida...@gmail.comwrote:
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=71http://www.bitbybit.dk/carsten/blog/?p=71on
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.comwrote:
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/-/**jz3f6lJrXEkJhttps://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/**
Re: [Rails-core] Activerecord: how to write a new feature?
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 pnascime...@gmail.comwrote: 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 almeida...@gmail.comwrote: 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+unsubscr...@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+unsubscr...@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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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.comjavascript:
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.comjavascript:
.
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.
Re: [Rails-core] Activerecord: how to write a new feature?
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.comjavascript: 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.comjavascript: 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.comjavascript: . 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.comjavascript: . 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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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.comwrote:
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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
[Rails-core] Activerecord: how to write a new feature?
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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Re: [Rails-core] Activerecord: how to write a new feature?
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 almeida...@gmail.comwrote: 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+unsubscr...@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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
