Search code examples
djangodjango-modelsinner-join

Django ORM inner join with table sharing the same foreign key

I have three tables:

Table 1 : order
Fields: id, client_reference, price and status
(status is a foreignkey linked to order_status.id)

Table 2 : order_status 
Fields: id, lastupdate

Table 3 : order_status_i18n
Fields: id, order_status_id, language and label
(order_status_id is also a foreignkey linked to order_status.id)

what I'd like to do is to get the order_status_i18n label instead of order_status_id based on the user language, so the SQL request would be like:

SELECT o.client_reference as reference, o_s_i18n.label 
FROM 
order AS o 
INNER JOIN order_status_i18n AS o_s_i18n 
ON o.status=o_s_i18n.order_status_id WHERE o_s_i18n.language='the language';

and the Model for order contains a foreignkey attribute linked to the order_status model but not order_status_i18n, so I tried to use

Order.objects.filter(some_filters).prefetch_related('status')
.filter(status__in=OrderStatusI18N.objects.filter(language='the_language'))

Which gives me a queryset conflict telling me that I should use the queryset for OrderStatus rather than OrderStatusI18N and I totally agree to this.

But anyway, what would be the good way to manage such a request using django ORM?

Solution

  • I think this should do the thing. 

    Order.objects.filter(some_filters).prefetch_related('status').annotate(i18_label=F('status__order_status_i18n__language')).filter(status__order_status_i18n__language='en')