Sequelize get random entry with association and limit, "missing FROM-clause entry

Question

Im trying to get a random entry from a table and its associations.

Recipe.findAll({
order: [
  [sequelize.fn('RANDOM')] 
],
where: {
  '$RecipeCategory.name$': category
},
include:[
{
  model: models.Category,
  as: 'RecipeCategory',
},{
  model: models.Product,
}],
subQuery:false,
limit:1})

With the above code I'm getting a random entry of Recipe and its associations with limit 1. For example , it returns only 1 product and I need all products. I need to get one recipe with all products.

Any suggestions?

If I remove the subQuery option , I receive this: SequelizeDatabaseError: missing FROM-clause entry for table "RecipeCategory"

Im searching 5 days for the solution and I think that I have check all related answers in this.'

edit: generated query

SELECT "Recipe"."id", "Recipe"."name", "Recipe"."description", "Recipe"."pic", "Recipe"."createdAt", "Recipe"."updatedAt", "RecipeCategory"."id" AS "RecipeCategory.id", "RecipeCategory"."name" AS "RecipeCategory.name", "RecipeCategory"."description" AS "RecipeCategory.description", "RecipeCategory"."createdAt" AS "RecipeCategory.createdAt", "RecipeCategory"."updatedAt" AS "RecipeCategory.updatedAt", "RecipeCategory.Recipes_Categories"."createdAt" AS "RecipeCategory.Recipes_Categories.createdAt", "RecipeCategory.Recipes_Categories"."updatedAt" AS "RecipeCategory.Recipes_Categories.updatedAt", "RecipeCategory.Recipes_Categories"."CategoryId" AS "RecipeCategory.Recipes_Categories.CategoryId", "RecipeCategory.Recipes_Categories"."RecipeId" AS "RecipeCategory.Recipes_Categories.RecipeId", "Products"."id" AS "Products.id", "Products"."name" AS "Products.name", "Products"."protein" AS "Products.protein", "Products"."fat" AS "Products.fat", "Products"."carbohydrates" AS "Products.carbohydrates", "Products"."salt" AS "Products.salt", "Products"."min" AS "Products.min", "Products"."max" AS "Products.max", "Products"."vegan" AS "Products.vegan", "Products"."piece" AS "Products.piece", "Products"."createdAt" AS "Products.createdAt", "Products"."updatedAt" AS "Products.updatedAt", "Products.Product_Recipe"."position" AS "Products.Product_Recipe.position", "Products.Product_Recipe"."createdAt" AS "Products.Product_Recipe.createdAt", "Products.Product_Recipe"."updatedAt" AS "Products.Product_Recipe.updatedAt", "Products.Product_Recipe"."ProductId" AS "Products.Product_Recipe.ProductId", "Products.Product_Recipe"."RecipeId" AS "Products.Product_Recipe.RecipeId"
FROM
    "Recipes" AS "Recipe"
    LEFT OUTER JOIN (
        "Recipes_Categories" AS "RecipeCategory.Recipes_Categories"
        INNER JOIN
        "Categories" AS "RecipeCategory" ON "RecipeCategory"."id" = "RecipeCategory.Recipes_Categories"."CategoryId"
    ) ON "Recipe"."id" = "RecipeCategory.Recipes_Categories"."RecipeId"
    LEFT OUTER JOIN (
        "Product_Recipes" AS "Products.Product_Recipe"
        INNER JOIN "Products" AS "Products" ON "Products"."id" = "Products.Product_Recipe"."ProductId"
    ) ON "Recipe"."id" = "Products.Product_Recipe"."RecipeId"
WHERE "RecipeCategory"."name" = 'meal-3'
ORDER BY RANDOM()
LIMIT 1;

Console log the query and include the console.log in your question. For instructions on logging your queries see this link http://stackoverflow.com/questions/21427501/how-can-i-see-the-sql-generated-by-sequelize-js — Patrick Motard, Aug 02 '16 at 17:01

score 0 · Accepted Answer · answered Aug 02 '16 at 20:13

It looks like using an include on the same level as where is not supported as of this thread in 2015. It's not clear what the solution would be in your case.

A relavant quote from the above thread by a sequelize contributor:

include.where won't since you need a negative query. You could probably try a raw where. .and({filter_level: ...}, ['RAW QUERY']})

On a side note.. On the off chance that you aren't fully commited to using sequelize, I personally recommend using knex.js. I use it in production code. It's a good balanced library. You get a lot of the flexibility/ease of use of an ORM like sequelize, without losing too much sight/control of the query itself. It's very easy to add/run raw queries if the library doesn't support what you do.

This is pretty rough because I'm pretty sure that either sequelize is overcomplicating this or your database structure is over complicated. This shouldn't require 4 joins! Here's the general idea:

assume your db auto incrememts the recipes id
get number of recipes from recipes table, using COUNT()
generate a random number between 0 and COUNT(), this is the id of your random recipe
query the recipe table for for the recipe, left join on the products table to get, join on recipe id. This is a simple query that can be written easily in knex.
Profit! you now have a random recipe and all products required for the recipe.

Sequelize get random entry with association and limit, "missing FROM-clause entry

1 Answers1