Geek asks and answers

Annotating with a graph does not work, as I expect it to

I am experimenting with Django creating a simple RPG. He has armor. There are different categories of armor (for example, head and body). There are many armor in each category. For example, the category “Head” may have “Dragon Helmet,” “Duck Helmet,” “Needle Helmet,” and others.

In order for the user to see any armor available in a category, they must first have access to at least one of the parts of the armor in this category. At this point, they can see all the armor in this category, including armor that they still cannot buy.

Problem

I am trying to effectively query the database for all armor categories, while taking into account which items of armor are available to the user. It works for me, but not completely.

Corresponding code

models.py

from django.contrib.auth.models import User
from django.db import models


class Armor(models.Model):
    armor_category = models.ForeignKey('ArmorCategory')
    name = models.CharField(max_length=100)
    profile = models.ManyToManyField('Profile', through='ProfileArmor')

    def __unicode__(self):
        return self.name


class ArmorCategory(models.Model):
    name = models.CharField(max_length=100)
    slug = models.SlugField(blank=True)

    class Meta:
        verbose_name_plural = 'Armor categories'

    def __unicode__(self):
        return self.name


class Profile(models.Model):
    user = models.OneToOneField(User)
    dob = models.DateField('Date of Birth')

    def __unicode__(self):
        return self.user.get_full_name()


class ProfileArmor(models.Model):
    profile = models.ForeignKey(Profile)
    armor = models.ForeignKey(Armor)
    date_created = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ('-date_created',)

    def __unicode__(self):
        return '%s: %s' % (self.profile.user.get_full_name(), self.armor.name)

urls.py

from django.conf.urls import patterns, url
from core import views

urlpatterns = patterns('',
    url(r'^upgrades/(?P<armor_category_slug>[-\w]+)/$', views.Upgrades.as_view(), name='upgrades'),
)

views.py (this file is a problem)

from django.db.models import Count, Q
from django.http import HttpResponseRedirect
from django.shortcuts import get_object_or_404
from django.views.generic.base import TemplateView
from .models import ArmorCategory


class Upgrades(TemplateView):
    template_name = 'core/upgrades.html'

    def get(self, request, *args, **kwargs):
        # Make sure the slug is valid.

        self.armor_category = get_object_or_404(ArmorCategory, slug=kwargs['armor_category_slug'])

        # Make sure the user has been granted access to at least one item in
        # this category, otherwise there is no point for the user to even be
        # here.

        if self.armor_category.armor_set.filter(
            profilearmor__profile=self.request.user.profile
        ).count() == 0:
            return HttpResponseRedirect('/')

        return super(Upgrades, self).get(request, *args, **kwargs)

    def get_context_data(self, **kwargs):
        # THIS IS WHERE THE PROBLEM IS.

        # Get all of the armor in this category, but also take note of which
        # armor this user has been granted access to.

        armor = self.armor_category.armor_set.filter(
            Q(profilearmor__profile=self.request.user.profile) |
            Q(profilearmor__profile=None)
        ).annotate(profile_armor_count=Count('profilearmor__id'))

        print armor.query

        for armor_item in armor:
            print '%s: %s' % (armor_item.name, armor_item.profile_armor_count)

        return {
            'armor_category': self.armor_category,
            'armor': armor,
        }

More about the problem

I created the "Head" category and gave him the three pieces of armor indicated in the first paragraph of this question. I created the armor in the same order as I listed above. Then I created two user profiles.

I granted the first user profile access to the duck helmet armor. Then I got access / upgrade / head / with the first user profile and got this output from the loop for:

Dragon Helm: 0
Duck Helm: 1
Needle Helm: 0

. " " . URL- , :

Dragon Helm: 1
Needle Helm: 0

" "? URL- , , . , :

Duck Helm: 1
Needle Helm: 0

" " .

?

+5
1

1.

" " None Django, :

Q(profilearmor__profile=None)

, " ". ,

self.armor_category.armor_set.filter(
    Q(profilearmor__profile=self.request.user.profile) |
    Q(profilearmor__profile=None))

, self.request.user, . Duck Helm: - ( ) .

2.

, , : SQL:

SELECT `myapp_armor`.`id`, `myapp_armor`.`armor_category_id`, `myapp_armor`.`name`,
       COUNT(`myapp_profilearmor`.`id`) AS `profile_armor_count`
FROM `myapp_armor`
LEFT OUTER JOIN `myapp_profilearmor`
             ON `myapp_armor`.`id` = `myapp_profilearmor`.`armor_id`
                AND `myapp_profilearmor`.`profile_id` = %s
WHERE `myapp_armor`.`armor_category_id` = %s
GROUP BY `myapp_armor`.`id`, `myapp_armor`.`armor_category_id`, `myapp_armor`.`name`

, - Django, , . ORM SQL-. :

