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 [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.
