Feature: SQL view class

Is your feature request related to a problem? Please describe.

I want to create a SQL view but there's no builtin way to do that with Tortoise.

Describe the solution you'd like

from tortoise import fields
from tortoise.models import Model
from tortoise.views import View


class Customer(Model):
    id = fields.IntField(primary_key=True)
    name = fields.CharField(max_length=255)
    active = fields.BooleanField()


class Order(Model):
    id = fields.IntField(primary_key=True)
    customer_id: fields.IntField
    customer: fields.ForeignKeyRelation[Customer] = fields.ForeignKeyField(
        "models.Customer",
        related_name="orders",
        on_delete="RESTRICT",
    )


class CustomerOrder(View):
    # Using the same field functions as for models
    order_id = fields.IntField(source_field="id")
    # Use the double underscore separator to do joins to other tables
    customer_id = fields.IntField(source_field="customer__id")
    customer_name = fields.CharField(source_field="customer__name")

    class Meta:
        table = "customerorder"
        # Specify the source table + filter for the data
        source = Order.filter(customer__active=True)

async def get_order_info(customer_id: int) -> list[CustomerOrder]:
    return await CustomerOrder.filter(customer_id=customer_id)

which generates SQL like this:

CREATE VIEW "customerorder" AS
SELECT
    "order"."id" AS "order_id",
    "customer"."id" AS "customer_id",
    "customer"."name" AS "customer_name"
FROM "order"
JOIN "customer"
    ON "order"."customer_id" = "customer"."id"
WHERE "customer"."active" = TRUE;

and

SELECT
    "order_id",
    "customer_id",
    "customer_name"
FROM "customerorder"
WHERE "customer_id" = $1;

Describe alternatives you've considered

Writing SQL manually, but that's what an ORM is for.

Additional context

I understand the basic view SQL syntax is consistent across database implementations.

Start from the proposed View, CustomerOrder, and Meta.source API in the issue, then compare the requested CREATE VIEW and SELECT SQL behavior across the database implementations. Done means a SQL view can be declared with model-style fields and relations, created from the specified source query, and queried through CustomerOrder as shown.