using custom select

[AEnterprise]

Is your feature request related to a problem? Please describe. I'm working with a complex dataset involving geodata, currently i'm writing some pretty complex querries to find the a set of locations around.

Tortoise doesn't support most of the stuff i'm doing (or i couldn't find any way atleast to insert vars into a query via subquerry or to make it do the math expressions) so i'm executing it with transaction.execute_sql

Describe the solution you'd like It would be amazing if i could pass this list of dicts i get from this querry to tortoise to get the parsed objects linked to the database back (especially with the upcomming pydantic support that would greatly simplify the api code)

Describe alternatives you've considered Currently i just have to process and use the dicts myself

[grigi]

I can think of three solutions to this (there may be more):

1. We allow adding custom Pypika terms to the queryset, so one can build the query one needs
2. We do a custom SQL runner that can pass the result into models or return as raw dict/tuples. This would then work as long as the field names are unchanged and all required fields are selected?
3. A hybrid query approach?

I suppose different people would want different solutions. I know proposal 1 is what SQLAlchemy does, and I know one person that loves that. I often find writing SQL directly is easier.

Problem is DB compatibility. If we handle the simpler differences, e.g. parameter passing. That would enable at least some compatibility for simpler queries, but the moment you start doing something more complicated, you will have to be on your own. At least we can encourage a safe parameter passing to protect against SQL attacks.

The hybrid solution... the more I think of it, the more dangerous I feel it is.That would essentially require writing partial queries, which means you need to know BOTH SQL and Tortoise internals (and how they interfere). That is a lot of cognitive overhead.

I think you are asking for solution 2?

[AEnterprise]

i personally think option 2 is the easiest for people (or well atleast for me) as it doesn't require you to be familiar with pypika or have to learn it it's intricacies to write queries, requiring all fields to be selected under the proper names makes perfect sense as requirement to me. (or well all the ones tortoise is aware exist)

also just realized i forgot to add this but the querry is basically an adaptation of https://mariadb.com/resources/blog/mariadb-server-10-2-json-geojson-gis/

not even sure if there is a good way to make pypika generate that

[grigi]

Yes, I was leaning to option 2 as well.

Could you propose how such an API would look/work? Just so we get a clearer spec.

[AEnterprise]

my initial idea was to just have something like Model.custom_select that takes an string query and params (just like Transaction.execute_sql does)

then it would run this as a regular filter does, just using the passed query instead of generating one, bypassing the current building process