Rails complex query setup with multiple records

Question

Im trying to figure out how to do a query in rails where with multiple ids returned from another query. I have a Food table that has_many compounds through contents. What Im trying to do is get a list of foods that share at least one compound with the original food. Currently I have the Following:

To get the compounds from the initial food i have this:

def set_food
      @food = Food.includes(:compounds).find(params[:id])
    end

This sets the food and compounds and i can output by calling each-do all the contents and show what compounds are in each food. So the next step im not sure how to do is how to get all the food_id's from the content table where compound_id equals one of the ones in the original content returned above (hopefully that makes sense). So something like this (i know this isnt right)

def show
    @pairs = Food.joins(:contents).where(contents: {compound_id: @food.contents.compound_id})
  end

Any help would be appreciated, tried googling answers but not even sure what to google to get in the right direction

Smek · Accepted Answer · 2018-08-09 06:59:12Z

1

You can also do this with a subquery like this:

@pairs = Food.joins(:contents).where(contents: { compound_id: @food.compounds.select(:id) }) Then you get a query something like:

SELECT `foods`.* FROM `foods` INNER JOIN `contents` ON `contents`.`food_id` = `foods`.`id` WHERE `contents`.`compound_id` IN(SELECT id FROM compounds WHERE ...)

This can be better for performance if you have lots of ids. If you use pluck or @food.compounds.ids of @food.compound_ids you can get a huge array of ids and pass that to your query that can become very slow.

answered Aug 9, 2018 at 6:59

Smek

1,20814 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DRing Over a year ago

This works great, but now Ive come across two issues. 1. The results take a minute or so to query, which i might have to change my approach, cause its going thru 1.5 mil records on the contents table. 2. Is there a way to only return 1 food item, foods can share more then one compound so its returning the same food multiple times

Smek Over a year ago

1. Do you have indexes on your tables? 2. You can add a group(:field_1, :field_2, ..) to your query maybe that will help to make your result set smaller. If needed you can do that in a subquery as well this makes it easier to retrieve the fields you might need.

DRing Over a year ago

thanks for the answer, got me in the right direction to move forward now

Raindal · Accepted Answer · 2018-08-09 02:38:31Z

1

You already eager loaded your compounds while getting your @food so you can do it in two steps:

First, get all the interesting compound ids related to your original food:

compound_ids = @food.compounds.pluck(:id)

I think with AR relations you should even be able to do:

# worth the try
compound_ids = @food.compound_ids

Step number two you pretty much use the same query you had before:

@pairs = Food.joins(:contents).where(contents: { compound_id: compound_ids })

The { compound_id: [array] } thing should be converted to a SQL IN statement like: contents.compound_id IN (1,2,3).

This is the most straightforward way to do it without getting into subqueries and whatnot.

answered Aug 9, 2018 at 2:38

Raindal

362 bronze badges

1 Comment

DRing Over a year ago

This worked for me, went with @Smek answer cause it returned the results a faster. But thank you for the answer, helped me realize what I was not grasping for the compound_ids

Collectives™ on Stack Overflow

Rails complex query setup with multiple records

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related