My goal is to let the user select the number of results they want returned from the SQL query.
I have two API endpoints returning data
I'm then using the Query JSON with SQL resource to be able to join the two data sets together
That query looks something like
SELECT *
FROM
{{query1.data}} AS query1
LEFT JOIN {{query2.data}} AS query2 ON query1.member_email = query2.email
WHERE
status = 'Success'
ORDER BY query1.total DESC
LIMIT 10
I'm wanting to make LIMIT 10 be dynamic. So I've added a numberInput to the UI and updated my SQL to be
SELECT *
FROM
{{query1.data}} AS query1
LEFT JOIN {{query2.data}} AS query2 ON query1.member_email = query2.email
WHERE
status = 'Success'
ORDER BY query1.total DESC
LIMIT {{numberInput1.value}}
However, when I try to run this updated SQL, it just gives the error
Parse error on line 8: ...n_total DESCLIMIT ? ----------------------^ Expecting 'NUMBER', got 'QUESTION'
How can I make the LIMIT value in the SQL query be dynamic based on what the user selects in the UI?
Just a WAG, but what if you add row number, make it a CTE, and then select * from the CTE where row number < the input value? Kind of a long way around, but might get you the functionality you are looking for…
Thanks for the inspiration. You're right it's a long way around but points to an idea that should work. In looking it up, AlaSQL doesn't support ROW_NUMBER functions, so you can't do it just in the Query JSON with SQL resource. You'd have to run your SQL, then add a JS mapping function to add row numbers to the data then filter. Or just nix SQL and do it in straight JS, which is what I ended up doing. Something like:
var items = query1.data;
var relatedItems = query2.data;
if (items && relatedItems) {
// Step 1: Filter
var filteredItems = items.filter(item =>
item.status === 'Success'
);
// Step 2: Join
var joinedData = filteredItems.map(item => {
var relatedItem = relatedItems.find(relatedItem => relatedItem.email === item.member_email);
return {
...item,
...relatedItem
};
});
// Step 3: Sort
joinedData.sort((a, b) => b.total - a.total);
// Step 4: Add row numbers
joinedData = joinedData.map((row, index) => {
return {
row_num: index + 1,
...row
};
});
// Step 5: Select top X rows
var topXRows = joinedData.slice(0, select1.value);
// Output
return topXRows;
} else {
console.error('Data is not loaded correctly.');
return [];
}
