[Answered ]-How can i do such query in multiple tables at the same time in django

by

2👍

✅

B_Photo, B_Activity and B_Part don’t have the same table structure, right? I think you cannot make a query with three different table. UNION can do this but it requires all the sub-queries return data with the same structure.

It seems that you want to display a timeline mixed with photos, activities and parts. The most reasonable way is to sort the results in python.

bphoto = B_Photo.objects.filter(q_user).order_by('-pub_date')
bactivity = B_Activity.objects.filter(q_user).order_by('-pub_date')
bpart = B_Part.objects.filter(q_user).order_by('-pub_date')

timeline = sorted(bphoto + bactivity + bpart, key=lambda x:x.pub_date)

UPDATE:

I see what you mean. If you have too much data in these 3 tables and you only want to show the most recent, say, 20 records, you can run a raw UNION sql on the 3 tables like this:

cursor = connection.cursor()
cursor.execute("SELECT id, type FROM (
    SELECT id, 'photo' AS type, pub_date FROM b_photo UNION 
    SELECT id, 'activity' AS type, pub_date FROM b_activity UNION 
    SELECT id, 'part' AS type, pub_date FROM b_part) AS my_query
    ORDER BY pub_date DESC LIMIT 20")
results = cursor.fetchall()
# results will be something like ((id, type), (id, type), ...)
# where type indicates what the id is (photo, activity or part)

Then use individual B_XXX.objects.get(id=id) to get each object in ids.

for result in results:
    if result[1] == 'photo':
        obj = B_Photo.objects.get(id=result[0])
    elif result[1] == 'activity':
        obj = B_Activity.objects.get(id=result[0])
    elif result[1] == 'part':
        obj = B_Part.objects.get(id=result[0])
    # do sth with obj...
👤charlee

Leave a comment