CODEWITHSUNDEEP
ruby-on-railsactiverecord
JP Silvashy
JP Silvashy

Reputation: 48525

Rails: returning records for which a relationship does not exist

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

So basically I have a Rake task that looks through each recipe and it's description and try to put in in a category, but the task takes a long time and I'll need to run it on a regular basis, so I only want to run it if the Recipe in question has not already been categorized and I'd rather not add a superfluous column like categorized, so I figure there is a way to retrieve only the Recipes that don't exist in the join table. Any thoughts?

Upvotes: 9

Views: 8217

Answers (5)

Ankit Patel
Ankit Patel

Reputation: 67

You could do this using the pluck method for Recipe and CategoryReceipe.

Steps:

  • r = Receipe.pluck(:id)

    #provides an array of all ids within the model. Ex: [1, 2, 3, 4, 5...]

  • cr = CategoryRecipe.pluck(:recipe_id)

    #provides an array all ids from recipe_id column that are present.

  • remainder = r - cr

    #this takes the two arrays of id and recipe_id and evaluates to a new array. This new array will have only those ids that are not present in CategoryRecipe.

You can then run your operation on this new array: Recipe.where(id: remainder).count

#gives you the number of records that need to be updated

I prefer this method because you can make it into a seperate function which you can use when evaluating multiple associations without rewriting code. This is also extremely easy to test.

Upvotes: 2

user486646
user486646

Reputation:

Had to solve this myself today and think this will do the trick:

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

Upvotes: 13

edgerunner
edgerunner

Reputation: 14973

I haven't tested it but you may try

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

Upvotes: 1

Jason Weathered
Jason Weathered

Reputation: 7801

You can solve this query with a LEFT OUTER JOIN:

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

Upvotes: 16

Jakob Borg
Jakob Borg

Reputation: 24435

You could do this with an 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 might vary depending on your database.

Upvotes: 3

Related Questions