Django-Tables2 add extra columns from dictionary

Question

I apologize if this question has been asked before but I couldn't find my specific use case answered.

I have a table that displays basic product information. Product details such as price, number of sales, and number of sellers are scraped periodically and stored in a separate database table. Now I want to display both the basic product information and scraped details in one table on the frontend using tables2. To do this, I wrote a function in my Product model to fetch the latest details and return them as a dictionary this way I can use a single Accessor call.

# models.py

class Product(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)

    name = models.CharField(max_length=256)
    brand = models.ForeignKey(Brand)
    category = models.CharField(max_length=128, choices=CATEGORY_CHOICES)

    def __unicode__(self):
        return self.name

    def currentState(self):
        currentDetailState = ProductDetailsState.objects.filter(
            product=self
        ).latest('created_at')

        # return current details as a dictionary
        return {
            price: currentDetailState.price,
            num_sellers: currentDetailState.num_sellers,
            num_sales: currentDetailState.num_sales
        }


class ProductDetailsState(models.Model):
    product = models.ForeignKey(Product)
    created_at = models.DateTimeField(auto_now_add=True)

    price = models.DecimalField(max_digits=6, decimal_places=2, null=True)

    num_sellers = models.IntegerField(null=True)
    num_sales = models.IntegerField(null=True)

    def __unicode__(self):
        return self.created_at



# tables.py

class ProductTable(tables.Table):
    productBrand = tables.Column(
        accessor=Accessor('brand.name'),
        verbose_name='Brand'
    )
    currentRank = tables.Column(
        accessor=Accessor('currentRank')
    )

    class Meta:
        model = Product
        ...

How do I now use this returned dictionary and split it into columns in my Product table? Is there another way to use an Accessor than how I am doing it?

Jieter · Accepted Answer · 2018-08-18T11:46:07.110

You can use the Accessor to traverse into the dict, so something like this should work:

class ProductTable(tables.Table):
    # brand is the name of the model field, if you use that as the column name, 
    # and you have the __unicode__ you have now, the __unicode__ will get called, 
    # so you can get away with jus this:
    brand = tables.Column(verbose_name='Brand')
    currentRank = tables.Column()

    # ordering on the value of a dict key is not possible, so better to disable it.
    price = tables.Column(accessor=tables.A('currentState.price'), orderable=False)
    num_sellers = tables.Column(accessor=tables.A('currentState.num_sellers'), orderable=False)
    num_sales = tables.Column(accessor=tables.A('currentState.num_sales'), orderable=False)

    class Meta:
        model = Product

While this works, sorting is also nice to have. In order to do that, your 'currentState' method is a bit in the way, you should change the QuerySet you pass to the table. This view shows how that could work:

from django.db.models import F, Max
from django.shortcuts import render
from django_tables2 import RequestConfig

from .models import Product, ProductDetailsState
from .tables import ProductTable


def table(request):
    # first, we make a list of the post recent ProductDetailState instances
    # for each Product.
    # This assumes the id's increase with the values of created_at, 
    # which probably is a fair assumption in most cases.
    # If not, this query should be altered a bit.
    current_state_ids = Product.objects.annotate(current_id=Max('productdetailsstate__id')) \
        .values_list('current_id', flat=True)

    data = Product.objects.filter(productdetailsstate__pk__in=current_state_ids)

    # add annotations to make the table definition cleaner.
    data = data.annotate(
        price=F('productdetailsstate__price'),
        num_sellers=F('productdetailsstate__num_sellers'),
        num_sales=F('productdetailsstate__num_sales')
    )
    table = ProductTable(data)
    RequestConfig(request).configure(table)

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

This simplifies the table definition, using the annotations created above:

class ProductTable(tables.Table):
    brand = tables.Column(verbose_name='Brand')
    currentRank = tables.Column()

    price = tables.Column()
    num_sellers = tables.Column()
    num_sales = tables.Column()

    class Meta:
        model = Product

You can find the complete working django project at github

thank you! That's exactly what I was looking for. I'm pretty bummed that there's no way to order it though.. Is there some solution that would allow me to sort it? — AlfredoGG, Aug 15 '18 at 16:47
It would be easier if you would use a different query to select the data for the table, I think you could get around your custom method using [`FilteredRelation`](https://docs.djangoproject.com/en/2.0/ref/models/querysets/#filteredrelation-objects), also allowing django/django-tables2 to know how to order on those columns. — Jieter, Aug 16 '18 at 10:05
can you give me an example of how to use the FilteredRelation in my case or at least a brief description? Should I make a method in my Product model to return something like ProductDetaisState.objects.annotate(currentState=FilteredRelation(...))? — AlfredoGG, Aug 17 '18 at 17:45
hang on, I dove into this, but still working out how to get the sorting work nicely. — Jieter, Aug 18 '18 at 11:11
This might also give your more inspiration, at least it's what I referred to while updating my answer: https://stackoverflow.com/questions/2074514/django-query-that-get-most-recent-objects-from-different-categories — Jieter, Aug 18 '18 at 11:47
that worked perfectly! Thank you. I have only tested that it sorts but it seems to be correct. A quick question: what does `current_id=Max('productdetailsstate__id')` do? Does it sort the productDetailsState by ids in descending order then you just get the first one with a matching Product foreign key to get the latest entry? — AlfredoGG, Aug 21 '18 at 18:46
`current_id=Max('productdetailsstate__id')` adds an attribute `current_id` to the `Product` model with highest value of the corresponding `ProductDetailsState` — Jieter, Aug 23 '18 at 18:25

Django-Tables2 add extra columns from dictionary

1 Answers1