0

I have the below two querysets books1 and books2. On both i am using annotate to add another column with a constant of 10 for books1 and 30 for books2. The problem is after i Concatenate the querysets the final set shows the annotate value = 10 even for books2

author1 = Author.objects.get(pk=1)

books1 = author1.books_set.annotate(sample_var=Value(10))

author2 = Author.objects.get(pk=2)

books2 = author2.books_set.annotate(sample_var=Value(30))

combine = books1 | books2

what i see:

name                  author id       sample_var
name1 (from books1)       1                10
..                        1                10
..                        1                10
..                        1                10
name2 (from books2)       2                10 (instead of 30) 
..                        2                10 (instead of 30) 
..                        2                10 (instead of 30) 
..                        2                10 (instead of 30)

the reason is, the sql being used for the combine is as below. I get this when i try to access combine[0].name. It takes care of the WHERE part, but not the annotate part. It uses 10 AS "sample_var" for the combine queryset, which is not right, thats why all show 10 in the above table.

SELECT "books_books"."name", "books_books"."author_id", 10 AS "sample_var"
FROM "books_books" 
WHERE ("books_books"."author_id" = 1 OR "books_books"."author_id" = 2)

I think if the union to happen properly the sql should be:

SELECT "books_books"."name", "books_books"."author_id", 10 AS "sample_var"
FROM "books_books" 
WHERE ("books_books"."author_id" = 1)
union all
SELECT "books_books"."name", "books_books"."author_id", 30 AS "sample_var"
FROM "books_books" 
WHERE ("books_books"."author_id" = 2)
Santhosh
  • 9,965
  • 20
  • 103
  • 243

1 Answers1

0

In Django 1.11 you can do union, and in your case:

all_books = books1.union(books2, all=True)
A K
  • 788
  • 6
  • 17