```
sub_query = PaymentDetails.objects.filter(order=OuterRef('pk')).order_by('-updated_at')[:1]
payment_summary = """select jsonb_build_object(
'total_paid_amount', coalesce(sum(amount), 0),
'total_due_amount', CASE WHEN (order_order.total_gross - coalesce(sum(amount), 0)) > 0.02 then (order_order.total_gross - coalesce(sum(amount), 0)) else 0 end,
'is_full_paid', coalesce(sum(amount), 0) > 0 and (order_order.total_gross - coalesce(sum(amount), 0)) <= 0.02 and order_order.is_payment_completed,
'total_customer_payable_amount', CASE WHEN (coalesce(sum(amount), 0) - order_order.total_gross) > 0.02 then coalesce(sum(amount), 0) - order_order.total_gross else 0 end
)
from payment_paymentdetails
where payment_paymentdetails.order_id = order_order.id
and payment_paymentdetails.status = %s"""
orders = Order.objects.prefetch_related(
'lines',
'lines__qc',
'lines__variant__product__vendor_user',
'lines__variant__product',
'lines__variant',
'lines__variant__product__vendor_user__vendor_details',
'lines__tickets', 'lines__lines', 'lines__lines__pickup_req'
).select_related('shipping_address').annotate(
payment_updated_at=Subquery(sub_query.values('transaction_date_time'))) \
.annotate(payment_summary=RawSQL(payment_summary, (PaymentStatus.CONFIRMED,))) \
.annotate(payment_method=Subquery(sub_query.values('method'))).order_by('-payment_updated_at').distinct()
if 'status' in request.GET or 'id' in request.GET:
status_list = request.GET.getlist('status')
order_list = request.GET.getlist('id')
if len(status_list) > 0 or len(order_list) > 0:
orders = orders.filter(
Q(status__in=status_list)
| Q(id__in=order_list),
Q(payment_details__status__in=[PaymentStatus.PAY_LATER, PaymentStatus.CONFIRMED])
)
else:
orders = orders.filter(
Q(status=OrderStatus.UNFULFILLED),
Q(payment_details__status__in=[PaymentStatus.PAY_LATER, PaymentStatus.CONFIRMED])
)
payment_summary = """select jsonb_build_object(
'total_paid_amount', coalesce(sum(amount), 0),
'total_due_amount', CASE WHEN (order_order.total_gross - coalesce(sum(amount), 0)) > 0.02 then (order_order.total_gross - coalesce(sum(amount), 0)) else 0 end,
'is_full_paid', coalesce(sum(amount), 0) > 0 and (order_order.total_gross - coalesce(sum(amount), 0)) <= 0.02 and order_order.is_payment_completed,
'total_customer_payable_amount', CASE WHEN (coalesce(sum(amount), 0) - order_order.total_gross) > 0.02 then coalesce(sum(amount), 0) - order_order.total_gross else 0 end
)
from payment_paymentdetails
where payment_paymentdetails.order_id = order_order.id
and payment_paymentdetails.status = %s"""
orders = Order.objects.prefetch_related(
'lines',
'lines__qc',
'lines__variant__product__vendor_user',
'lines__variant__product',
'lines__variant',
'lines__variant__product__vendor_user__vendor_details',
'lines__tickets', 'lines__lines', 'lines__lines__pickup_req'
).select_related('shipping_address').annotate(
payment_updated_at=Subquery(sub_query.values('transaction_date_time'))) \
.annotate(payment_summary=RawSQL(payment_summary, (PaymentStatus.CONFIRMED,))) \
.annotate(payment_method=Subquery(sub_query.values('method'))).order_by('-payment_updated_at').distinct()
if 'status' in request.GET or 'id' in request.GET:
status_list = request.GET.getlist('status')
order_list = request.GET.getlist('id')
if len(status_list) > 0 or len(order_list) > 0:
orders = orders.filter(
Q(status__in=status_list)
| Q(id__in=order_list),
Q(payment_details__status__in=[PaymentStatus.PAY_LATER, PaymentStatus.CONFIRMED])
)
else:
orders = orders.filter(
Q(status=OrderStatus.UNFULFILLED),
Q(payment_details__status__in=[PaymentStatus.PAY_LATER, PaymentStatus.CONFIRMED])
)
```
Now add I raw query with Order model like this
```
AND
(lower("account_user"."email") similar to '%(sara|aa)%' OR lower("account_vendordetails"."company_name") similar to '%(sara|aa)%' OR
lower(order_orderline.data->>'host') similar to '%(sara|aa)%'
))
(lower("account_user"."email") similar to '%(sara|aa)%' OR lower("account_vendordetails"."company_name") similar to '%(sara|aa)%' OR
lower(order_orderline.data->>'host') similar to '%(sara|aa)%'
))
```
when I filter with vendor name, then added this query like this
```
if vendor_name:
vendor_name = "".join(vendor_name).replace(', ', ',').replace(',', '|')
print("vendor name============>", vendor_name)
vendor_sub_query = f""" and (lower("account_user"."email") similar to '%({vendor_name})%' OR lower("account_vendordetails"."company_name") similar to '%({vendor_name})%' OR
lower(order_orderline.data->>'host') similar to '%({vendor_name})%'
)"""
orders = orders.raw(vendor_sub_query)
vendor_name = "".join(vendor_name).replace(', ', ',').replace(',', '|')
print("vendor name============>", vendor_name)
vendor_sub_query = f""" and (lower("account_user"."email") similar to '%({vendor_name})%' OR lower("account_vendordetails"."company_name") similar to '%({vendor_name})%' OR
lower(order_orderline.data->>'host') similar to '%({vendor_name})%'
)"""
orders = orders.raw(vendor_sub_query)
```
but I found this error "'RawQuerySet' object has no attribute 'qs'
"
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/556111a0-117d-499c-b548-01e0459e0ffdn%40googlegroups.com.
