search

tortoise / tortoise-orm

Familiar asyncio ORM for python, built with relations in mind
https://tortoise.github.io
Apache License 2.0
4.63k stars 385 forks source link

Natively support PGARRAY from PostgreSQL within the ORM #370

Open odimko opened 4 years ago

odimko commented 4 years ago

Is your feature request related to a problem? Please describe. The request is not related to a problem and it is about a new feature, namely, I would like to be able to do exact matching of elements in a column in a database with a provided list of values natively by the means of PGARRAY data type, PostgreSQL built-in.

Describe the solution you'd like Possible usage is mentioned in the fixed issue: using __icontains for string matching returns partial string matches. However, fo my use case, I need exact matches. Thus, I would like, instead of implicitly converting JSONB to VARCHAR, to be able to have my data in the PGARRAY data type, so that if I have two rows with ["book", "new book", "interesting bookstore", "interesting book"] and ["new book", "interesting bookstore", "interesting book"] values in the Phrases column respectively, to search for matches with "book" and only get the first row, since it contains this element.

Describe alternatives you've considered A solution (and a manual alternative) would be to create a new field type in a similar fashion as here for the JSONB field: https://github.com/tortoise/tortoise-orm/blob/bad27105b07f85e9ea2213be3e451024a08a12dd/tortoise/fields/data.py#L371 After that a new filter is needed, that could also be created similarly to https://github.com/tortoise/tortoise-orm/blob/a3d8507fa529c89bc9f09dfcce16af2877e0d2a3/tortoise/filters.py#L150

Additional context The feature would bring more value to the ORM when it comes to the exact matching of elements with a database.

grigi commented 4 years ago

Seems reasonable, we should do it.

Ok, for filtering by it, the SQL would be something like:

SELECT * FROM foo WHERE ANY(bar) = 'value'

Ah, and UNNEST(bar) would be a good reverse aggregate? Possibly only usable in .values()? And there is also index access support? How would we even filter on that?

I think we should create an ArrayField but the actual type of field is flexible, so can we do CharArrayField, IntArrayField, TextArrayField?

We will have to try and implement some kind of emulation for SQLite and MySQL which don't support this (doesn't have to be perfect, e.g. I have no idea how to emulate UNNEST())

odimko commented 4 years ago

Hi @grigi! It's nice to see you have answered so fast :) I am afraid I have not been fully clear on what I was requesting, sorry for the possible misunderstanding. What I meant in my request was to have the functionality in tortoise-orm similar to the array functions in PostgreSQL, described in the documentation, specifically (please see Table 9.51. Array Operators):

Operator Description Example Result
<@ is contained by ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] t

So essentially I would like to be able to check if one array is contained in another one (if each of its elements appears in the other array). Please let me know if this makes things clearer.

odimko commented 4 years ago

hey @grigi! I was wondering if there are any updates on this request? Thanks in advance.

grigi commented 4 years ago

Hi @odimko No, sorry no updates. I don't have much time this week to do anything much.

odimko commented 4 years ago

Hi @grigi! Just checking in to ask if there's any progress with my request. Any update would be appreciated. Cheers, Dmytro.

grigi commented 4 years ago

Sorry, no. Since the whole covid-19 thing I have extremely limited time. I'm still around and healthy, but don't see much time becoming available for the next few weeks. if anybody wants to have a go at this feature, you're welcome.