[SOLVED] Raw SQL query issue in Django

Raw SQL query issue in Django

I am trying to write a SQL query to implement the answer given here.

The user suggested I try to write a raw SQL query to solve. I am having issues implementing what he suggests.

This is what I have so far:

ingredients = ["eggs", "bacon", "salt"]
recipes = Recipe.objects.raw('select whattocook_RecipeIngredients \
    from whattocook_Recipe a \
    inner join whattocook_RecipeIngredients b \
    on a.id = b.recipe_id and b.ingredient in (ingedients) \
    group by recipeingredients \
    having count(*) >= 2')

But this does not work. His answer says to do this

recipe_list = Recipe.objects.raw('select a.*
   from app_Recipe a 
   inner join app_RecipeIngredients b
      on a.id = b.recipe_id and b.ingredient in ("egg", "bacon", "rice")
   group by a.*
   having count(*) >= 2')

maybe replace app_ with your project name, replace a.* with list of column names.

So I think I am misunderstanding which columns I need to replace, my code throws this error:

django.db.utils.ProgrammingError: column "ingedients" does not exist LINE 1: ... on a.id = b.recipe_id and b.ingredient in (ingedients...
^
HINT: Perhaps you meant to reference the column "b.ingredient".

My app is named whattocook, and the models are as follows

class RecipeIngredients(models.Model):
    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, null=True)
    ingredient = models.TextField(null=True, blank=True)
    quantity = models.CharField(max_length=10, null=True, blank=True)
    type = models.CharField(max_length=50, null=True, blank=True)
class Recipe(models.Model):
    account = models.ForeignKey(CustomUser, on_delete=models.CASCADE, null=True, blank=True)
    name = models.TextField(null=True, blank=True)
    slug = models.SlugField(null=False, blank=True, unique=True)
    image_path = models.ImageField(upload_to=MEDIA_URL, null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    preptime = models.IntegerField(null=True, blank=True)
    cookingtime = models.IntegerField(null=True, blank=True)
    cookingtimeoptions = models.CharField(max_length=100, null=True, blank=True)
    preptimeoptions = models.CharField(max_length=100, null=True, blank=True)
    servings = models.CharField(max_length=100, null=True, blank=True)
    rating_value = models.IntegerField(null=True, blank=True)
    rating_count = models.IntegerField(null=True, blank=True)
    categories = models.ManyToManyField('Category', blank=True)
    date_added = models.DateField(auto_now_add=True)

Solution

You can query with:

from django.db.models import Count
ingredients = ["eggs", "bacon", "rice"]
Recipe.objects.filter(
    recipeingredients__ingredient__in=ingredients
).alias(
    ningredient=Count('recipeingredients')
).filter(
    ningredient__gte=len(ingredients)
)