Tortoise-ORM: How to query a postgres `ArrayField` from tortoise.contrib.postgres.fields

user_693642 0
Viewed 125

Just created a Postgres ArrayField in Tortoise-ORM via from tortoise.contrib.postgres.fields import ArrayField to host some tags I wish to put. Looking at my postgres db the generated field is indeed of type text[] which is good but how exactly do I query it using the Tortoise Query API?

I've tried several attempts at using filter but each one either returns an empty list or raises an error.

Table

+----+---------------+
| id | tags (text[]) |
+----+---------------+
| 1  | {foo,bar}     |
+----+---------------+
| 2  | {foo}         |
+----+---------------+
| 3  | {bar}         |
+----+---------------+

# End results

# Works but uses: CAST("tags" AS VARCHAR) LIKE '%foo%'
await Card.filter(tags__contains='foo').values_list('id', flat=True)   

# Doesn't work
await Card.filter(tags__search=['foo']).values_list('id', flat=True)     # error
await Card.filter(tags__search='foo').values_list('id', flat=True)       # error
await Card.filter(tags=['foo']).values_list('id', flat=True)             # error
await Card.filter(tags='foo').values_list('id', flat=True)               # error
await Card.filter(tags__in=['foo']).values_list('id', flat=True)         # []
await Card.filter(tags__in='foo').values_list('id', flat=True)           # []

Also tried it using set or tuple but the results are the same.

Will scour the source more but any help is appreciated, captains.

"May your bugs be light and your bag of doritos chips heavy." -- Me

Update:

I haven't been able to find a way to query an ArrayField in TORM so I'm using JSONField instead which has much better support.

# Old
tags = ArrayField('text', null=True)    # can't query for now

# New
tags = JSONField(default=[])

Will keep this question here in case someone else has the same problem.

tortoise-orm fastapi orm python
1 Answers

I was also considering using JSONField, although in Postgres Array field is a bit more flexible in some cases. I found this comparison in DBA community.

I ended up implementing my own typed ArrayField: How to use Postgresql Array field in Tortoise-ORM

In your case, I suppose, you will need to implement your own filter operator for array contains. I would check out existing filters for an example: default filters and contrib Postgres filters.

Here is how contains operator looks in PostgreSQL itself: Postgres: check if array field contains value?

Hope this is somehow helpful :)

user_83856660
Related