Re: [Rails-core] Activerecord: how to write a new feature?

[Pedro Nascimento]

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.

[Everton Moreth]

Isn't the case where a simple "group by X having count(*) = 2" could handle the problem ?

Arel can achieve this query easily.

--

Att,

Everton
http://www.evertonmoreth.com.br

[Rafael Almeida]

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.

[Rafael Almeida]

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.

[Rafael Almeida]

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

[Everton Moreth]

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.

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

[Rafael Almeida]

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? 

[Matt Jones]

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

[Jonathan Lozinski]

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)

[Rafael Almeida]

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

[Rodrigo Rosenfeld Rosas]

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