select from products_product left join vips on vips user_id user_id an

  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
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
select *
from products_product
left join vips on vips.user_id = {user_id} and vips.game_id = {game_id}
where
case visibility = 'vip'
then vips.user_id is not null
else visibility = 'xye vip'
then vips.user_id is null
else true
end
У товара есть поле visibiltity, оно указывает на то что товар должен быть показан только вип пользователям , только не вип пользователям и всем (вип и не вип)
сделать unique-together game_id-user_id
спросить будут ли файлы большими, точнее что будет после перезаписи фала происходить
подумать - нужно ли индексировать
при повторной загрузке цсв файла - данные просто добавляются, удалять можно через админку - галочки руками
для разных игр разные VIP пользователи.
=>
select p.id, user_id
from products_product as p
left join products_vips as v on v.game_id=22 and v.user_id=38586872
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;
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;
users:
games ---> products: game_id , visibility(v, i ,a)
vips: game_id, user_id
front get:
{
"game_id_1": [product1, product2],
"game_id_2": [product3, product4],
...
}
query.annotate(visible=Case(When(visibility='v', then=Value(F('vips') is not None)), When(visibility='i', then=Value(F('vips') is None)), default=Value(True), output_field=BooleanField()))
query.annotate(visible=Case(When(visibility='v', then='vips' is not None), When(visibility='i', then='vips' is None), default=Value(True), output_field=BooleanField()))
#last previouse solution
query = Product.objects
vips = Vips.objects.filter(game_id=OuterRef('game_id'), user_id=38593023)
query = query.annotate(vips=Subquery(vips.values_list('user_id', flat=True)))
query = query.annotate(
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)
Case(When(visibility='v', then=Q(vips__isnull=False)), When(visibility='i', then=Q(vips__isnull=True)), default=Value(True), output_field=BooleanField())
#solution with 2 left joins
query.annotate(visible=Case(When(visibility='v', then=Q(game__vips__user=38593023)), When(visibility='i', then=~Q(game__vips__user=38593023)), default=Value(True), output_field=BooleanField())).filter(visible=True).values_list('id', flat=True)
#simple solution
p = query.annotate(visible=Case(When(visibility='v', then=Q(game__vips__user_id=user)), When(visibility='i', then=~Q(game__vips__user_id=user)), default=Value(True), output_field=BooleanField())).filter(visible=True).distinct().values_list('id', flat=True)
------------------
p1 = query.annotate(vip_user=F('game__vips__user_id'))
res_p1 = p1.annotate(visible=Case(When(visibility='v', then=Q(vip_user=user)), When(visibility='i', then=~Q(vip_user=user)), default=Value(True), output_field=BooleanField())).filter(visible=True).distinct().values_list('id', flat=True)
query.annotate(visible=Case(When(Q(visibility='v') & Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=True), When(Q(visibility='v') & ~Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=False), When(Q(visibility='i') & Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=False), When(Q(visibility='i') & ~Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=True), default=Value(True), output_field=BooleanField())).filter(visible=True).distinct().values_list('id')
query.annotate(visible=Case(When(Q(visibility='v') & Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=True), When(Q(visibility='v') & ~Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=False), When(Q(visibility='i') & Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=False), When(Q(visibility='i') & ~Q(game__vips__user=user) & Q(game__vips__game=F('game')), then=True), default=Value(True), output_field=BooleanField())).filter(visible=True).distinct().values_list('id')
SELECT `products_product`.`id` 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` = 38592790)) 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` = 38592790)) IS NULL) ELSE 1 END = 1 ORDER BY `products_product`.`priority` DESC LIMIT 21; args=('v', 38592790, 'i', 38592790, True, True)
!!!!!!!!!!!!!!!!!!!===========================
r = query.prefetch_related(Prefetch('game__vips', queryset=Vips.objects.filter(game=F('game'), user=user2)))
t = r.annotate(visible=Case(When(visibility='v', then=Q(game__vips__isnull=False)), When(visibility='i', then=Q(game__vips__isnull=True)), default=Value(True), output_field=BooleanField())).filter(visible=True).distinct().values_list('id', flat=True)