Raw sql query issue in Django

Question

I am trying to to make an SQL query to impliment the answer given here.

The user suggested I try doing a raw sql query to solve. I am having issues implimenting what he suggests.

For example, 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, given my code gives me 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): https://stackoverflow.com/questions/23887559/how-to-pass-a-list-of-values-for-an-in-sql-clause-in-django 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, May 29 '22 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, May 29 '22 at 22:10

score 2 · Accepted Answer · edited May 29 '22 at 22:06

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 '22 at 22:06

nadermx

2,596
7
31
66

answered May 29 '22 at 21:57

Willem Van Onsem

443,496
30
428
555

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? – nadermx May 29 '22 at 22:01
@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 May 29 '22 at 22:03
@nadermx: but if you have 20 ingredients, and it should match *all*, you should indeed use 20. – Willem Van Onsem May 29 '22 at 22:04
Thank you, is this case sensative in the search too? – nadermx May 29 '22 at 22:05
@nadermx: yes, so `'egg'`, will *not* match `'Egg'` or `'EGG'`. – Willem Van Onsem May 29 '22 at 22:06
Is there any way to do this search case insensative? Also I edited the answer to make it more dynamic. Thank you – nadermx May 29 '22 at 22:07
I presume I replace the `ìn` with `icontains` in this query `recipeingredients__ingredient__in=ingredients` – nadermx May 29 '22 at 22:16

Raw sql query issue in Django

1 Answers1

Linked