Question 332678 in PostgreSQL Question posted in
The official documentation can be found here.

raw postgresql query in django

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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!


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search