How does serializer retrieve columns referred by a reverse foreign key?

Question

In a scenario of transaction processing, we have:

• table order to record incoming transactions, and
• table order_log to log updates on a recorded order, with a foreign key to table order.

A recorded order may have zero to multiple updates on the log.

We want to make a flattened view of the two tables like a SQL query selecting from order left outer join order_log, with the following behaviour:

• if the order has no update, list the order in joint with null values;
• if the order has one update, list the order in joint with the update log;
• if the order has multiple updates, list the order multiple times in joint with each update log.

As the sample source code below, we used .prefetch_related('orderlog_set'), and it gets the wanted flattening effect, and the Django backend log shows a left-outer-join SQL query sent to the database as expected. So, the mutual relation works correctly for all the three conditions above.

However, we cannot retrieve columns in order_item, as the table is in a foreign key's reverse direction.

The serializer FlatOrderLogSerializer bases its meta-model on the table order, so it refers to table order_log by a foreign key's reverse or backward direction. As a result, the serializer cannot retrieve the correct column and always gets null values.

We correctly retrieved columns in the native table and tables referred by a foreign key's normal direction. Unfortunately, just the reverse/backward foreign key does not work.

We are new to this part of Django and not sure how to correctly set the source attribute of the field object. We tried serializers.ReadOnlyField(source='orderlog_set__update_ts', ... and a few other options, but not working so far. See the comments in the sample code for more details.

Just let me know if you need more information, and any suggestions will be highly appreciated.

# Table 'order'
class Order(models.Model):
    ...

# Table 'order_log'
class OrderLog(models.Model):
    order = models.ForeignKey('Order')
    update_ts = models.DateTimeField(auto_now=True, editable=False, verbose_name="Last Updated On")
    ...

# The serializer
class FlatOrderLogSerializer(serializers.ModelSerializer):
    # Standing at table 'order', it refers to table 'order_log' by a reverse (backward) foreign-key.
    # The field with source='orderlog_set__update_ts' always gets null, and
    # we tried other source settings, e.g. 'orderlog_set.update_ts', or 'orderlog.update_ts', 
    # unfortunately nothing works so far.
    update_ts = serializers.ReadOnlyField(source='orderlog_set__update_ts', allow_null=True)
    ...
    class Meta:
        model = Order
        fields = (
            'update_ts',
            ...
        )

# The view
class FlatOrderLogView(generics.ListAPIView):
    serializer_class = rest_models.FlatOrderLogSerializer
    ...
    def get_queryset(self):
        flat_orderlogs = Order.active_objects.filter(
            ...
        ).prefetch_related(
            'orderlog_set',
        ).all()
        return flat_orderlogs

Answer 1

After some Google searching, it looks like:

• The Django framework provides a standard function select_related() for joining following a foreign key’s forward direction. The backend implements this function as an inner-join SQL query.
• However, we did not find an elegant way to look backward by an outer-join. Instead, most articles recommend using function prefetch_related() and making a nested serializer. And this design pattern seems to be a common practice.

Below are some tentative thoughts, and we hope to get feedback or corrections from experts:

With SQL query, we can refer to another table in the foreign key's backward direction by an outer-join. However, it seems that Django does not encourage this intuitive design pattern, therefore, did not implement a standard method. If confirmed, we will give up the outer-join query and look into other solutions instead.