How to display two QuerySets in one table?

Question

I have two different QuerySets which both return a list of Users (with different fields). How can I display them both in one HTML table? There will be some overlap between which users are returned, and for the missing data I just want to fill it in with 0s (which I can probably do in the template with the default filter).

If it helps, the queries look like this:

new_users = User.objects.filter(date_joined__gt=sd, date_joined__lte=ed)
new_referrals = User.objects.filter(referrals__user__in=new_users).annotate(referral_count=Count('referrals')).select_related('profile')

accepted_bids = Bid.objects.filter(created__gt=sd, created__lte=ed, status='acc')
completed_shipments = Shipment.objects.filter(bids__in=accepted_bids)
vehicles_shipped = User.objects.filter(referrals__user__shipments__in=completed_shipments).annotate(vehicles_shipped=Count('referrals__user__shipments__items')).select_related('profile')

I need to combine new_referrals and vehicles_shipped so that I can iterate over it in my template (merged on something like user.id), or something like that... maybe I can use python's itertools somehow?

---

Here's the generated SQL for the vehicles_shipped query:

SELECT "auth_user"."id", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", "auth_user"."date_joined", COUNT("shipments_vehicleitem"."id") AS "vehicles_shipped", T7."id", T7."user_id", T7."company_name", T7."phone", T7."address_id", T7."referred_by_id", T7."user_type_id", T7."object_id", T7."credits" FROM "auth_user" LEFT OUTER JOIN "users_profile" ON  ("auth_user"."id" = "users_profile"."referred_by_id") LEFT OUTER JOIN "auth_user" T3 ON ("users_profile"."user_id" = T3."id") LEFT OUTER JOIN "shipments_shipment" ON (T3."id" = "shipments_shipment"."user_id") INNER JOIN "shipments_bid" ON ("shipments_shipment"."id" = "shipments_bid"."shipment_id") LEFT OUTER JOIN "shipments_vehicleitem" ON ("shipments_shipment"."id" = "shipments_vehicleitem"."shipment_id") LEFT OUTER JOIN  "users_profile" T7 ON ("auth_user"."id" = T7."user_id") WHERE ("shipments_bid"."created" <= E'2010-05-27 18:22:41.954766' AND "shipments_bid"."status" = E'acc' AND "shipments_bid"."created" > E'0001-01-01 00:00:00' ) GROUP  BY "auth_user"."id", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", "auth_user"."date_joined", T7."id", T7."user_id", T7."company_name", T7."phone", T7."address_id", T7."referred_by_id", T7."user_type_id", T7."object_id", T7."credits"

Answer 1

You could use a JOIN

http://dev.mysql.com/doc/refman/5.1/en/join.html

Answer 2

Might this help you? Pulls together two querysets and lets you treat them as one.

Alternatively, whack them both with list() in the view: overall_users = list(new_referrals) + list(vehicles_shipped)

Answer 3

For most applications you really don't need anything more than default django queryset combinations. This merges on the primary key.

combined = new_referrals | vehicles_shipped

I'm actually surprised this can't be found in django documentation, this behaviour is quite old. You should bring up a ticket for that.

btw: & can be used to get the intersect.

Answer 4

At some point, the result sets of the two queries needs to be iterated over and User objects created. You can punt that to the template, or you can do it in a view, building up a set of User objects (to eliminate duplicates). Then either pass the set to the template, or turn it into a list and sort it first.

Sometimes the answer is to do more work in your view. I'm not sure this case is one of them (you should be able to do this with Q queries), but it's an option worth remembering.