Feature Description

imagine Users and Files tables that are many-to-many related via UserFileShares (join) table.

the join table also includes metadata related to the share type, example: permission: "read-write", "write", etc...

i would like to use the query builder to populate the Users.sharedFiles field with a list of files and related permissions, like:

{
userId: 1,
name: "Foo",
sharedFiles: [{ filename: "...", permission: "readOnly"}]
}

how to achieve this ?

The Solution

queryBuilder
            .leftJoinAndMapMany("self.fileShares",UserFileShares , "fs", "fs.userId= self.userId")
            .leftJoinAndMapMany(
              "self.fileShares.*",
              Files,
              "f",
              "f.fileId= fs.fileId",
            )

Considered Alternatives

as a workaround i am now using a virtual column "permission" with query "select 1" and overwrite that column in the query builder, but this is so bad and works only for specific arguments ... :

queryBuilder
            .leftJoin(UserFileShares , "fs", "fs.userId= self.userId")
            .leftJoinAndMapMany(
              "self.fileShares",
              Files,
              "f",
              "f.fileId= fs.fileId",
            )
            .addSelect(`f.permission`, "f_permission")

Additional Context

No response

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, and I know how to start.