Rails: return records for which relationships do not exist

Question

Using an active record, how can I return the results for a model if they do not exist in certain respects elsewhere. For example, let's say I have a Recipe model that belongs to categories (via the category_recipes join table).

So basically I have a Rake task that looks at each recipe and its description and tries to paste it into a category, but the task takes a lot of time and I will need to run it on a regular basis, so I only want to run it if the Recipe question wasn’t already classified, and I would prefer not to add an extra column, for example categorized , so I believe that there is a way to get only Recipes that do not exist in the join table. Any thoughts?

Answer 1

You can solve this query with LEFT OUTER JOIN :

 Recipe.joins('LEFT OUTER JOIN recipe_categories ON recipes.id = recipe_categories.recipe_id').where('recipe_categories.recipe_id IS NULL') 

Answer 2

You will have to solve it yourself today and think that this will do the trick:

 Recipe.includes(:categories).where('categories.id IS NULL').references(:categories) 

Answer 3

You can do this with SQL select, perhaps.

 @uncategorized_recipes = Recipe.find_by_sql("select * from recipes where id not in ( select recipe_id from category_recipes )") 

The exact syntax may vary depending on your database.

Answer 4

You can do this using the pluck method for the recipe and CategoryReceipe.

Steps:

• r = Receipe.pluck (: id)

  # prints an array of all identifiers inside the model. Example: [1, 2, 3, 4, 5 ...]

• cr = CategoryRecipe.pluck (: recipe_id)

  # provides an array of all identifiers from the recipe_id column found.

• remainder = r - cr

  #this takes two id and recipe_id arrays and computes a new array. This new array will only have identifiers that are not in CategoryRecipe.

Then you can start your operation in this new array: Recipe.where (id: remainder) .count

# contains the number of records that need to be updated

I prefer this method because you can turn it into a separate function that you can use when evaluating multiple associations without rewriting the code. It is also very easy to check.

Answer 5

I have not tested it, but you can try

 Recipe.joins(:categories).select('recipes.*, categories.count(*) as category_count').where(:category_count => 0).group('recipes.id')