Queryset Union

[Matthew Pava]

Hi fellow users,

I came across a situation where I needed to annotate a union-all query.  Django’s ORM is so powerful, and it has those features separately, but it cannot seem to handle annotating a union-all query.

I was able to solve my problem by using two views inside PostgreSQL.  I created an unmanaged model and then learned about the fact that the ORM cannot seem to handle a natural key, so I had to go back and use “row_number()” to set as an alias for id on the database view to make the lone primary key, even though I will never, ever, ever use it, and it seems like a waste of computational power.

 

I was wondering if anyone else was in this type of situation, and if they resolved it differently than me.  With the unmanaged model, I have to write RunSQL commands in the migrations, but I used Django’s ORM to actually generate the SQL and copy-pasted it with some minor changes.  I guess my issue ended up being that I needed a separate query for the union-all, and then writing a query that referenced the union-all query.