In a scenario of transaction processing, we have:
orderto record incoming transactions, and
order_logto log updates on a recorded order, with a foreign key to table
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.
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