Questions about Django's Subquery expressions
Subquery expressions were added in Django 1.11.
Questions tagged [django-subquery]
30 questions
1
vote
1 answer
Django annotate by sum of two values with multiple relations
I have 4 models:
class App(models.Model):
...
class AppVersion(models.Model):
app = models.ForeignKey(App)
version_code = models.IntegerField()
class Meta:
ordering = ('-version_code',)
...
class Apk(models.Model):
…
artem
- 16,382
- 34
- 113
- 189
1
vote
2 answers
How to get table alias for inner join in django
I'm using django 2.1, python 3.6 and SQL Server 2012 as backend. I have following models:
class ModelA(models.Model):
name = models.CharField(...)
value = models.PositiveIntegerField(...)
class ModelB(models.Model):
name =…
binaryEcon
- 380
- 4
- 12
1
vote
1 answer
Nested subqueries in Django
Getting into deep water with Subquery. I have a set of Carparks. Carparks have multiple Bookings. Bookings have many BarrierActivity records, which are the various coming and going events at the barriers. These are all simple FKs up the stack.
It is…
Oli
- 235,628
- 64
- 220
- 299
0
votes
1 answer
How can I ensure correct grouping in a Django subquery annotation?
When I'm writing Django Subquery expressions, sometimes I have no idea how Django is going to group the result.
Example:
subquery = (
RelatedModel.objects.filter(
category="some_category",
…
0
votes
1 answer
Django ORM: This queryset contains a reference to an outer query and may only be used in a subquery
In the queryset, I want to get the average of what subquery returns and then group by 'store_id' and 'avg_sales'. However, when I used the following queries:
subquery = StoreStatistics.objects.filter(
store=OuterRef("store_id"),
…
Jasur
- 99
- 2
- 6
0
votes
2 answers
How to use order_by with last_added manytomanyfield?
Models.py
class ChatRoomId(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
created_date = models.DateTimeField(auto_now_add=True)
class MessageContent(models.Model):
content =…
Shankar Balaji U
- 1
- 3
0
votes
1 answer
Django Annotation Count with Subquery & OuterRef
I'm trying to create a high score statistic table/list for a quiz, where the table/list is supposed to be showing the percentage of (or total) correct guesses on a person which was to be guessed on. To elaborate further, these are the models which…
BigNoob1304
- 11
- 1
- 5
0
votes
1 answer
Django query to fetch top performers for each month
I need to fetch the top performer for each month, here is the below MySql query which gives me the correct output.
select id,Name,totalPoints, createdDateTime
from userdetail
where app=4 and totalPoints in ( select
max(totalPoints)
FROM…
0
votes
1 answer
Django ORM create nested query prevent using `having`
Assume I have a Transaction model which has following fields [token, pair_token, amount, related_transaction], I want to generate a query like this in mysql:
SELECT token_id, pair_token_id, (
SELECT ABS(t3.amount / t2.amount) price
FROM
…
motam
- 677
- 1
- 6
- 24
0
votes
1 answer
Annotating with the count of a subquery filtering by jsonb fields in Django
I have a single model with a jsonb field. There is a value inside this jsonb field that can be shared amongst other rows. I am trying to get the count of a subquery while filtering by this jsonb field.
Some pseudo code of what I have been attempting…
anthony-dandrea
- 2,583
- 7
- 26
- 46
0
votes
1 answer
Perform arithmetic operations on Django Subquery value
In our system, there are "Car" objects that are associated with "User" objects. With a subquery, I want to count a specific set of Car objects, perform an arithmetic operation on the result and update the User object with that value. The problem is…
archer
- 73
- 1
- 1
- 8
0
votes
1 answer
Aggregate annotated field from Subquery in Django
I'm trying to implement a subquery with Django ORM, but I can't find a working solution.
The SQL query that I would need to reverse-engineer is:
select t1.location, sum(t1.value_relative::numeric) as total
from (
select
…
Stefano Luoni
- 81
- 1
- 8
0
votes
1 answer
Django Exists() / ~Exists() return if there is no matching data?
EDIT:
As per schillingt's answer below I have switched to using Case/When:
context['db_orders'] = Order.objects.filter(
retailer_code=self.object.retailer_code).annotate(
…
cakethief
- 3
- 3
0
votes
2 answers
Django filter on date contained by any date range
I'm trying to filter a model which has a DateField (date) to retrieve a queryset of instances whose date is in any one of a list of DateRanges but I'm struggling to figure out the exact logic I need.
So for example, if I have the following…
bodger
- 1,112
- 6
- 24
0
votes
1 answer
Sum of Subquery fields django
I have the following subquery:
Subquery(
ContestTaskRelationship.objects.filter(
contest=contest,
solved=OuterRef('id')
).values('cost').all()
)
I need then to annotate my QuerySet with sum of cost values…
pomo_mondreganto
- 2,028
- 2
- 28
- 56