SQL Query working differently in playground vs SDK
Hi (again),
I have the following code:
And the result of that query is
But, in the playground, I run the same query:
And then I get
Is there anything I may be doing wrong in here?
Thanks!!
const subquery = `SELECT DISTINCT (o.id)
FROM transactions t,
orders o
WHERE t.report = '${report_id}'
AND t.order = o.id`;
const total_amounts = await this.dbClient.sql<{
total_invoiced: number,
total_commission: number;
}>`SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
FROM orders
WHERE orders.id in (${subquery})`;
const subquery = `SELECT DISTINCT (o.id)
FROM transactions t,
orders o
WHERE t.report = '${report_id}'
AND t.order = o.id`;
const total_amounts = await this.dbClient.sql<{
total_invoiced: number,
total_commission: number;
}>`SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
FROM orders
WHERE orders.id in (${subquery})`;
{
records: [ { total_commission: null, total_invoiced: null } ],
rows: undefined,
warning: undefined,
columns: [
{ name: 'total_invoiced', type: 'float8' },
{ name: 'total_commission', type: 'float8' }
]
}
{
records: [ { total_commission: null, total_invoiced: null } ],
rows: undefined,
warning: undefined,
columns: [
{ name: 'total_invoiced', type: 'float8' },
{ name: 'total_commission', type: 'float8' }
]
}
SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
FROM orders
WHERE orders.id in (SELECT DISTINCT (o.id)
FROM transactions t,
orders o
WHERE t.report = 'rec_REDACTED'
AND t.order = o.id)
SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
FROM orders
WHERE orders.id in (SELECT DISTINCT (o.id)
FROM transactions t,
orders o
WHERE t.report = 'rec_REDACTED'
AND t.order = o.id)
{
"records": [
{
"total_commission": 2271.84,
"total_invoiced": 28398
}
],
"columns": [
{
"name": "total_invoiced",
"type": "float8"
},
{
"name": "total_commission",
"type": "float8"
}
],
"total": 0
}
{
"records": [
{
"total_commission": 2271.84,
"total_invoiced": 28398
}
],
"columns": [
{
"name": "total_invoiced",
"type": "float8"
},
{
"name": "total_commission",
"type": "float8"
}
],
"total": 0
}
2 Replies
Looks like the SDK does not allow the subquery as a parameter to avoid sql injection
Try something like:
const report = "rec_REDACTED";
const total_amounts = await xata.sql({
statement: `SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
FROM orders
WHERE orders.id in (SELECT DISTINCT (o.id)
FROM transactions t,
orders o
WHERE t.report = $1
AND t.order = o.id)`,
params: [report],
});
const report = "rec_REDACTED";
const total_amounts = await xata.sql({
statement: `SELECT SUM(orders.total_price) AS total_invoiced, SUM(orders.total_commission) AS total_commission
FROM orders
WHERE orders.id in (SELECT DISTINCT (o.id)
FROM transactions t,
orders o
WHERE t.report = $1
AND t.order = o.id)`,
params: [report],
});
Thank you, I'll check it out and will come back to you.
Perfect! Thank you!!