Typeorm anyway to join table with rows that satisfy a condition while keep all data in main table

Viewed 32

Assuming there is an OneToMany relation between A and B, and B has a nullable property p,

 A:        B:
| id |    | id | A_id | p  |
|----|    |----|------|----|
| A1 |    | B1 | A2   |    |
| A2 |    | B2 | A2   |    |
| A3 |    | B3 | A2   | p1 |
|    |    | B4 | A3   | p2 |

When do an A LEFTJOIN B query, it will get

| A_id | B_id | B_p |
|------|------|-----|
| A1   |      |     |
| A2   | B1   |     |
| A2   | B2   |     |
| A2   | B3   |  p1 |
| A3   | B4   |  p2 |

What I want to do is: when keeping all As still there, join all B which p is null to them

something like filter it to

| A_id | B_id | B_p |
|------|------|-----|
| A1   |      |     |
| A2   | B1   |     |
| A2   | B2   |     |
| A3   |      |     |

Or in Typeorm, let it parse the raw data to

[
  { id: 'A1', Bs:[] },
  { 
    id: 'A2', 
    Bs:[ 
      { id: 'B1', p: undefined }, 
      { id: 'B2', p: undefined } 
    ] 
  },
  { id: 'A3', Bs:[] }
]

I know I could query all data and do another filter by typescript, but I would like to know if there's a simple way to achieve that either by SQL or Typeorm?

0 Answers
Related