Django: Creating a non-destinct union of two querysets

Question

I am writing an accouting app in django and there are Orders, which have a date when the invoice was created and an optional date when a credit note is created.

class Order(models.Model):
    date_invoice_created = models.DateTimeField(null=True, blank=True)
    date_credit_note_created = models.DateTimeField(null=True, blank=True)

I'm currently developing the view for our accountant, and she'd like to have both the invoice and the credit note on separate rows in the admin panel, sorted by theirs respective creation dates.

So basically I'd like to show the same model twice, in different row, sorted by different fields. In SQL, this would be something like:

SELECT id, create_date FROM (
    SELECT id, date_invoice_created AS create_date, 'invoice' AS type FROM order
        UNION
    SELECT id, date_credit_note_created AS create_date, 'creditnote' AS type FROM order
) ORDER BY create_date

Don't mind my SQL-fu not being up-to-date, but I guess you understand what I mean.

So I've tried to get django to do this for me, by overriding the date in the second queryset, because django does not support the union of two extra'd querysets:

invoices = Order.objects.filter(date_invoice_created__isnull=False)
credit_notes = Order.filter_valid_orders(qs
    ).filter(
        date_credit_note_created__isnull=False
    ).extra(
        select={'date_invoice_created': 'date_credit_note_created'}
    )
return (invoices | credit_notes).order_by('date_invoice_created')

unfortunately, the bit-wise-or operation for union always makes sure that the IDs are distinct, but I really want them not to be. How can I achieve to have a union with duplicate rows?

It looks to me like you are engaging in some premature optimization. If those are two distinct lists, shown independent of each other, why do you try so hard to get them in a single query? — Ludwik Trammer, Sep 17 '14 at 12:09
Because I want to keep all the functionality that the `contrib.admin` interface provides, which uses querysets to do its magic. — devsnd, Sep 17 '14 at 12:12
this may be what u want: http://stackoverflow.com/questions/431628/how-to-combine-2-or-more-querysets-in-a-django-view — Fabricator, Sep 17 '14 at 19:12
Thanks, but `itertools.chain` is not compatible with the queryset interface and therefore does not work in my case. I need a queryset since I want to override the `queryset` method of `django.contrib.admin.ModelAdmin` ... — devsnd, Sep 18 '14 at 09:42

score 0 · Answer 1 · answered Sep 18 '14 at 12:09

I have now found the solution to my problem using a SQL-View.

I've created a new migration (using south), which contains the above SQL query mentioned in the question as a view, which returns all rows twice, each with a create_date and type respectively for the credit note and the invoice.

accounting/migrations/00xx_create_invoice_creditnote_view.py:

class Migration(SchemaMigration):

    def forwards(self, orm):
        query = """
          CREATE VIEW invoiceoverview_invoicecreditnoteunion AS
            SELECT * FROM (
                SELECT  *, 
                        date_invoice_created AS create_date,
                        'invoice' AS type 
                    FROM accounting_order
                    WHERE date_invoice_created NOT NULL
                UNION
                SELECT  *,
                        date_credit_note_created AS date,
                        'creditnote' AS type
                    FROM accounting_order
                    WHERE date_credit_note_created NOT NULL
            );
        """
        db.execute(query)


    def backwards(self, orm):
        query = """
          DROP VIEW invoiceoverview_invoicecreditnoteunion;
        """
        db.execute(query)

    # ...
    # the rest of the migration model
    # ...

Then I've created a new model for this view, which has the Meta managed = False so that django uses the model without caring about it's creation. It has all the same fields as the original Order model, but also includes the two new fields from the SQL-View:

invoiceoverview/models.py:

class InvoiceCreditNoteUnion(models.Model):
    """ This class is a SQL-view to Order, so that the credit note and
    invoice can be displayed independently.

    """
    class Meta:
        managed = False  # do not manage the model in the DB
    # fields of the view
    date = models.DateTimeField() 
    type = models.CharField(max_length=255)

    # ...
    # all the other fields of the original Order
    # ...

Now I can use this model for the contrib.admin.ModelAdmin and display the appripriate content by checking the type field. e.g.:

class InvoiceAdmin(admin.ModelAdmin):
    list_display = ['some_special_case']

    def some_special_case(self, obj):
        if obj.type == 'creditnote':
            return obj.credit_note_specific field
        else:
            return obj.invoice_specific_field

admin.site.register(InvoiceCreditNoteUnion, InvoiceAdmin)

This finally allows me to use all the other features provided by the admin-panel, e.g. overriding the queryset method, sorting etc.

Django: Creating a non-destinct union of two querysets

1 Answers1