[Fixed]-Join annotations in Django without raw SQL

Approach 1

Using Djagno 1.8+ Conditional Expressions
(see also Query Expressions)

items = Item.objects.all().annotate(
    first=models.Case(models.When(attribute__key='first', then=models.F('attribute__value')), default=models.Value('')),
    second=models.Case(models.When(attribute__key='second', then=models.F('attribute__value')), default=models.Value(''))
).distinct()

for item in items:
    print item.first, item.second

Approach 2

Using prefetch_related with custom models.Prefetch object

keys = ['first', 'second']
items = Item.objects.all().prefetch_related(
    models.Prefetch('attributes',
        queryset=Attribute.objects.filter(key__in=keys),
        to_attr='prefetched_attrs'),
)

This way every item from the queryset will contain a list under the .prefetched_attrs attribute.

This list will contains all filtered-item-related attributes.

Now, because you want to get the attribute.value, you can implement something like this:

class Item(models.Model):
    #...
    def get_attribute(self, key, default=None):
        try:
            return next((attr.value for attr in self.prefetched_attrs if attr.key == key), default)
        except AttributeError:
            raise AttributeError('You didnt prefetch any attributes')

#and the usage will be:
for item in items:
    print item.get_attribute('first'), item.get_attribute('second')

Some notes about the differences in using both approaches.

• you have a one idea better control over the filtering process using the approach with the custom Prefetch object. The conditional-expressions approach is one idea harder to be optimized IMHO.
• with prefetch_related you get the whole attribute object, not just the value you are interested in.
• Django executes prefetch_related after the queryset is being evaluated, which means a second query is being executed for each clause in the prefetch_related call. On one way this can be good, because it this keeps the main queryset untouched from the filters and thus not additional clauses like .distinct() are needed.
• prefetch_related always put the returned objects into a list, its not very convenient to use when you have prefetchs returning 1 element per object. So additional model methods are required in order to use with pleasure.