Django raw query - using dot notation to traverse related model fields
This is my request raw
in Django
q = Book.objects.raw('''
SELECT * FROM
( SELECT "book"."name", "author"."name",
RANK() OVER (PARTITION BY "author"."id") AS "rank"
FROM "book"
INNER JOIN "book" ON ("book"."author_id" = "author"."id")
) AS "book_table"
WHERE "rank" < %s ''', 10)
In the above query, the field is name
ambiguous. I am passing this object to another library which requires using dot notation i.e. q[0].name
you should refer to the name of the book, and q[0].author.name
- to the name of the author. Is it possible to use raw query dot notation (the latter "author"."name" AS "author_name"
does use , but this would lead to redundant code because these functions are also injected from Django's managed queries that support dot notation).
source to share
It might be better to use as much Django syntax as possible, and only manually add the rank field. You can do this with extra
, not raw
, and use select_related
to cross the link. Something like that:
Book.objects.select_related('author').extra(
select={'rank': 'RANK() OVER (PARTITION BY "author"."id")'},
where=['"rank" < 10']
)
source to share
I think you are confused about what it raw
means in context models.manager
.
You can use FooModel.objects.raw
to create a set of queries (actually a RawQuerySet) for FooModel; the result of the request passed to raw
will be converted to FooModels; so you should only return fields related to FooModel. See here in the docs for an example. Not sure what additional fields (like author.name in your example) will do, but my guess is that this will confuse Django and won't do what you intend to do.
If you want to run completely arbitrary commands that return arbitrary data, you just have to go to db talk like here .
In your case, however, doing
q = Book.objects.raw('''
SELECT "book"."id" FROM
( SELECT "book"."id"
RANK() OVER (PARTITION BY "author"."id") AS "rank"
FROM "book"
INNER JOIN "book" ON ("book"."author_id" = "author"."id")
) AS "book_table"
WHERE "rank" < %s ''', 10)
).prefetch_related("author")
should do what you want. It will make another request to fetch authors than your original raw request, but that is probably acceptable in exchange for not having to go to database queries with unprotected metal.
source to share