sql = '''
    SELECT `myapp_armor`.`id`, `myapp_armor`.`armor_category_id`, `myapp_armor`.`name`,
           COUNT(`myapp_profilearmor`.`id`) AS `profile_armor_count`
    FROM `myapp_armor`
    LEFT OUTER JOIN `myapp_profilearmor`
                 ON `myapp_armor`.`id` = `myapp_profilearmor`.`armor_id`
                    AND `myapp_profilearmor`.`profile_id` = %s
    WHERE `myapp_armor`.`armor_category_id` = %s
    GROUP BY `myapp_armor`.`id`, `myapp_armor`.`armor_category_id`, `myapp_armor`.`name`
'''
armor = Armor.objects.raw(sql, [self.request.user.profile.id, self.armor_category.id])
for armor_item in armor:
    print('{:14}{}'.format(armor_item.name, armor_item.profile_armor_count))

:

>>> helmets = ArmorCategory.objects.get(id=1)
>>> profile = Profile.objects.get(id=1)
>>> armor = Armor.objects.raw(sql, [profile.id, helmets.id])
>>> for armor_item in armor:
...     print('{:14}{}'.format(armor_item.name, armor_item.profile_armor_count))
... 
Dragon Helm   0
Duck Helm     1
Needle Helm   0

3.

Django:

armor = self.armor_category.armor_set.filter(
    Q(profilearmor__profile=self.request.user.profile) |
    Q(profilearmor__profile=None)
).annotate(profile_armor_count=Count('profilearmor__id'))

, , SQL, , queryset query :

>>> from django.db.models import Q
>>> helmets = ArmorCategory.objects.get(name='Helmets')
>>> profile = Profile.objects.get(id=1)
>>> print(helmets.armor_set.filter(Q(profilearmor__profile=profile) |
...                                Q(profilearmor__profile=None)
... ).annotate(profile_armor_count=Count('profilearmor__id')).query)
SELECT `myapp_armor`.`id`, `myapp_armor`.`armor_category_id`, `myapp_armor`.`name`,
       COUNT(`myapp_profilearmor`.`id`) AS `profile_armor_count`
FROM `myapp_armor`
LEFT OUTER JOIN `myapp_profilearmor`
             ON (`myapp_armor`.`id` = `myapp_profilearmor`.`armor_id`) 
LEFT OUTER JOIN `myapp_profile`
             ON (`myapp_profilearmor`.`profile_id` = `myapp_profile`.`id`) 
WHERE (`myapp_armor`.`armor_category_id` = 1
       AND (`myapp_profilearmor`.`profile_id` = 1
            OR `myapp_profile`.`id` IS NULL))
GROUP BY `myapp_armor`.`id`, `myapp_armor`.`armor_category_id`, `myapp_armor`.`name`
ORDER BY NULL

? SQL, 5. .

4. SQL-

, , SQL ( , ). , :

mysql> SELECT * FROM myapp_armorcategory;
+----+---------+---------+
| id | name    | slug    |
+----+---------+---------+
|  1 | Helmets | helmets |
|  2 | Suits   | suits   |
+----+---------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM myapp_armor;
+----+-------------------+-------------+
| id | armor_category_id | name        |
+----+-------------------+-------------+
|  1 |                 1 | Dragon Helm |
|  2 |                 1 | Duck Helm   |
|  3 |                 1 | Needle Helm |
|  4 |                 2 | Spiky Suit  |
|  5 |                 2 | Flower Suit |
|  6 |                 2 | Battle Suit |
+----+-------------------+-------------+
6 rows in set (0.00 sec)

a JOIN 12 2 6 :

mysql> SELECT * FROM myapp_armorcategory JOIN myapp_armor;
+----+---------+---------+----+-------------------+-------------+
| id | name    | slug    | id | armor_category_id | name        |
+----+---------+---------+----+-------------------+-------------+
|  1 | Helmets | helmets |  1 |                 1 | Dragon Helm |
|  2 | Suits   | suits   |  1 |                 1 | Dragon Helm |
|  1 | Helmets | helmets |  2 |                 1 | Duck Helm   |
|  2 | Suits   | suits   |  2 |                 1 | Duck Helm   |
|  1 | Helmets | helmets |  3 |                 1 | Needle Helm |
|  2 | Suits   | suits   |  3 |                 1 | Needle Helm |
|  1 | Helmets | helmets |  4 |                 2 | Spiky Suit  |
|  2 | Suits   | suits   |  4 |                 2 | Spiky Suit  |
|  1 | Helmets | helmets |  5 |                 2 | Flower Suit |
|  2 | Suits   | suits   |  5 |                 2 | Flower Suit |
|  1 | Helmets | helmets |  6 |                 2 | Battle Suit |
|  2 | Suits   | suits   |  6 |                 2 | Battle Suit |
+----+---------+---------+----+-------------------+-------------+
12 rows in set (0.00 sec)

, , . , , , :

mysql> SELECT * FROM myapp_armorcategory JOIN myapp_armor
           ON myapp_armorcategory.id = myapp_armor.armor_category_id;
