[Feature Request]: Query generation as an alternative to ORM

[Wulfheart]

Description

Personally I don't like ORMs that much as I want to know what is going on under the hood and if I f something up I want to be able to explain my query in the DB. While wrting go I found out about https://sqlc.dev/ which allows to generate type safe gateways (name pending :D).

Benefits

You are on a lower level than when using an ORM and you are more in control. Just wanted to put this out there for discussion as this would be something that is not yet present in the PHP space.

[brendt]

So I realise this isn't properly documented yet, but Tempest's ORM is very limited, by design. I want people to write their own queries whenever they want more flexibility or whenever they need something that's more complex than simple a SELECT and JOIN.

Tempest allows you to write plain old queries, and map their result to objects like so:

$books = map(new Query("
    SELECT * 
    FROM Book
    LEFT JOIN …
    HAVING … 
"))->collection()->to(Book::class);

You don't even have to implement any DatabaseModel interface for this to work, Book can be a plain old class.

Things get a little more complex if you want to load relations within the same query (which is the most important thing the ORM is managing behind the scenes). I cannot guarantee it'll work out of the box without the ORM, simply because I haven't properly tested it yet.

However, with map(new Query())->to(Model::class), you could easily build your own gateway classes.

It would of course be very interesting if we could generate these gateway classes for models directly. Maybe that's also what you were asking about? I for sure am open to that idea!

[Jeroen-G]

In case you didn't know and are looking for inspiration: Laminas has these table and row gateways.