I have the following query:
await prisma.$kysely
.selectFrom('Product')
.selectAll()
.innerJoin('BrandSupplier', 'Product.brandSupplierId', 'BrandSupplier.id')
.where(({ or, eb, and }) =>
or([
sql<SqlBool>`word_similarity("Product".name, 'heinnekinn') > 0.3`,
sql<SqlBool>`word_similarity("BrandSupplier".name, 'heinnekinn') > 0.3`
])
)
.select((eb) => [
jsonObjectFrom(
eb
.selectFrom('BrandSupplier')
.selectAll()
.whereRef('BrandSupplier.id', '=', 'Product.brandSupplierId')
).as('brandSupplier')
])
.execute();
The query seems to work but the nested relation (brand supplier) is also being written on top of parent. Here is what I mean:
Expected:
{
"id": 99999,
"name": "Heineken 330ml can",
"sizeMl": 330,
"brandSupplierId": 22222,
"brandSupplier": {
"id": 22222,
"name": "Heineken brewing co."
}
}
Actual:
{
"id": 22222,
"name": "Heineken brewing co.",
"sizeMl": 330,
"brandSupplierId": 22222,
"brandSupplier": {
"id": 22222,
"name": "Heineken brewing co."
}
}
2
The problem is that you are selecting the id column and the name column twice and they end up being stored as a single field. You will need to replace .selectAll() with a .select() which specifies your fields:
.select(
'Product.id AS pId',
'Product.name AS pName',
'Product.sizeMl',
'Product.brandSupplierId',
)
And do the join like
.innerJoin(
'BrandSupplier.id AS bsId',
'BrandSupplier.name AS bsName'
)
Because your function calls will ultimately translate to a query like
SELECT Product.id,
Product.name,
Product.sizeMl,
Product.brandSupplierId,
BrandSupplier.id,
BrandSupplier.name
FROM Product
JOIN BrandSupplier
ON Product.brandSupplierId = BrandSupplier.id
...
And since id and name of Product are the same name as their counterparts in BrandSupplier, first Product.id and Product.name are being read and they are overriden by their counterpart in BrandSupplier and by the time the result is being parsed, you have only the second id and name at play to read. So you need to alias them, but beware, the result set will now reference their aliased names.
3