How to filter an item in a database by different fields in python?

Question

I am creating an application with FastAPI and I want to filter 'items' in an endpoint according to their name and category. However, I want that if any of these two fields is not passed, it is not taken into account, and if both are passed, both filters are applied. I have written the following code for this:

async def filter_items_by_name_category(
        self,
        name: str | None = None,
        category: str | None = None
    ):
        if name is not None:
            items_by_name = await self.model.filter(name__icontains = name).all()
            if category is None:
                return items_by_name

        if category is not None:
            items_by_category = await self.model.filter(category = category).all()
            if name is None:
                return items_by_category

        items = [item for item in items_by_name if item in items_by_category]
        return items

The code works but it seems very inelegant to me! I was thinking that maybe there is another better implementation. I am using tortoise orm but I think it will not have relevance in the code.

Patrick Wang · Answer 1 · 2022-12-28T18:58:27.607

1

I think you should be able to dynamically generate the Q object that you need.

Something like:

async def filter_items_by_name_category(
        self,
        name: str | None = None,
        category: str | None = None
    ):
        qs = []

        if name is not None:
            qs.append(Q(name__icontains = name)

        if category is not None:
            qs.append(category = category)

        return await self.model.filter(Q(*qs, join_type="AND")).all()

edited Dec 28 '22 at 18:58

answered Dec 28 '22 at 18:57

Patrick Wang

11
3

Thanks a lot! I really like this implementation! I knew there were better ways to do it! – Diegol Dec 29 '22 at 00:30

score 1 · Answer 2 · answered Dec 28 '22 at 20:25

1

In addition to the example given by Patrick Wang, the returned query set objects can be further expanded with filters, so you can keep on extending the query for each part:

query = self.model

if name is not None:
    query = query.filter(name__icontains = name)

if category is not None:
    query = query.filter(category = category)

return await query.all()

answered Dec 28 '22 at 20:25

MatsLindh

49,529
4
53
84

Incredible! I did not know that! Thank you very much for sharing it, I will use it in my day to day. – Diegol Dec 29 '22 at 00:31

score 0 · Answer 3 · answered Dec 28 '22 at 19:01

How about this:

async def filter_items_by_name_category(
        self,
        name: str = "",
        category: str = ""
    ):

    items_by_name = []
    items_by_category = []

        if name:
            items_by_name = await self.model.filter(name__icontains = name).all()
            if not category:
                return items_by_name

        if category:
            items_by_category = await self.model.filter(category = category).all()
            if not name:
                return items_by_category

        return [item for item in items_by_name if item in items_by_category]

Tidied a bit by using truthy/falsy values (which avoids None as a default) and returning items instead of setting as a variable and then returning it.

Also set default empty values so the function will return [] if neither name nor category gets set.

I would also suggest breaking each filter into its own function, so that you can test and reuse each one independently.

How to filter an item in a database by different fields in python?

3 Answers3