Jump to content

The ultimate community for Ruby on Rails developers.


Photo

Rails 4 query nested resources after geocoded

geocoded activerecord

  • Please log in to reply
2 replies to this topic

#1 tjsherrill

tjsherrill

    Passenger

  • Members
  • 8 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

Ohm

    Driver

  • Moderators
  • 398 posts
  • LocationCopenhagen

Posted 24 April 2014 - 06:50 AM

This

@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

#3 tjsherrill

tjsherrill

    Passenger

  • Members
  • 8 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.

 

thanks







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