The as_query method in the queryset.py file has been removed

[jsonvot]

Describe the bug
The as_query() method of the AwaitableQuery class was removed in version 0.24.0, and I haven't found any alternative method to replace it. This method was extremely useful for converting a queryset to a QueryBuilder. I'm wondering if this removal was an intentional/planned change, or an accidental one.

Here are two historical changes to this method:

[waketzheng]

Could you show code snippet of your use case?

[jsonvot]

For example, it is applied in some complex association query scenarios, but you do not want to use physical foreign keys.

from tortoise.models import Model

class User(Model):
	id
	name
	dept_id
	role_id

class Dept(Model):
	id
	name
	
class Role(Model):
	id
	name


tb_user = User._meta.basetable
tb_dept = Dept._meta.basetable
tb_role = Role._meta.basetable

query = (
	User.all().only('email', 'nickname', 'avatar').as_query().
	join(Dept.all().as_query()).on(tb_user.dept_id==tb_dept.id)
	join(Role.all().as_query()).on(tb_user.role_id==tb_role.id)
	select(
		tb_dept.name.as_('dept_name'),
		tb_role.name.as_('role_name')
	)
)
db = User._choose_db()
result = await  db.execute_query_dict(query.get_sql())

[waketzheng]

@henadzit Cloud you look at this

[seladb]

@jsonvot why don't you want to use foreign keys in your models? 🤔

[jsonvot]

@jsonvot why don't you want to use foreign keys in your models? 🤔

@seladb I am merely citing a simple usage case. In practice, you may encounter problems that the tortoise model cannot solve directly. In such cases, you have no choice but to convert the tortoise model into a pypika object to implement complex dynamic SQL.

[seladb]

@jsonvot why don't you want to use foreign keys in your models? 🤔

@seladb I am merely citing a simple usage case. In practice, you may encounter problems that the tortoise model cannot solve directly. In such cases, you have no choice but to convert the tortoise model into a pypika object to implement complex dynamic SQL.

@jsonvot obviously Tortoise has limitations, but it'd be useful to have an example that Tortoise API cannot solve. Maybe the solution could be extending the API?

[jsonvot]

@seladb
This kind of situation is quite common in dashboard chart queries or some special interfaces. Let me give you a simple example.Suppose I have the following model.
I aim to obtain the query results represented by the SQL in the example with the fewest possible SQL queries. However, the table names, fields, and query conditions in the SQL still need to be dynamically retrieved from the existing model instead of being hardcoded in the SQL, and I also do not wish to create new tables. Therefore, Pypika is the simplest approach that comes to my mind.

# models
class FaultData(Model):
	id:int
	...
	device_id:str
	dept_id:int
	create_at:datetime

class OfflineData(Model):
	id:int
	...
	device_id:str
	dept_id:int
	create_at:datetime
	
class AlarmData(Model):
	id:int
	...
	device_id:str
	dept_id:int
	create_at:datetime


# target sql
select * from (
	select device_id, dept_id, create_at, 1 as data_type, id as data_id from fault_data where dept_id = $1
		union all
	select device_id, dept_id, create_at, 2 as data_type, id as data_id from offline_data where dept_id = $1
		union all
	select device_id, dept_id, create_at, 3 as data_type, id as data_id from alarm_data where dept_id = $1
) as t order by create_at desc limit 20,0;

[seladb]

From my understanding, the missing piece in Tortoise is a union query which has an open issue. The rest should already be supported:

class FaultData(Model):
    id = fields.IntField(primary_key=True)
    device_id = fields.CharField(max_length=100)  # or foreign key?
    dept_id = fields.IntField()  # or foreign key?
    create_at = fields.DatetimeField()


class OfflineData(Model):
    id = fields.IntField(primary_key=True)
    device_id = fields.CharField(max_length=100)  # or foreign key?
    dept_id = fields.IntField()  # or foreign key?
    create_at = fields.DatetimeField()


class AlarmData(Model):
    id = fields.IntField(primary_key=True)
    device_id = fields.CharField(max_length=100)  # or foreign key?
    dept_id = fields.IntField()  # or foreign key?
    create_at = fields.DatetimeField()


async def main():
    from tortoise.expressions import Value, F
    from datetime import datetime, timedelta
    import asyncio

    dept_id = 1

    fault_qs = (
        FaultData.filter(dept_id=dept_id)
        .order_by("-create_at")
        .annotate(data_type=Value(1), data_id=F("id"))
        .values(
            "device_id",
            "dept_id",
            "create_at",
            data_type="data_type",
            data_id="data_id",
        )
    )

    offline_qs = (
        OfflineData.filter(dept_id=dept_id)
        .order_by("-create_at")
        .annotate(data_type=Value(2), data_id=F("id"))
        .values(
            "device_id",
            "dept_id",
            "create_at",
            data_type="data_type",
            data_id="data_id",
        )
    )

    alarm_qs = (
        AlarmData.filter(dept_id=dept_id)
        .order_by("-create_at")
        .annotate(data_type=Value(3), data_id=F("id"))
        .values(
            "device_id",
            "dept_id",
            "create_at",
            data_type="data_type",
            data_id="data_id",
        )
    )

    # Now you need to merge the 3 querysets in memory (instead of the DB). If the result set is too large it might be more complex, but should still be doable

Is merging in memory too complex or impossible in your use-case?

[jsonvot]

@seladb
In actual development, everyone adopts different approaches, and my example only represents one such scenario. Specifically, in my implementation, I intend to use Pypika objects to combine three SQL statements into a single paginated SQL query, which is then sent to the database (DB) for execution. While this approach may seem a bit complex, its logic is extremely clear, and the resulting dataset only occupies a small amount of memory on the web server side.
In contrast, a conventional approach would require executing three subqueries to retrieve large result sets, assigning them to three separate variables, and then merging these result sets before pagination can be processed—this would consume significantly more memory on the web server side.

[seladb]

I agree that running the union in the DB level is much better, but aside from it - is there anything that is missing and requires using Pypika / raw SQL?

Ideally users shouldn't use Pypika / raw SQL - the whole purpose of Tortoise is to mask it and provide an easier-to-use API...

If you want to use Pypika - why use it from Tortoise and not directly?

[seladb]

@jsonvot a union query was added in #2146 , is it sufficient for your needs?

[jsonvot]

@seladb

It seems that this cannot meet my requirements. What I need is to construct a statement like qs.join(qs2).on(qs.xx == qs2.xx), which narrows the query result set of qs through conditional association. qs.union() simply merges the query result sets of two queries with similar fields only.

[seladb]

@jsonvot this is indeed a complex scenario 🤯

I found the PR where it was removed: #1777

It was opened by @henadzit and reviewed by @abondar .

I personally don't mind adding it back. @abondar @henadzit what do you think?