How can such a query be described using Django ORM syntax?

Question

PostgreSQL

Good day.
Can you please tell me how to describe a query to the following type of database using Django ORM syntax?

SELECT *, (
    SELECT cp.value
    FROM calculated_price cp 
    WHERE cp.currency = 'USD' AND cp.item_id = it.id
    ) as converted
FROM items it
ORDER BY converted;

Thanks in advance.

Answer 1

The solution was suggested by Vadim Shatalov . Link: https://docs.djangoproject.com/en/dev/ref/models/e...
In my case, this is what happened:

class Item(models.Model):
    name = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=16, decimal_places=2)
    
class ConvertedPrice(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    currency = models.CharField(max_length=3)
    value = models.DecimalField(max_digits=16, decimal_places=2)

ordered_items = Item.objects.annotate(converted=Subquery(
    ConvertedPrice.objects.filter(item=OuterRef('id'), currency='USD').values('value')
)).order_by('converted')

And the output is this SQL:

SELECT 
    "app_item"."id", 
    "app_item"."name", 
    "app_item"."price", 
    (
        SELECT U0."value" 
        FROM "app_convertedprice" U0 
        WHERE (U0."currency" = USD AND U0."item_id" = ("app_item"."id"))
    ) AS "converted" 
FROM "app_item" 
ORDER BY "converted" ASC;

Answer 2

Item.objects.filter(
     id__in=ConvertedPrice.objects.filter(currency='USD').values_list('item_id', flat=True)
).prefetch_related('convertedprice_set')