1

I'm using django-mptt.

I'm trying to get all categories with the count of items inside each category.

But I get this error:

OperationalError at /items/
(1054, "Unknown column 'items_category.lft' in 'where clause'")

From my view.py

    categories = Category.objects.add_related_count(
        Category.objects.all(),  # Queryset
        Item,  # Related mobile
        'category',  # Name of the foreignkey field
        'count',  # Name of the property added to the collection
        cumulative=True)  # Cumulative or not.
        
    print(categories)

model.py

class Category(MPTTModel):
    name = models.CharField(max_length=50, unique=True)
    slug = models.SlugField(max_length=50, null=True, blank=True, )
    parent = TreeForeignKey('self', on_delete=models.CASCADE, null=True, blank=True, related_name='children')

    class MPTTMeta:
        order_insertion_by=['name']

    def save(self, *args, **kwargs):
        self.slug = slugify(self.name)
        super(Category, self).save(*args, **kwargs)
    
    class Meta:
        unique_together = ('slug', 'parent',)    
        verbose_name_plural = "categories"   

class Item(models.Model):
    category = TreeForeignKey(Category, on_delete=models.SET_NULL, blank=True, null=True) 
    name = models.CharField(max_length=250 )

Using print(categories.query) query generated is:

SELECT `items_category`.`id`, `items_category`.`name`, `items_category`.`slug`, `items_category`.`parent_id`, `items_category`.`lft`, `items_category`.`rght`, `items_category`.`tree_id`, `items_category`.`level`, (SELECT count(*) FROM (SELECT U0.`id` FROM `items_item` U0 INNER JOIN `items_category` U1 ON (U0.`category_id` = U1.`id`) WHERE (U1.`lft` >= `items_category`.`lft` AND U1.`lft` <= `items_category`.`rght` AND U1.`tree_id` = `items_category`.`tree_id`)) _count) AS `count` FROM `items_category` ORDER BY `items_category`.`tree_id` ASC, `items_category`.`lft` ASC

I tried to fix query generator trying it directly on my database and seems query is correct but syntax is very wrong.

The correct and working query is that I just removed `.

SELECT items_category.id, items_category.name, items_category.slug, items_category.parent_id, items_category.lft, items_category.rght, items_category.tree_id, items_category.level, 
    (
        SELECT count(*) 
            FROM (
                SELECT U0.id 
                FROM items_item U0 
                INNER JOIN items_category U1 ON ( U0.category_id = U1.id) 
                WHERE (U1.lft >= items_category.lft 
                       AND U1.lft <= items_category.rght 
                       AND U1.tree_id = items_category.tree_id
                )
            ) _count
    ) AS count 
                       FROM items_category 
ORDER BY items_category.tree_id 
    ASC, items_category.lft ASC
David Faizulaev
  • 4,651
  • 21
  • 74
  • 124
oscurodrago
  • 788
  • 1
  • 8
  • 18
  • 1
    Perhaps you haven't run the migration? `python manage.py migrate` – hashlash Mar 12 '22 at 00:01
  • yes i did migrations and still dosen't work – oscurodrago Mar 12 '22 at 08:30
  • Here's some troubleshooting I can think of: make sure you've run `makemigrations` and `migrate`, try to pass the app name if the error persists, try to use a new empty DB. If the error is still there, you could create an example project on GitHub to demonstrate the problem and make it easier for others to examine. – hashlash Mar 12 '22 at 08:48
  • 1
    atm i can't add/share my project ... however i added some info to my question.... the query generated is correct but have some ' give problems to query – oscurodrago Mar 12 '22 at 09:15
  • Hmm, interesting. So the problem lies on the backtick \`. I'm curious, do other queries (ex. `User.objects.all()`) also use backtick? And another thing to check, does the query succeed if you only include `Category`'s direct field (append the query with `.values('name', 'slug', 'parent')`). – hashlash Mar 12 '22 at 10:03
  • all i found something very strange... i'm testing with 2 different databases on 2 different PC on local... add_related_count works on Postgress... same code on phpMyadmin dosen't work.... all other pages of django app and pages works well even where i use category exept where i call add_related_count only on MySql, testing the query get error 'lft' colum dosen't exist but when i check Database it's exist – oscurodrago Mar 12 '22 at 13:43
  • So, the main problem lies between MySQL and `add_related_count`. Have you tried different versions of MySQL? And just to make sure. have you tried SQLite? It's relatively simple than the other database backends since it doesn't require DBMS installation. – hashlash Mar 12 '22 at 14:54
  • i've tried to upgrade mysql on XAMPP but the problem still continue ... seem cannot find colums of where clause.... instead with Sqlite and postgress works all perfectly – oscurodrago Mar 14 '22 at 10:47

0 Answers0