Complex queryset with django content type model

Question

I have a set of models that contain content that is created and contributed by users.

Model User:

class User(models.Model):
    first_name = models.CharField(max_length=30, blank=True)
    last_name = models.CharField(max_length=150, blank=True)
    is_active = models.BooleanField(default=True)

Model Tip:

class Tip(models.Model):
    title = models.CharField(max_length=30, blank=True)
    content = models.CharField(max_length=150, blank=True)

Model Example:

class Example(models.Model):
    headline = models.CharField(max_length=30, blank=True)
    content = models.CharField(max_length=150, blank=True)

Model Struggle:

class Struggle(models.Model):
    headline = models.CharField(max_length=30, blank=True)
    content = models.CharField(max_length=150, blank=True)

and model UserContribution

class UserContribution(models.Model):
    id = models.AutoField(primary_key=True)

    contributed_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        verbose_name="User that contributed the object",
        on_delete=models.CASCADE
    )
    contributed_at = models.DateTimeField(auto_now_add=True)

    object_id = models.PositiveIntegerField(
        help_text="Primary key of the model",
    )
    content_type = models.ForeignKey(
        ContentType,
        on_delete=models.CASCADE
    )

I want to be able to select a set of users and list the contribution objects they have contributed (created or updated). For example,

[
    {
        "user_id": 1,
        "first_name": "A",
        "last_name": "B",
        "tips": [
            {
                "id": 1,
                "title": "abc",
                "content": "bcd",
                "contibuted_at": "2021-08-10"
            },
            {
                "id": 2,
                "title": "eabc",
                "content": "abcd",
                "contibuted_at": "2021-08-09"
            }
        ],
        "examples": [
            {
                "id": 1,
                "headline": "abc",
                "content": "bcd",
                "contibuted_at": "2021-08-10"
            },
            {
                "id": 2,
                "headline": "eabc",
                "content": "abcd",
                "contibuted_at": "2021-08-09"
            }
        ],
        "struggles": [
            {
                "id": 1,
                "headline": "abc",
                "content": "bcd",
                "contibuted_at": "2021-08-10"
            },
            {
                "id": 2,
                "headline": "eabc",
                "content": "abcd",
                "contibuted_at": "2021-08-02"
            }
        ]
    },
    {
        "user_id": 2,
        "first_name": "C",
        "last_name": "D",
        "tips": [
            {
                "id": 1,
                "title": "abc",
                "content": "bcd",
                "contibuted_at": "2021-09-09"
            },
            {
                "id": 3,
                "title": "eabc",
                "content": "abcd",
                "contibuted_at": "2021-09-02"
            }
        ],
        "examples": [
            {
                "id": 1,
                "headline": "abc",
                "content": "bcd",
                "contibuted_at": "2021-09-10"
            },
            {
                "id": 3,
                "headline": "eabc",
                "content": "abcd",
                "contibuted_at": "2021-08-09"
            }
        ],
        "struggles": [
            {
                "id": 1,
                "headline": "abc",
                "content": "bcd",
                "contibuted_at": "2021-09-10"
            },
            {
                "id": 2,
                "headline": "eabc",
                "content": "abcd",
                "contibuted_at": "2021-08-09"
            }
        ]
    }
]

Is there a specific way this can be achieved using Django's ORM, or do I have to use a raw SQL query? And what would the most efficient way be to achieve this in raw SQL?

Answer 1

I think you can get most of this by writing an admin class object for your models and create list_filter to access the content of child or sibling models. Assuming the UserContribution model is the 'parent' model. As example in your main app's admin.py create a admin model for UserContribution and register it: eg.

#admin.py

    @admin.register(UserContribution)
    class UserContributionAdmin(admin.ModelAdmin):
    ordering=('contributed_by','object_id',)
    list_display = ('id','contributed_by','object_id','contributed_at', 'content_type',)
    search_fields = ('contributed_by','content_type',)
    readonly_fields=('contributed_by','id',)
    actions = [export_as_csv] #you will need to define this export_as_csv function above the current one if you require it

    filter_horizontal = ()
    # below is where you create the filters that will appear on the admin panel
    list_filter = ('contributed_by','content_type','struggle__headline','example__headline','tip__title',)
    fieldsets = ()
    list_per_page=20

These filters should appear in your admin panel for the app and create querysets which you can access from the 'actions' you will create or you can just set up the display to show what your are looking for. The format of the items in '' is key to accessing the data fields depending on the model you create this admin function for. This example is for the UserContribution[UC] model so all of its model fields are accessed directly. To access a child model field (or related table) use: 'modelname__fieldname' separated with double underscore all lowercase as shown above. You can even access a child field from another child field via the common parent! For example in class TipAdmin(admin.ModelAdmin) you can write a filter to show the Tips of those who had a specific Struggle by accessing the parent eg

list_filter=('usercontribution__struggle__headline',)

VCool! If the filters have too many options you can reformat them with built in dropdown filters that can be imported such as

    from more_admin_filters import MultiSelectDropdownFilter
    from django_admin_listfilter_dropdown.filters import DropdownFilter
    #these go with the filter instantiations
    list_filter=('contributed_by',('content_type',DropdownFilter),)

viola!