Lateral join support in QueryBuilder

[alexojegu]

Is your feature request related to a problem? Please describe.
Currently only is possible use lateral join with raw queries:

SELECT e0.id, e1.* FROM gallery AS e0 LEFT JOIN LATERAL (
    SELECT e2.* FROM photo AS e2 WHERE e2.gallery_id = e0.id LIMIT ?
) AS e1 ON e0.id = e1.gallery_id WHERE gallery_id IN (?)

Describe the solution you'd like
Would be possible that QueryBuilder have support for lateral join.

Describe alternatives you've considered
Continue using raw queries.

Additional context
lateral join it is supported by MySQL and PostgreSQL, I am not sure about SQLite support.

[B4nan]

Knex does not support those, so not sure how easy it would be to integrate it. Here is the issue on their end (4 years old, no response :/ ):

knex/knex#1651

[B4nan]

This issue is more up to date: knex/knex#3732

[alexojegu]

For now, to add more where clauses dynamically, I am trying:

    public async findGalleries(galleries: number[], limit: number): Promise<PhotoEntity[]> {
        const subquery = this.photoRepository.createQueryBuilder("e2").select("*");

        subquery.where({ gallery: { $eq: "e0.id" } });

        const results = await this.sqlConnection.execute(
            `select e0.id, e1.* from gallery as e0 left join lateral (
                ${subquery.orderBy({ created: "desc", id: "desc" }).limit(limit).getQuery()}
            ) as e1 on e1.gallery_id = e0.id where gallery_id in (?)`,
            [galleries],
        );

        /*
        const results = await this.sqlConnection.execute(
            `select e0.id, e1.* from gallery as e0 left join lateral (
                select e2.* from photo as e2 where e2.gallery_id = e0.id order by e2.created desc limit ?
            ) as e1 on e1.gallery_id = e0.id where gallery_id in (?)`,
            [limit, galleries],
        );
        */

        return results.map((result) => this.photoRepository.map(result));
    }

But I can't get it work.

Update: Using knex.raw works.

[SacDin]

This is indeed important feature, should be prioritised.

[B4nan]

This is blocked by knex not supporting it, if you want to see this feature in MikroORM, better to ask there first.

[robbyemmert]

Knex does support raw query fragments. Is there a way to do this with raw query fragments?

[B4nan]

Not really, but you can build any query (let it be via knex or any other QB or even manually), and execute and map it to the entities.

https://mikro-orm.io/docs/query-builder#mapping-raw-results-to-entities

[B4nan]

Implemented in v6 via 5c5174e