Jump to content

The ultimate community for Ruby on Rails developers.


Rails 4 query nested resources after geocoded

geocoded activerecord

  • Please log in to reply
3 replies to this topic

#1 tjsherrill



  • Members
  • 11 posts

Posted 23 April 2014 - 10:21 PM

I am running a basic rails 4 app that uses Google maps to locate users on a map.

I am trying to write a query that finds a set of users by multiple attributes. Relationships are:

User has_one :location
User has_many :children

Location belongs_to :user
Children belongs_to :user

@close = Location.near([current_user.location.latitude, current_user.location.longitude], params[:distance])
@close = @close.joins(user: :children).where('locations.users.children.count <= ?', params[:number_of_children]) if params[:number_of_children].present?
I am using the Geocoded gem, which provides the .near method. So I query a set of locations by params[:distance], then based on that set of objects, I need to query the number of children.count <= params[:number_of_children].
PG::FeatureNotSupported: ERROR: cross-database references are not implemented: locations.users.children.count

The initialization of the @close instance var, gets an list of locations near a given location. I am then trying to filter those objects by a given associated locations.users.children.count.

#2 Ohm



  • Moderators
  • 529 posts
  • LocationCopenhagen

Posted 24 April 2014 - 06:50 AM


@close = @close.joins(user: :children).where('locations.users.children.count <= ?', params[:number_of_children]) if params[:number_of_children].present?

won't generate any valid SQL. What you probably want is either the HAVING SQL term or doing a sub-select with the count like so

@close = @close.joins(:user).where("(SELECT COUNT(*) FROM children WHERE children.user_id = users.id) <= ?", params[:number_of_children]) if params[:number_of_children].present?

Blog: http://ohm.sh | Twitter: @madsohm | Work: Lokalebasen.dk

#3 tjsherrill



  • Members
  • 11 posts

Posted 24 April 2014 - 03:54 PM

Thanks for the note.  I'll give this a try, a friend also recommended using a counter_cache which seems like a valid option as well.


I'll post back once I solve.



#4 sidk



  • Members
  • 58 posts

Posted 24 May 2016 - 09:12 PM

Hey there, 


If anyone stumbles on this, you might find the following article I wrote useful:




In it, I go over a few options that are available when you want to filter your associations/nested resources.

Join my free, 4-part course on Managing Technical Debt in your Rails app at http://email-course.ducktypelabs.com

Also tagged with one or more of these keywords: geocoded, activerecord

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users