Filter by comma-separated values in the Django admin#

I have a text column which contains comma-separated values - inherited from an older database schema.

I should refactor this into a many-to-many field (or maybe even a PostgreSQL array field), but I haven’t done that yet. And I wanted to be able to filter by those values in the Django admin.

Since I’m using PostgreSQL, I decided to figure out how to do this using the PostgreSQL regexp_split_to_array() function.

There are two necessary SQL queries here: one to figure out all of the unique distinct values that are represented across all of those comma-separated lists, and one to filter for rows that include a specific value.

Here’s what I came up with for the first:

1
select distinct unnest(
2
  regexp_split_to_array(my_column, ',\s*')
3
) from my_table

This uses unnest(), see this TIL.

For filtering down to rows that contain a specific value in their comma-separated list, I figured out this:

1
select
2
  *
3
from
4
  my_table
5
where
6
  array_position(
7
    regexp_split_to_array(
8
      my_column, ',\s*'
9
    ),
10
    'MyValue'
11
  ) is not null

That second one, translated into the Django ORM, looks like this:

1
from django.contrib.postgres.fields import ArrayField
2
from django.db.models import F, IntegerField, TextField, Value
3
from django.db.models.expressions import Func
4

5
queryset.annotate(
6
    value_array_position=Func(
7
        Func(
8
            F(my_column),
9
            Value(",\\s*"),
10
            function="regexp_split_to_array",
11
            output_field=ArrayField(TextField()),
12
        ),
13
        Value(my_value),
14
        function="array_position",
15
        output_field=IntegerField()
16
    )
17
).filter(value_array_position__isnull=False)

I didn’t bother figuring out the ORM equivalent of that first unnest() SQL.

Here’s the reusable admin filter factory I came up with using these:

1
from django.contrib.admin import SimpleListFilter
2
from django.contrib.postgres.fields import ArrayField
3
from django.db import connection
4
from django.db.models import F, TextField, Value
5
from django.db.models.expressions import Func
6

7

8
def make_csv_filter(filter_title, filter_parameter_name, table, column):
9
    class CommaSeparatedValuesFilter(SimpleListFilter):
10
        title = filter_title
11
        parameter_name = filter_parameter_name
12

13
        def lookups(self, request, model_admin):
14
            sql = """
15
                select distinct unnest(
16
                    regexp_split_to_array({}, ',\\s*')
17
                ) from {}
18
            """.format(
19
                column, table
20
            )
21
            with connection.cursor() as cursor:
22
                cursor.execute(sql)
23
                values = [r[0] for r in cursor.fetchall() if r[0]]
24
            return zip(values, values)
25

26
        def queryset(self, request, queryset):
27
            value = self.value()
28
            if not value:
29
                return queryset
30
            else:
31
                return queryset.annotate(
32
                    value_array_position=Func(
33
                        Func(
34
                            F(column),
35
                            Value(",\\s*"),
36
                            function="regexp_split_to_array",
37
                            output_field=ArrayField(TextField()),
38
                        ),
39
                        Value(value),
40
                        function="array_position",
41
                        output_field=IntegerField()
42
                    )
43
                ).filter(value_array_position__isnull=False)
44

45
    return CommaSeparatedValuesFilter

Then you use it in a ModelAdmin subclass like this:

1
@admin.register(Reporter)
2
class ReporterAdmin(admin.ModelAdmin):
3
    list_filter = (
4
        make_csv_filter(
5
            filter_title="Roles",
6
            filter_parameter_name="role",
7
            table="reporter",
8
            column="role_names",
9
        ),
10
    )