Question about Queries

Using Django
1

Hello everyone!

I know this is a real beginners question…
But i really cant seem to find a way to get this working.

I have the following querie:

SELECT sum(amount * kcal) FROM journal_data_personaljournal

LEFT JOIN journal_data_foodtype
ON journal_data_personaljournal.food_id = journal_data_foodtype.id
LEFT JOIN journal_data_foodvalues
ON journal_data_foodtype.nutritions_id = journal_data_foodvalues.id

where date=current_date()

Example output (See Image) : 85
image

When i try to use the “Manager.raw” it outputs this on my page

<RawQuerySet: SELECT sum(amount * kcal) FROM journal_data_personaljournal LEFT JOIN journal_data_foodtype ON journal_data_personaljournal.food_id = journal_data_foodtype.id LEFT JOIN journal_data_foodvalues ON journal_data_foodtype.nutritions_id = journal_data_foodvalues.id where date=current_date()>

Thank you :slight_smile:

2

You’ll need to iterate over the QuerySet to get the value: e.g. this answer on StackOverflow.

3

Thanks for the reply! i tried that… But i must be doing something wrong. Do i have to do that in my view function? Or in the template?

When i load view:

Raw query must include the primary key, but in my sql there is no ID since it is just a sum

4

You should use the ORM. The query that corresponds to your SQL will be something like:

PersonalJournal.objects.filter(date=timezone.now()).aggregate(
    total_calories=Sum(F("foodtype__amount") * F("foodtype__foodvalues__kcal"))
)["total_calories"]

(untested)

See the aggregation guide.

1 Like
5

Thank you! I think you are right… using raw sql just gives me more challenges …