Struggling with subqueries
I want to basically do this query in drizzle:
SELECT o.id, o.date, t.currency
FROM "Operations" o
LEFT JOIN "Transactions" t ON o."id" = t."operationId"
WHERE o.id IN (
SELECT DISTINCT t."operationId"
FROM "Transactions" t
WHERE t.currency = 'usd'
ORDER BY t."operationId" DESC
LIMIT 8
);
Im using postgres-js and the problem is how to select those operations from the subquery.
This query is just a mock, but the reason to do the subquery is that I have to put conditions on both the operations and the transactions table, and if i just make a join of these queries and put the conditions, i will get the last 8 transactions for example, when what I really want is the last 8 operations and all their related transactions (meaning possibly more than 8 rows), even the ones whose currency isn't 'usd' for example. Doing raw sql it works as intended but I hoped to get it working with drizzle
Suggestions? Thanks in advance
SELECT o.id, o.date, t.currency
FROM "Operations" o
LEFT JOIN "Transactions" t ON o."id" = t."operationId"
WHERE o.id IN (
SELECT DISTINCT t."operationId"
FROM "Transactions" t
WHERE t.currency = 'usd'
ORDER BY t."operationId" DESC
LIMIT 8
);
Im using postgres-js and the problem is how to select those operations from the subquery.
This query is just a mock, but the reason to do the subquery is that I have to put conditions on both the operations and the transactions table, and if i just make a join of these queries and put the conditions, i will get the last 8 transactions for example, when what I really want is the last 8 operations and all their related transactions (meaning possibly more than 8 rows), even the ones whose currency isn't 'usd' for example. Doing raw sql it works as intended but I hoped to get it working with drizzle
Suggestions? Thanks in advance
