4

Basically, I have a Listing model, where each listing has a country id. I need the country name in my search results view. I know I can do @listing.country.name, but this performs an extra query for each listing in my search results. I'm using Thinking Sphinx, and in my controller I have

@listings = Listing.search(@ts_params).page(page_num).per(limit)

I have tried adding .includes(:countries) and variations thereof but no luck.

What's the best way to go about this? I want the country data to be fetched in the same query as the listings.

I have exactly the same issue with listing images - it is performing an extra query for every listing to find the image, when surely it can be done in one with joins.

arserbin3
  • 6,010
  • 8
  • 36
  • 52
Dave
  • 1,051
  • 1
  • 10
  • 20

4 Answers4

2

Are you trying to eager load the associated model (to avoid an N + 1 query problem), or are you trying to load the associated model into fields on the parent model?

If it's the former, you're probably better off forgetting about :select and instead of :joins using: 

ts_params[:sql][:include] = :countries, :listing_images

Now you should be able to call listing.countries and listing.listing_images to access child models, as normal.

Taavo
  • 2,406
  • 1
  • 17
  • 17
  • Thanks taavo, at the moment I have the select/joins working, as I figured less queries is better? The :include option would work too but it loads a lot of unnecessary data, as I only need one 'listing_image' result for each listing. Perhaps I could create a new association for the listing - has_one :main_image which just pulls one image? – Dave Sep 25 '13 at 18:20
  • Yeah, if you only need one, and you want to be able to load it using activerecord's `includes` (here triggered via `:include`), then you're going to need a `has_one`. – Taavo Sep 25 '13 at 19:27
1

Thinking Sphinx provides functionality to eager load associated entities, so for eager loading we don't need to add [:sql]. Following is the way to do this.
For eager loading associated entities using sphinx.

ts_params[:include] = [:country, :listing_image]
Aamir
  • 16,329
  • 10
  • 59
  • 65
0

I managed to solve this using the :sql hash provided by Thinking Sphinx. I now have the following:

@ts_params[:sql][:joins] = "INNER JOIN countries ON countries.id = listings.country_id INNER JOIN listing_images ON listing_images.listing_id = listings.id"
@ts_params[:sql][:select] = "listings.*, countries.name as country_name, listing_images.image as image_name"

This is correctly retrieving the country name and image name, but I still have a bit of work to do in making it work with the images - I think that will deserve its own question!

Dave
  • 1,051
  • 1
  • 10
  • 20
0

Current v4 syntax is:

Article.search :sql => {:include => :user}

Ref: https://freelancing-gods.com/thinking-sphinx/v4/searching.html

Kiryl Plyashkevich
  • 2,157
  • 19
  • 18