How to refence current object (F object) within RawSQL

Question

My Order model looks like this:

class Order(models.Model):
    ...
    billing_email = models.EmailField()
    ...

Desired output I am looking for is a ValuesQuerySet that would contain some fields values and a calculated value of the amount of orders with the same billing_email.

I was trying to reference billing_email with F object but doing the following results into error:

In [68]: orders = Order.objects.annotate(
    total_orders=RawSQL(
        "SELECT COUNT(*) FROM appname_order WHERE billing_email = %s",
        (F('billing_email'),)  # current instance's billing_email
    ),
)

In [69]: orders
Out [70]: ProgrammingError: can't adapt type 'F'

Pretty much the value I need to evaluate and output as an extra field for each instance is:

Order.objects.filter(billing_email__iexact=<current_billing_email>).count()

for each instance and I really wouldn't want to loop through actual instances and reconstruct. I must be missing some simple solution here.

Thanks in advance for help.

Answer 1

You can reference the "current" row using the table name of your Model:

RawSQL( "SELECT COUNT(*) FROM appname_order order WHERE order.billing_email =appname_order.billing_email",[])