How to create a model from a select query/CTE in peewee?

Lets say I have a table called flights and I want an aggregation on it like:

SELECT plane, count(flightId) as num from flights;

And let´s say I don´t want to create a permanent View. Can I use an SQL Query or its results as the source for a Model?

Solution

No, you would need to write that as a view. You can likely create a temporary view if you don't want it to persist in your schema, and then define a model that uses your temp view.

class FlightCount(Model):
    plane = TextField()
    num = IntegerField()

    class Meta:
        table_name = 'flight_count'

    @classmethod
    def create_view(cls):
        db.execute_sql('create temporary view flight_count as ...')

Then when you wish to use it,

FlightCount.create_view()
query = FlightCount.select(...)