+----+---------+---------+----+-------------------+-------------+
| id | name    | slug    | id | armor_category_id | name        |
+----+---------+---------+----+-------------------+-------------+
|  1 | Helmets | helmets |  1 |                 1 | Dragon Helm |
|  1 | Helmets | helmets |  2 |                 1 | Duck Helm   |
|  1 | Helmets | helmets |  3 |                 1 | Needle Helm |
|  2 | Suits   | suits   |  4 |                 2 | Spiky Suit  |
|  2 | Suits   | suits   |  5 |                 2 | Flower Suit |
|  2 | Suits   | suits   |  6 |                 2 | Battle Suit |
+----+---------+---------+----+-------------------+-------------+
6 rows in set (0.08 sec)

. , , . :

mysql> INSERT INTO myapp_armorcategory (name, slug) VALUES ('Arm Guards', 'armguards');
Query OK, 1 row affected (0.00 sec)

(SELECT * FROM myapp_armorcategory JOIN myapp_armor ON myapp_armorcategory.id = myapp_armor.armor_category_id;), : , JOIN. , , , : a LEFT OUTER JOIN:

mysql> SELECT * FROM myapp_armorcategory LEFT OUTER JOIN myapp_armor
           ON myapp_armorcategory.id = myapp_armor.armor_category_id;

+----+------------+-----------+------+-------------------+-------------+
| id | name       | slug      | id   | armor_category_id | name        |
+----+------------+-----------+------+-------------------+-------------+
|  1 | Helmets    | helmets   |    1 |                 1 | Dragon Helm |
|  1 | Helmets    | helmets   |    2 |                 1 | Duck Helm   |
|  1 | Helmets    | helmets   |    3 |                 1 | Needle Helm |
|  2 | Suits      | suits     |    4 |                 2 | Spiky Suit  |
|  2 | Suits      | suits     |    5 |                 2 | Flower Suit |
|  2 | Suits      | suits     |    6 |                 2 | Battle Suit |
|  3 | Arm Guards | armguards | NULL |              NULL | NULL        |
+----+------------+-----------+------+-------------------+-------------+
7 rows in set (0.00 sec)

, , NULL .

5.

, :

mysql> INSERT INTO myapp_profile (name) VALUES ('user1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myapp_profilearmor (profile_id, armor_id) VALUES (1, 2);
Query OK, 1 row affected (0.00 sec)

, , :

mysql> SELECT `myapp_armor`.*, `myapp_profilearmor`.*, T5.*
        FROM `myapp_armor`
        LEFT OUTER JOIN `myapp_profilearmor`
                    ON (`myapp_armor`.`id` = `myapp_profilearmor`.`armor_id`)
        LEFT OUTER JOIN `myapp_profile` T5
                    ON (`myapp_profilearmor`.`profile_id` = T5.`id`)
        WHERE `myapp_armor`.`armor_category_id` = 1;

+----+-------------------+-------------+------+------------+----------+------+-------+
| id | armor_category_id | name        | id   | profile_id | armor_id | id   | name  |
+----+-------------------+-------------+------+------------+----------+------+-------+
|  1 |                 1 | Dragon Helm | NULL |       NULL |     NULL | NULL | NULL  |
|  2 |                 1 | Duck Helm   |    1 |          1 |        1 |    1 | user1 |
|  3 |                 1 | Needle Helm | NULL |       NULL |     NULL | NULL | NULL  |
+----+-------------------+-------------+------+------------+----------+------+-------+
3 rows in set (0.04 sec)

, (myapp_profilearmor.profile_id = 1 OR T5.id IS NULL), .

:

mysql> INSERT INTO myapp_profile (name) VALUES ('user2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myapp_profilearmor (profile_id, armor_id) VALUES (2, 1);
Query OK, 1 row affected, 1 warning (0.09 sec)

:

mysql> SELECT `myapp_armor`.*, `myapp_profilearmor`.*, T5.*
        FROM `myapp_armor`
        LEFT OUTER JOIN `myapp_profilearmor`
                    ON (`myapp_armor`.`id` = `myapp_profilearmor`.`armor_id`)
        LEFT OUTER JOIN `myapp_profile` T5
                    ON (`myapp_profilearmor`.`profile_id` = T5.`id`)
        WHERE `myapp_armor`.`armor_category_id` = 1;

+----+-------------------+-------------+------+------------+----------+------+-------+
| id | armor_category_id | name        | id   | profile_id | armor_id | id   | name  |
+----+-------------------+-------------+------+------------+----------+------+-------+
|  1 |                 1 | Dragon Helm |    2 |          2 |        1 |    2 | user2 |
|  2 |                 1 | Duck Helm   |    1 |          1 |        2 |    1 | user1 |
|  3 |                 1 | Needle Helm | NULL |       NULL |     NULL | NULL | NULL  |
+----+-------------------+-------------+------+------------+----------+------+-------+
3 rows in set (0.00 sec)

, (myapp_profilearmor.profile_id = 2 OR T5.id IS NULL), 1 3. 2 .

, , Q(profilearmor__profile=None) T5.id IS NULL, , ( ) ProfileArmor.

6.

  • , , count() :

    if self.armor_category.armor_set.filter(
    profilearmor__profile=self.request.user.profile
).count() == 0:

    , , , exists() .

  • ManyToManyField Armor, ? Armor :

    Q(profilearmor__profile = ...)

    , , :

    Q(profile = ...)

7.

. Django , .

+22

More articles:


All Articles