[Django]-Filter/order django rest api on a property=value but property is a value instead of a field

by

1👍

✅

What you have looks like a standard EAV model.

You might consider looking at a package like django-eav2 and see if that helps somewhat. I don’t think it handles ordering, which is sensible since its actually difficult to do through an ORM.

Have you considered changing your model to either use a JSON column or HStore (if you are on postgres)? Depending on your data model they could work perfectly and are also easily indexed, searched, and ordered by.

If you want to do it yourself, there are a few things you’ll need to write:

  • Helper functions to filter & order by fields in the attributes table
  • Custom OrderingBackend implementation
  • Custom FilterField to handle filtering by an attribute
  • Custom DjangoFilterBackend depending on how you want the urls to behave

If you can provide a better example of what you’d like the URL to look like then it’ll be easier to help give some guidance. Since your attributes can have any name, you have some choices to make:

  1. Is the url parameter the attribute name (e.g. ?title=)
  2. Do you use a custom url format, like ?attr=title&value=The Big Bang
  3. Do you need to filter on the Object level?
  4. Can you filter / order by more than one thing at a time?
  5. How do you handle different data types (date/int/string/etc?)

Sorry, but this is just too big a question for a single answer here. Though it is possible if you try hard enough, building EAV queries is not easy and they are not fast.

👤Andrew

4👍

Consider what you are trying to accomplish and how it maps to SQL queries (using ORM is convenient, but you have to always keep in mind that all your ORM calls get translated into SQL). You have a table books and you have a table properties. Lets say

create table books (
    id integer primary key,
);

create table properties (
    id integer primary key,
    name text,
    value text,
    book_id integer,
)

And then you insert records like this

insert into books set (id) values (1);
insert into properties set (id, name, value, book_id) values (1, 'title', 'A Book', 1)

And then you want to pull books and titles, so you end up with something like this

select b.id, p.value as "title"
from books as b
join properties as p on (b.id = p.book_id)
where p.name = 'title'
order by p.value

Unfortunately, building the query above using the ORM is not trivial. The reason for that is that by introducing both the name of the property and the value as data instead of writing data into a specific field, you are subverting your ability to refer to specific properties. You cannot simply refer to the book title as a specific field because it is not a field. What if the book has no ‘title’ property record? What if it has more than one?

You can do what you want using the Subquery() function from the ORM API, but I have never personally used it, so I am not sure what the exact syntax for that would be. Probably something like this

title = Property.objects.filter(name='title', book_id=OuterRef('pk')).values('value')[:1]
books = Object.objects.annotate(title=Subquery(title)).order_by('title')

https://docs.djangoproject.com/en/2.2/ref/models/expressions/#subquery-expressions

That said, this is fragile and hard to reason about and you might want to reconsider your database architecture.

👤Mad Wombat

1👍

Assuming you have the following models:

class Object(models.Model):
    name= Charfield(max_length=80)

class Property(models.Model):
    object= ForeignKey(Object, related_name='properties')
    name= Charfield(max_length=80)
    value= Charfield(max_length=80)

then you have instances of Object having only one property, with name='title'.

In this setting, I would go with something like:

Object.objects.filter(
    properties__name=Value('title'), 
    properties__value__icontains=request.data['title']
    )

for filtering.

Regarding ordering, you’ll hit an other issue: you attempt to order by a field of a Many2One relationship (many properties for one object), but only on one of the related objects in this relation… Subqueries as proposed in this answer might be the way to go, but I have personally already tried to do that without success.

This answer precises that your structure is hard to handle. Indeed, in your case, assuming you add at some point the authors of your books as Object instance, these objects will not have the title property at all…

If you cannot forgo this db architecture, I would advise looking into JSON fields or HStore fields. DjangoORM provides ways to query these fields according to what you try to achieve. However, be aware that this will still be slower than using a tradition architecture for RDBMSs.

Otherwise, create a ‘real’ Book model, that has a title, and in this case, everything is much simpler 😉

EDIT: also you have the possibility to write your query directly in SQL, either executed from a cursor directly (see here) or from a queryset of the model (see here). I’ve used both approaches in various scenarios, you’ll have to be careful with how you handle user input, but it should work =)

👤Moi

0👍

You can redefine method get_queryset() that prepares query to database and can do sorting for you by field(-s) you provide.
Have a look to example that I provided. Not sure if it works out-of-the box, but hope that you understood the logic. But if you’ll have questions – please add comment.

class ObjectViewSet(ModelViewSet):

    def get_queryset(self):
        queryset = super(ObjectViewSet, self).get_queryset()

        order_by = self.request.query_params.get('order_by', '')
        if order_by:
            order_by_name = order_by.split(' ')[1]
            order_by_sign = order_by.split(' ')[0]
            order_by_sign = '' if order_by_sign == 'asc' else '-'
            queryset = queryset.order_by(order_by_sign + order_by_name)

        return queryset
👤wowkin2

Leave a comment