Patrick Williams | c124f4f | 2015-09-15 14:41:29 -0500 | [diff] [blame] | 1 | Django Aggregate If: Condition aggregates for Django |
| 2 | ==================================================== |
| 3 | |
| 4 | .. image:: https://travis-ci.org/henriquebastos/django-aggregate-if.png?branch=master |
| 5 | :target: https://travis-ci.org/henriquebastos/django-aggregate-if |
| 6 | :alt: Test Status |
| 7 | |
| 8 | .. image:: https://landscape.io/github/henriquebastos/django-aggregate-if/master/landscape.png |
| 9 | :target: https://landscape.io/github/henriquebastos/django-aggregate-if/master |
| 10 | :alt: Code Helth |
| 11 | |
| 12 | .. image:: https://pypip.in/v/django-aggregate-if/badge.png |
| 13 | :target: https://crate.io/packages/django-aggregate-if/ |
| 14 | :alt: Latest PyPI version |
| 15 | |
| 16 | .. image:: https://pypip.in/d/django-aggregate-if/badge.png |
| 17 | :target: https://crate.io/packages/django-aggregate-if/ |
| 18 | :alt: Number of PyPI downloads |
| 19 | |
| 20 | *Aggregate-if* adds conditional aggregates to Django. |
| 21 | |
| 22 | Conditional aggregates can help you reduce the ammount of queries to obtain |
| 23 | aggregated information, like statistics for example. |
| 24 | |
| 25 | Imagine you have a model ``Offer`` like this one: |
| 26 | |
| 27 | .. code-block:: python |
| 28 | |
| 29 | class Offer(models.Model): |
| 30 | sponsor = models.ForeignKey(User) |
| 31 | price = models.DecimalField(max_digits=9, decimal_places=2) |
| 32 | status = models.CharField(max_length=30) |
| 33 | expire_at = models.DateField(null=True, blank=True) |
| 34 | created_at = models.DateTimeField(auto_now_add=True) |
| 35 | updated_at = models.DateTimeField(auto_now=True) |
| 36 | |
| 37 | OPEN = "OPEN" |
| 38 | REVOKED = "REVOKED" |
| 39 | PAID = "PAID" |
| 40 | |
| 41 | Let's say you want to know: |
| 42 | |
| 43 | #. How many offers exists in total; |
| 44 | #. How many of them are OPEN, REVOKED or PAID; |
| 45 | #. How much money was offered in total; |
| 46 | #. How much money is in OPEN, REVOKED and PAID offers; |
| 47 | |
| 48 | To get these informations, you could query: |
| 49 | |
| 50 | .. code-block:: python |
| 51 | |
| 52 | from django.db.models import Count, Sum |
| 53 | |
| 54 | Offer.objects.count() |
| 55 | Offer.objects.filter(status=Offer.OPEN).aggregate(Count('pk')) |
| 56 | Offer.objects.filter(status=Offer.REVOKED).aggregate(Count('pk')) |
| 57 | Offer.objects.filter(status=Offer.PAID).aggregate(Count('pk')) |
| 58 | Offer.objects.aggregate(Sum('price')) |
| 59 | Offer.objects.filter(status=Offer.OPEN).aggregate(Sum('price')) |
| 60 | Offer.objects.filter(status=Offer.REVOKED).aggregate(Sum('price')) |
| 61 | Offer.objects.filter(status=Offer.PAID).aggregate(Sum('price')) |
| 62 | |
| 63 | In this case, **8 queries** were needed to retrieve the desired information. |
| 64 | |
| 65 | With conditional aggregates you can get it all with only **1 query**: |
| 66 | |
| 67 | .. code-block:: python |
| 68 | |
| 69 | from django.db.models import Q |
| 70 | from aggregate_if import Count, Sum |
| 71 | |
| 72 | Offer.objects.aggregate( |
| 73 | pk__count=Count('pk'), |
| 74 | pk__open__count=Count('pk', only=Q(status=Offer.OPEN)), |
| 75 | pk__revoked__count=Count('pk', only=Q(status=Offer.REVOKED)), |
| 76 | pk__paid__count=Count('pk', only=Q(status=Offer.PAID)), |
| 77 | pk__sum=Sum('price'), |
| 78 | pk__open__sum=Sum('price', only=Q(status=Offer.OPEN)), |
| 79 | pk__revoked__sum=Sum('price'), only=Q(status=Offer.REVOKED)), |
| 80 | pk__paid__sum=Sum('price'), only=Q(status=Offer.PAID)) |
| 81 | ) |
| 82 | |
| 83 | Installation |
| 84 | ------------ |
| 85 | |
| 86 | *Aggregate-if* works with Django 1.4, 1.5, 1.6 and 1.7. |
| 87 | |
| 88 | To install it, simply: |
| 89 | |
| 90 | .. code-block:: bash |
| 91 | |
| 92 | $ pip install django-aggregate-if |
| 93 | |
| 94 | Inspiration |
| 95 | ----------- |
| 96 | |
| 97 | There is a 5 years old `ticket 11305`_ that will (*hopefully*) implement this feature into |
| 98 | Django 1.8. |
| 99 | |
| 100 | Using Django 1.6, I still wanted to avoid creating custom queries for very simple |
| 101 | conditional aggregations. So I've cherry picked those ideas and others from the |
| 102 | internet and built this library. |
| 103 | |
| 104 | This library uses the same API and tests proposed on `ticket 11305`_, so when the |
| 105 | new feature is available you can easily replace ``django-aggregate-if``. |
| 106 | |
| 107 | Limitations |
| 108 | ----------- |
| 109 | |
| 110 | Conditions involving joins with aliases are not supported yet. If you want to |
| 111 | help adding this feature, you're welcome to check the `first issue`_. |
| 112 | |
| 113 | Contributors |
| 114 | ------------ |
| 115 | |
| 116 | * `Henrique Bastos <http://github.com/henriquebastos>`_ |
| 117 | * `Iuri de Silvio <https://github.com/iurisilvio>`_ |
| 118 | * `Hampus Stjernhav <https://github.com/champ>`_ |
| 119 | * `Bradley Martsberger <https://github.com/martsberger>`_ |
| 120 | * `Markus Bertheau <https://github.com/mbertheau>`_ |
| 121 | * `end0 <https://github.com/end0>`_ |
| 122 | * `Scott Sexton <https://github.com/scottsexton>`_ |
| 123 | * `Mauler <https://github.com/mauler>`_ |
| 124 | * `trbs <https://github.com/trbs>`_ |
| 125 | |
| 126 | Changelog |
| 127 | --------- |
| 128 | |
| 129 | 0.5 |
| 130 | - Support for Django 1.7 |
| 131 | |
| 132 | 0.4 |
| 133 | - Use tox to run tests. |
| 134 | - Add support for Django 1.6. |
| 135 | - Add support for Python3. |
| 136 | - The ``only`` parameter now freely supports joins independent of the main query. |
| 137 | - Adds support for alias relabeling permitting excludes and updates with aggregates filtered on remote foreign key relations. |
| 138 | |
| 139 | 0.3.1 |
| 140 | - Fix quotation escaping. |
| 141 | - Fix boolean casts on Postgres. |
| 142 | |
| 143 | 0.2 |
| 144 | - Fix postgres issue with LIKE conditions. |
| 145 | |
| 146 | 0.1 |
| 147 | - Initial release. |
| 148 | |
| 149 | |
| 150 | License |
| 151 | ======= |
| 152 | |
| 153 | The MIT License. |
| 154 | |
| 155 | .. _ticket 11305: https://code.djangoproject.com/ticket/11305 |
| 156 | .. _first issue: https://github.com/henriquebastos/django-aggregate-if/issues/1 |