0

I have the following 3 PostgreSQL tables:

wallet:

+----+--------+-----------+-----------+--------+
| id | name   | createdAt | updatedAt | locked |
+----+--------+-----------+-----------+--------+
| 1  | name_1 | ...       | ...       | f      |
| 2  | name_2 | ...       | ...       | f      |
| 3  | name_3 | ...       | ...       | t      |
+----+--------+-----------+-----------+--------+

order:

+-----------------------------------+--------+-----------+-----------+
|    id    |    rate    |   status  | reason | createdAt | updatedAt |  
+-----------------------------------+------- +-----------+-----------+
| 4aface2d | 0.00062231 | accepted  |        | ...       | ...       |
| 9289add0 | 0.00062231 | new       |        | ...       | ...       |
| b3dfe546 | 0.00062231 | completed |        | ...       | ...       |
| f70ed6be | 0.00062231 | pending   |        | ...       | ...       |
+-----------------------------------+--------+-----------+-----------+

amount:

+----+-------+-----------+-----------+-----------+----------+
| id | value | createdAt | updatedAt |  orderId  | walletId |
+----+-------+-----------+-----------+-----------+----------+
| 1  | 200   | ...       | ...       | 4aface2d  | 1        |
| 2  | 100   | ...       | ...       | 9289add0  | 3        |
| 3  | 200   | ...       | ...       | b3dfe546  | 1        |
| 4  | 50    | ...       | ...       | 9289add0  | 2        |
| 5  | 100   | ...       | ...       | f70ed6be  | 3        |
| 6  | 200   | ...       | ...       | f70ed6be  | 2        |
+----+-------+-----------+-----------+-----------+----------+

Now I am struggling with composing a query that returns all the unlocked wallets (i.e. locked=false) together with all the related amounts, but where the amounts are filtered on status (e.g. only include amounts that have status='new' or status='accepted').

I've tried something like:

SELECT *

FROM "wallet"

LEFT JOIN "amount"

ON "wallet".id="amount"."walletId"

WHERE "amount"."orderId" IN (
    SELECT id FROM "order"
    WHERE "order".status='new' OR "order".status='accepted'
)

AND "wallet".locked=false;

but that doesn't quite do what I want, because all wallet results are also filtered on the order status condition. I only want the amount result to be filtered on the order status condition. Is such a thing even possible? Or do I need two separate queries for this?

Update

I think I have managed to get what I am after by changing my query to:

SELECT *

FROM "wallet"

LEFT JOIN "amount"

ON "wallet".id="amount"."walletId"

AND "amount"."orderId" IN (
    SELECT id
    FROM "order"
    WHERE status='new'
    OR status='accepted'
)

WHERE "wallet".locked=false;

Only thing now I am not sure about is if the IN (SELECT ...) part is efficient enough. I've read that when the table (i.e. "order") is relatively large then an IN based approach becomes slow.

0

You could use EXISTS instead of IN. That's likely to perform better.

SELECT *
       FROM "wallet"
            LEFT JOIN "amount"
                      ON "wallet"."id" = "amount"."walletId"
                         AND EXISTS (SELECT *
                                            FROM "order"
                                            WHERE "order"."id" = "amount"."orderId"
                                                  AND "order"."status" IN ('new',
                                                                           'accepted'))
       WHERE "wallet"."locked" = false;

To further improve the performance of the above maybe try indexes on "wallet" ("locked", "id"), "amount" ("walletid", "orderid") and order ("id", "status"). Unless you have them already, of course.

Check the plan if you're unsure which version performs better and what indexes help.

1
  • Nice one. Thanks! Forgot all about EXISTS. I don't do a lot of SQL stuff usually... And yes, was already planning to use indexes for all id's that are used in the different table comparisons.
    – Mikey Dee
    Apr 1 '19 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.