Нормальный запрос на sql select id user_id from products_product as le

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Нормальный запрос на sql
select p.id, user_id from products_product as p
left join products_vips as v on v.game_id=p.game_id and v.user_id=38592790
where case
when p.visibility='v' then v.user_id is not null
when p.visibility='i' then v.user_id is null
else true
end;
То что генерирует джанга при попытке его воссоздать через орм:
Product.objects.all().annotate(
vips=Subquery(Vips.objects.filter(game=OuterRef('game'), user=user3).values('user')[:1]),
visible=Case(When(visibility='v', then=Q(vips__isnull=False)),
When(visibility='i', then=Q(vips__isnull=True)),
default=Value(True),
output_field=BooleanField()
)
).filter(visible=True).distinct().values_list('id', flat=True)
SELECT DISTINCT `products_product`.`id`, `products_product`.`priority` FROM `products_product`
WHERE CASE
WHEN (`products_product`.`visibility` = 'v')
THEN ((SELECT U0.`user_id` FROM `products_vips` U0 WHERE (U0.`game_id` = (`products_product`.`game_id`) AND U0.`user_id` = 38587266) LIMIT 1) IS NOT NULL)
WHEN (`products_product`.`visibility` = 'i')
THEN ((SELECT U0.`user_id` FROM `products_vips` U0 WHERE (U0.`game_id` = (`products_product`.`game_id`) AND U0.`user_id` = 38587266) LIMIT 1) IS NULL)
ELSE 1
END = 1
ORDER BY `products_product`.`priority` DESC LIMIT 21; args=('v', 38587266, 'i', 38587266, True, True)