Raw SQL query issue in Django

Question

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)

There are two good answers here (one for raw queries, one for using a filter with a "normal" django command with filtering): stackoverflow.com/questions/23887559/… It is probably bad form to just ask your question again because you didn't like the original answer, but I will note that you haven't spelled your variable names right (which is one thing) but you still can't just type a variable name into a raw sql statement - so as a rule you need to provide parameters which is how sql deals with variable inputs. — topsail
– topsail, Commented May 29, 2022 at 22:07
@topsail I agree although I this case I was unable to answer despite a few tries, also helps to know it can be done via the ORM. — nadermx
– nadermx, Commented May 29, 2022 at 22:10

nadermx · Accepted Answer · 2022-05-29 22:06:47Z

2

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)
)

edited May 29, 2022 at 22:06

nadermx

2,95410 gold badges40 silver badges75 bronze badges

answered May 29, 2022 at 21:57

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

7 Comments

nadermx Over a year ago

This seems to work, one question. The ningredient__gte=2 if the ingredients vary, that number needs to change? So if say it is a list of 20 ingredients, that would be 19?

willeM_ Van Onsem Over a year ago

@nadermx: we first filter on the list of ingredients (['egg', 'bacon', 'rice'], so ningredient is the number of matched ingredients, and thus here it can only be at most three.

willeM_ Van Onsem Over a year ago

@nadermx: but if you have 20 ingredients, and it should match all, you should indeed use 20.

nadermx Over a year ago

Thank you, is this case sensative in the search too?

willeM_ Van Onsem Over a year ago

@nadermx: yes, so 'egg', will not match 'Egg' or 'EGG'.

|

Collectives™ on Stack Overflow

Raw SQL query issue in Django

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related