Take a look at ARel and Squeel gem. Squeel gem supports something like this already.
Isn’t the case where a simple “group by X having count(*) = 2” could handle the problem ?
Arel can achieve this query easily.
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.
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.
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).
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
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:
Added initial stab at an or scope operator by loz · Pull Request #6817 · rails/rails · GitHub
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)
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
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…