Rails activerecord joins and duplicates data

Question

I'm having a route model

class Route < ActiveRecord::Base
  has_many :etapes
  acts_as_mappable :through => :steps
end

and a step one (that contains lat and lgn)

class Step ActiveRecord::Base
  belongs_to :route
  acts_as_mappable
end

I'm trying to get the closest route to a given point.

With this request Route.joins(:etapes).within(10, :origin => [1.23456,-5.3269]) I can get the routes but I got duplicates informations (because this route has many steps closes to the given point):

#<ActiveRecord::Relation [
#<Route id: 1, created_at: "2016-03-26 21:53:01", updated_at: "2016-03-26 21:53:01">, 
#<Route id: 1, created_at: "2016-03-26 21:53:01", updated_at: "2016-03-26 21:53:01">
]>

What can I do to remove duplicates entries ?

`Route.joins(:etapes).within(10, :origin => [1.23456,-5.3269]).uniq` - try this — dp7, Apr 27 '16 at 10:03

score 0 · Accepted Answer · answered Apr 27 '16 at 11:47

0

My easy go-to would be

Route.joins(:etapes).within(10, :origin => [1.23456,-5.3269]).group(:id)

This generates a GROUP BY `routes`.`id` which for basic queries will get you what you want. If you need to COUNT these, though, then you need to change strategies, since the COUNT will apply within-group.

I tend to avoid the uniq solution for this purpose as I've found it to perform miserably. Not sure if that generalizes, but it may have to do with it generating a SELECT DISTINCT `routes`.*, which compares all columns even though you really only need the DISTINCT by id.

answered Apr 27 '16 at 11:47

Andrew Schwartz

4,440
3
25
58

Nice one. @AndrewSchwartz If I want to get all routes that match 2 steps, how could I do this ? I want the result of Route.joins(:etapes).within(10, :origin => [1.23456,-5.3269]).group(:id) and Route.joins(:etapes).within(10, :origin => [43.604408, 1.440736]).group(:id) – Alain ANDRE Apr 27 '16 at 12:50
I wonder if acts_as_mappable has a solution for this. If not, my instinct would be to go with table aliases, which allow you to join to the same table under two independent conditions. I'd need to know what the `within` method is doing to know how to apply this idea. The general idea is to say `Foo.joins("inner join bars b1 on b1.foo_id = foos.id and inner join bars b2 on b2.foo_id and ")`. It's just not clear to me that you can do this without hacking into the `within` method, though. – Andrew Schwartz Apr 27 '16 at 13:16
Thanks, I'm going to ask them. – Alain ANDRE Apr 27 '16 at 13:38

Rails activerecord joins and duplicates data

1 Answers1