raw postgresql query in django

Question

Please help me with raw postgresql query:

given models.py:

from django.db import models


class Menu(models.Model):
    name = models.CharField(max_length=255)
    menu_url = models.CharField(max_length=255)

    def __str__(self):
        return self.name


class MenuEntry(models.Model):
    menu = models.ForeignKey("menu", null=True, blank=True,
                             on_delete=models.CASCADE)
    parent = models.ForeignKey("menuentry", null=True,
                               blank=True,
                               on_delete=models.CASCADE)
    text = models.CharField(max_length=255)

    def __str__(self):
        return self.text

I am trying to retrieve a menu item with related tree of menuentries, in another file:

    queryset = MenuEntry.objects.raw("WITH RECURSIVE \
my_2ndmenu_items (id, parent, text) \
AS( \
SELECT * FROM menu_menuentry WHERE menu_menuentry.menu_id=(SELECT id FROM menu_menu WHERE name=%s) \
UNION \
SELECT m.* FROM menu_menuentry m \
INNER JOIN my_2ndmenu_items r ON m.parent_id = r.id \
WHERE m.parent_id IS NOT NULL \
) \
SELECT * FROM my_2ndmenu_items", ["menu_B"])

But I'm getting the following error, trying to get queryset[0]:

*** ValueError: Cannot assign "'menu_B_1lvl_entry'": "MenuEntry.parent" must be a "MenuEntry" instance.

Thanks a lot for your help!

Answer 1

Changing this part of your raw query my_2ndmenu_items (id, parent, text) \ to my_2ndmenu_items (id, parent_id, text) \ should fix your problem. But I'd also recommend making sure name on the Menu model is unique or you should change your subquery on Menu to something like this SELECT * FROM menu_menuentry WHERE menu_menuentry.menu_id in (SELECT id FROM menu_menu WHERE name=%s) \ in case you have more than one menu that matches the name you are searching for.

Answer 2

the final answer is:

    queryset = MenuEntry.objects.raw("WITH RECURSIVE \
my_2ndmenu_items \
AS( \
SELECT * FROM menu_menuentry WHERE menu_menuentry.menu_id=(SELECT id FROM menu_menu WHERE name=%s) \
UNION \
SELECT m.* FROM menu_menuentry m \
INNER JOIN my_2ndmenu_items r ON m.parent_id = r.id \
WHERE m.parent_id IS NOT NULL \
) \
SELECT * FROM my_2ndmenu_items", ["menu_B"])

it seems to give what I want thank you everyone for your help!