Nested Selects

[Playrom]

Hi

i'd like to replicate a query like this (leave aside the fact that is not useful a query like this, but is a valid SQL and it is just to explain the case)

SELECT *
FROM
	"records"
	LEFT JOIN (
		SELECT 
			"transfers"."record_id" as "transfersRecordId", 
			"sourceWallets"."id" as "sourceWalletId", 
			"sourceWallets"."name" as "sourceWalletName", 
			"destinationWallets"."id" as "destinationWalletId",
			"destinationWallets"."name" as "destinationWalletName"
		FROM "transfers"
		JOIN "wallets" AS "sourceWallets" ON ("transfers"."source_wallet_id" = "sourceWallets"."id")
		JOIN "wallets" AS "destinationWallets" ON ("transfers"."destination_wallet_id" = "destinationWallets"."id")
	) ON ("records"."id" = "transfersRecordId")

I thought that using an already joined table as parameter of a join, would create such a subquery, but in reality the joins are appended to the leftJoin

RecordTable
    .leftJoin(
        TransferTable
            .join(WalletTable.as(SourceWalletsTable.self).all, on: { transferTable, sourceWalletsTable in transferTable.sourceWalletId.eq(sourceWalletsTable.id) })
            .join(WalletTable.as(DestinationWalletsTable.self).all, on: { transferTable, _, destinationWalletsTable in transferTable.sourceWalletId.eq(destinationWalletsTable.id) }),
        on: { recordsTable, transfersTable, _, _ in recordsTable.id.eq(transfersTable.recordId) }
    )

This Is the query generated by the package

SELECT
  "records"."id",
  "records"."name",
  "records"."type",
  "records"."amount",
  "records"."currency",
  "records"."created_at",
  "transfers"."record_id",
  "transfers"."source_wallet_id",
  "transfers"."destination_wallet_id",
  "sourceWallets"."id",
  "sourceWallets"."name",
  "destinationWallets"."id",
  "destinationWallets"."name"
FROM
  "records"
  LEFT JOIN "transfers" ON ("records"."id" = "transfers"."record_id")
  JOIN "wallets" AS "sourceWallets" ON (
    "transfers"."source_wallet_id" = "sourceWallets"."id"
  )
  JOIN "wallets" AS "destinationWallets" ON (
    "transfers"."source_wallet_id" = "destinationWallets"."id"
  )

It is possible to create a query like the one I'm trying to create using the package, or it is a feature that in case needs to be implemented?
I already tried to use CTEs, but as already discussed in another discussion it may not be the right tool for the case

[mbrandonw]

Hi @Playrom, we do not currently support doing LEFT JOIN (SELECT …). Typically it's best to avoid queries like that because SQLite can't always flatten it into something more efficient (see here for more details). And CTE's are the easiest way to flatten things.

[Playrom]

Ya but with using CTEs I have the same problem I've discussed before in my failed PR, it is difficult to get not homogenous data from a single query without doing a lot of if/else for the content

but I think this is the best for efficiency o SQL and to have library that is not so complex