How to join into the current table

[m1guelpf]

I'm working on a project which includes a Block record, which can include any number of other blocks as children. I'm trying to write a query like the following:

Block.find(id)
	.group(by: \.id)
	.join(Ancestor.all) { $0.id.eq($1.ancestorId) }
	.join(Block.all) { $1.blockId.eq($2.id) }
	.select { block, _, children in
		Block.WithChildren.Columns(block: block, content: children)
	}

The generated query is not valid, producing the error ambiguous column name: blocks.id:

SELECT
	"blocks"."id" AS "_id",
	"blocks"."content" AS "_content",
	json_group_array(
		json_object(
			'id', json_quote("blocks"."id"),
			'content', json_quote("blocks"."content"),
		)
	) AS "_content"
FROM "blocks"
JOIN "blockAncestors" ON ("blocks"."id") = ("blockAncestors"."ancestorId")
JOIN "blocks" ON ("blockAncestors"."blockId") = ("blocks"."id")
WHERE ("blocks"."id") IN ((?))
GROUP BY "blocks"."id"

Usually you'd alias the joined table to avoid the conflict, but I haven't found a way to do this with this library

Example valid query

SELECT
	b1."id" AS "_id",
	b1."content" AS "_content",
	json_group_array(
		json_object(
			'id', json_quote(b2."id"),
			'content', json_quote(b2."content"),
		)
	) AS "_content"
FROM "blocks" b1
JOIN "blockAncestors" ON b1."id" = "blockAncestors"."ancestorId"
JOIN "blocks" b2 ON "blockAncestors"."blockId" = b2."id"
WHERE b1."id" IN (?)
GROUP BY b1."id"

[mbrandonw]

Hi @m1guelpf, table aliases are supported and documented here: https://swiftpackageindex.com/pointfreeco/swift-structured-queries/main/documentation/structuredqueriescore/table/as(_:)

There are also many tests in the repo that demonstrate how to use this tool.