[FIXED] How to get a field's value by joining multiple querysets in Django

Issue

I am trying to display images in a list based on certain conditions.

To that end, I have been trying to create a queryset join of a set of models (as shown below):

models.py

class ModeOfTransport(models.Model):
    mode_of_transport = models.CharField(unique=True, max_length=4...


class Route(models.Model):
    route_id = models.AutoField(primary_key=True)
    route_start = models.ForeignKey(City, related_name='route_start')
    route_end = models.ForeignKey(City, related_name='route_end')


class RouteLeg(models.Model):
    route_leg_id = models.AutoField(primary_key=True)
    route_id = models.ForeignKey(Route, related_name='route_hdr')
    leg_start = models.ForeignKey(City, related_name='leg_start')
    leg_end = models.ForeignKey(City, related_name='leg_start')
    mode_of_tptn = models.ForeignKey(ModeOfTransport, related_name='route_tptn_mode')


class ImageType(models.Model):
    image_type_id = models.CharField(primary_key=True, max_length=1)
    image_type = models.CharField(max_length=4)


class RouteImage(models.Model):
    image_id = models.AutoField(primary_key=True)
    image_name = models.CharField(max_length=125)
    image_type = models.ForeignKey(ImageType)
    image_route = models.ImageField(upload_to='images/route_images/')

From a list of existing routes (a generic listview), I am trying to create a further drilldown like this:

views.py

def graf_display_route(request, pk):
    if request.method == 'GET':
        route_grafiks = RouteLeg.objects.all().filter(route_id=pk)
        return render(request, 'route_grafiks.html', {'route_grafiks': route_grafiks})

Now in order to get to the image field (RouteImage.image_route), I tried the following to get the image_type and then the image itself:

qs_mode_of_tptn = ImageType.objects.filter(image_type_id__in= route_graphics.values_list('mode_of_tptn', flat=True))

The result (on printing the list of resultant queryset) I can see in the terminal as:

[<ImageType: ImageType object (2)>, <ImageType: ImageType object (3)>]>

Next, using the queryset qs_mode_of_tptn when I use another qset, I get the image table fields (model "RouteImage"):

qs_route_image = RouteImage.objects.filter(image_id__in= qs_mode_of_tptn.values_list('image_type_id', flat=True))

And this is what I get in the terminal:

[<RouteImage: RouteImage object (2)>, <RouteImage: RouteImage object (3)>]>

The purpose of my doing this is to figure out how to get the image from the model RouteImage based on the queryset result.

To further clarify, I have done the mapping for images in the following manner:

Field	Road	Rail	Air
MoT (Mode of Transportation)	1	2	3
Image Type	1	2	3

My goal is to seek the image like this:

Get the "MoT (Model - RouteLeg)" -> Get "Image Type (Model - ImageType)" -> Get the "Image (Model - RouteImage)"

So, if the MoT is Road (1), get the ImageType (with a value "1") and then get the RouteImage (for image_type == 1).

Can I really get the images based on the above scenario? If not, can you please suggest a way to do just that.

Edit

As you advised, I have made changes to the qss as under:

a. qs_mode_of_tptn = ImageType.objects.filter(image_type__in=route_graphics.values_list('mode_of_tptn__mode_of_transport', flat=True))

b. collect_images = RouteImage.objects.filter(image_type__in=qs_mode_of_tptn.values_list('image_type_id', flat=True))

And using the first two qss (which are returning the text values of the key fields - ROAD, RAIL etc), qs collect_images is returning the fields (including the image fields) now.

Solution

You can build a dict with the image_type as key so that you can map the images to a certain mode of transportation. Note that if you use select_related() for mode_of_tptn Django will already join the corresponding table so there will be no addional lookups. To get the images by type you can use a query that spans over the relationship.

# your view
def graf_display_route(request, pk):
    if request.method == 'GET':
        route_grafiks_qs = RouteLeg.objects.all().filter(route_id=pk).select_related("mode_of_tptn")
        
        # build a list of the modes of transport without additional query
        modes_of_tpn = [rg.mode_of_tptn.mode_of_transport for rg in route_grafiks_qs]

        # filter images for image type
        image_qs = RouteImage.objects.filter(image_type__image_type__in=modes_of_tpn)
        # build a dict of images with type as key
        images = {i.image_type.image_type: i for i in image_qs}

        route_grafiks = []
        for rg in route_grafiks_qs:
            rg.image = images[rg.mode_of_tptn.mode_of_transport]
            route_grafiks.append(rg)

        return render(request, 'route_grafiks.html', {
            'route_grafiks': route_grafiks,
        })

# in your template
{% for r in route_grafiks %}
    {{ r.image }}
{% endfor %}

Answered By - Bernhard Vallant