CODEWITHSUNDEEP
mysqlnode.jsmariadbtypeorm
Heartbit
Heartbit

Reputation: 1836

How to get result from multiple tables with `leftJoin` in typeorm?

I have three tables:

1. ViewTable:

id   type    created_at    status  action_id
-----------------------------------------
1    page_1  2020-01-01    1       2

2. ItemTable:

id   view_id   image       action_id
-------------------------------------
1    1         thumb.png   1

3. ActionTable:

id    message
------------------------------------
1     this is a test
2     this an action for header view

And the result that I am expecting is:

{
    "status": 1,
    "action": {
         "message": "this an action for header view"
     },
     "items": [
         {
           "id": 1,
           "description": "Comments",
           "image": "comment.png",
           "status": 1,
           "action": {
                 message: "this is test"
              }
          }
      ]
}

Also, I have created all Entities with required relations by one-to-one and one-to-many decorators. So how can I get the result with this.createQueryBuilder('view') inside my ViewRepository?

Upvotes: 0

Views: 2346

Answers (2)

Heartbit
Heartbit

Reputation: 1836

I can get all required columns now by this way:

@EntityRepository(View)
export class ViewRepository extends Repository<View> {
    async getView() {
        return await this.createQueryBuilder('view')
            .leftJoinAndSelect("view.action", "action")
            .leftJoinAndSelect("view.items", "item")
            .leftJoinAndSelect("item.action", "itemAction")
            .select([
                "view.type",
                "action.message",
                'itemAction.message',           
                'item.image',
                'item.id'
            ])
            .getOne()
    }
}

Upvotes: 2

Art Olshansky
Art Olshansky

Reputation: 3356

this.viewRepository.createQueryBuilder('view')
  .leftJoin('view.items', 'item') // 'view.items' will work if you describe 'items' property in ViewEntity as a relation
  .leftJoin('view.action', 'viewAction')
  .leftJoin('item.action', 'itemAction') // the same as above, but for ItemEntity
  .select(['view.status', 'viewAction.message', 'item.id', 'item.description', 'item.image', 'item.status', 'itemAction.message']) // probably you needed to select ids from rest tables too, for correct joining 
  .where(<here is your 'where' condition>)
  .getOne() // or .getMany() if you wanna to get multiple results

upd: not sure that it would work inside repository

Upvotes: 1

Related Questions