Loading data from associated model in same query i

Question

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.

Answer 1

Current v4 syntax is:

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

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

Answer 2

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!

Answer 3

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]

Answer 4

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.