Closed leosuncin closed 11 months ago
Hi,
It here any reason why are you doing a subquery instead of joins? Usually a join will be referred over a subquery.
If I use join I can rewrote your query as:
const brand = new Brand();
const category = new Category();
const product = new Product().as('product');
const id = 1n;
const singleProductWithCategoryAndBrand = await connection
.selectFrom(product)
.join(category).on(category.id.equals(product.categoryId))
.join(brand).on(brand.id.equals(product.brandId))
.select({
id: product.id,
name: product.name,
modelYear: product.modelYear,
listPrice: product.listPrice,
category: {
id: category.id,
name: category.name
},
brand: {
id: brand.id,
name: brand.name
}
})
.where(product.id.equals(id))
.executeSelectOne();
This will do what you want in a more readable way. Can you explain yourself a little bit.
Note: In case your relation were optional, I will use a left join.
Some documentation: Complex projections, Inner objects and joins, Inner objects and left joins
Let me know your feedback.
I'm currently using mssql
to connect and query the DB, so I was trying to replicate the queries that I already have.
The one where I use FOR JSON AUTO
and JSON_QUERY
is to get a resultset ready to send as a JSON object, so with mssql
if I use joins then I'll have to transform the columns of the resultset into a JSON object before send it.
But, be aware, forUseAsInlineAggregatedArrayValue
will not return the JSON string representation; instead, a real object will be projected in JavaScript (yes, it uses for json path
in SQL Server to perform it, but you never will get the JSON string).
Sending the straightforward JSON representation of the output will be very limiting for your backend, and if that is the case, why do not just send the whole result of the query as JSON serialized in the DB?
I'm not sure why you want to hide those results to your backend but still keep some parts visible.
If you still need a JSON string representation of your query, you can use a Sql Fragment to change the output (the result of the fragment have a different type than the subquery type), something like this:
const brand = new Brand();
const category = new Category();
const product = new Product().as('product');
const id = 1n;
const singleProductWithCategoryAndBrand = await connection
.selectFrom(product)
.select({
id: product.id,
name: product.name,
modelYear: product.modelYear,
listPrice: product.listPrice,
category: connection.fragmentWithType('string', 'required').sql`JSON_QUERY((${
connection
.subSelectUsing(product)
.from(category)
.select({ id: category.id, name: category.name })
.where(category.id.equals(product.categoryId))
.forUseAsInlineAggregatedArrayValue(),
}), '$[0]')`
brand: connection.fragmentWithType('string', 'required').sql`JSON_QUERY((${
connection.
.subSelectUsing(product)
.from(brand)
.select({ id: brand.id, name: brand.name })
.where(brand.id.equals(product.brandId))
.forUseAsInlineAggregatedArrayValue(),
}, '$[0]')`
})
.where(product.id.equals(id))
.executeSelectOne();
I've released ts-sql-query 1.56.0, fixing an issue that will not allow you to use the subquery in a sql fragment.
Let me know what you think.
Given the following schema in SQL Server
Test data
```sql SET IDENTITY_INSERT brands ON; INSERT INTO brands(brand_id, brand_name) VALUES (1, 'Electra'); SET IDENTITY_INSERT brands OFF; SET IDENTITY_INSERT categories ON; INSERT INTO categories(category_id, category_name) VALUES (1, 'Children Bicycles'); SET IDENTITY_INSERT production.categories OFF; SET IDENTITY_INSERT production.products ON; INSERT INTO production.products(product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (1, 'Electra Cruiser 1 (24-Inch) - 2016', 1, 1, 2016, 269.99); SET IDENTITY_INSERT production.products OFF; ```I want to select a product with brand and category as JSON columns, so I build the following query
And produce the following query which is valid, I tested it directly in SQL Server
But when I run the code I got an error
Because
forUseAsInlineAggregatedArrayValue
expects an array but for my case it only produces one result in the subquery, so maybe you can add a method calledforUseAsInlineValue
for the cases when only a single result is expected