0

I'm using the rails 3 geokit gem that allows you to find users within a distance of a point, e.g. 

User.within(10, origin: [51.123123, 0.1323123])

['10' is the distance in mi/km; 51 & 0 are latitude and longitude]

I'm now trying to get all posts by users who are within a certain distance of a point. something like:

Post.where(User.within(10, origin: [51.123123, 0.1323123]))

(my Post model belongs_to my User model, my User model has_many posts)

Reading around it seems like the solution lies in .where, or using :through & :source, or .joins, but I haven't been able to nail it down.

I'd be very grateful for any help!

christian
  • 807
  • 1
  • 11
  • 19

4 Answers4

1

There may be a more elegant solution, but I think the simplest may be to use IN with SQL. Do something like:

Post.where(["user_id IN (?)", User.within(10, origin: [51.123123, 0.1323123]).map { |u| u.id }])

Basically, you're just sending an array of user_ids that Post.user_id could match.

Mischa
  • 42,876
  • 8
  • 99
  • 111
jbarket
  • 882
  • 7
  • 14
  • This works! Thank you. I agree though, it does seem like rails has a more conventional solution somewhere – christian Aug 15 '12 at 19:34
  • 1
    The `.map` part is not necessary. This is enough: `Post.where(user_id: User.within(10, origin: [51.123123, 0.1323123]))`. – Mischa Aug 16 '12 at 05:17
  • Sorry micha, this doesn't work, I get 'ActionView::Template::Error (PGError: ERROR: subquery has too many columns LINE 1: ...CT COUNT(*) FROM "posts" WHERE "posts"."user_id" IN (SELECT...' – christian Aug 16 '12 at 09:15
1

If your User model has_many posts, why don't you just load them directly?

@posts = User.within(10, origin: [51.123123, 0.1323123]).collect(&:posts).flatten

collect is a synonym for map, it effectively transforms an array into another array applying a transforming method or a block to every element of the original array.

In our command above, within is a named scope that returns an array of Users, collect applys the method posts to each user, thereby generating an array of posts per user. Finally, flatten takes the array of posts arrays (its a 2 dimensional array) and squishes it into one big array of posts.

You could also have a default includes in your User model to prevent the N+1 query problem. while loading Posts.

FWIW, I prefer using ActiveRecord methods (associations, etc.) instead of using raw SQL where possible.

CubaLibre
  • 1,675
  • 13
  • 22
  • I'm not familiar with .collect so forgive me if I'm doing something wrong - using this line as @posts that I then loop through in the view does not work... – christian Aug 15 '12 at 19:35
  • I have added more details to my answer. Hope this helps! Sorry for the terse answer before :) – CubaLibre Aug 16 '12 at 04:40
  • this is not efficient at all, for each user user you have to make query for posts. – Yuri Barbashov Aug 16 '12 at 07:27
  • ...it doesn't work for me though! I get 'NoMethodError (undefined method `search' for #):'. In fact my line looks like this: User.within(1000, origin: @userlocation).collect(&:posts).flatten.search(params[:search]).order('created_at desc').paginate(per_page: 7, page: params[:page]) .... but search, order and paginate work just fine on jbarket's .map solution above... and should here too no? – christian Aug 16 '12 at 09:09
  • Bottomline: TIMTOWTDI @YuriBarbashov i agree your method is more efficient. The associated posts can be eager loaded via `includes` for some efficiency gains. @user1362141 thats because the 'search' method is not defined for `Array`s :) Either ways, I didn't mean to suggest that my answer is the 'best'. – CubaLibre Aug 17 '12 at 11:24
  • Thank you CubaLibre - apologies, rookie-hour over here. So using Yuri's method now ATposts = Post.joins(:user).merge(User.within(1000, origin: [51.123123, 0.1323123])).search(params[:search]).order('created_at desc').paginate(per_page: 7, page: params[:page]) ...I get 'ActionView::Template::Error (missing attribute: user_id):' when I try to call the post.user_id in a loop through ATposts in the view... Is this a similar issue as with your method: Yuri's code is not returning something that is compatible with search, order etc? – christian Aug 17 '12 at 12:34
1

Try this solution:

class Post
  acts_as_mappable through: :user
end

Post.joins(:user).within(10, origin: [51.123123, 0.1323123])
Dmitry Dedov
  • 1,106
  • 7
  • 15
0
Post.joins(:user).merge(User.within(10, origin: [51.123123, 0.1323123]))
Yuri Barbashov
  • 5,407
  • 1
  • 24
  • 20
  • this looks like exactly what I need! But i'm getting this error currently: "ActionView::Template::Error (Association named 'users' was not found; perhaps you misspelled it?)" – christian Aug 15 '12 at 23:04
  • @user1362141, that's because it should be `Post.joins(:user)` instead of `Post.joins(:users)`. – Mischa Aug 16 '12 at 05:02
  • I don't think `merge` is appropriate here. Should just be `where` IMO. – Mischa Aug 16 '12 at 05:12
  • @Mischa IMO `where` is not appropriate here and `merge` is appropriate – Yuri Barbashov Aug 16 '12 at 07:21
  • Yuri, this still isn't working for me i'm afraid. I now get the error 'ActionView::Template::Error (missing attribute: user_id):' when the first post is called in the view... – christian Aug 16 '12 at 09:05
  • Hm, maybe you're right. Could you explain why, because the [API](http://apidock.com/rails/ActiveRecord/SpawnMethods/merge) does not give much info? – Mischa Aug 16 '12 at 11:09