Can you 'filter' the columns from a second leftJoinAndMapOne?

Viewed 1437

Can the columns of a second leftJoinAndMapOne() be 'filtered' so that it only returns the columns or values that I need inside the object?

Query Builder:

    const favorites = await this.favoritesRepo.createQueryBuilder('favorite')
      .where('favorite.user_id = :token', { token })
      .leftJoinAndMapOne('favorite.item', 'favorite.item_id', 'items')
      .select(['favorite.id'])
      .addSelect([
        'items.title',
        'items.description',
        'items.price',
        'items.stock',
      ])
      .leftJoinAndMapOne(
        'items.photos',
        PhotosEntity,
        'photos',
        'favorite.item_id = photos.subject_id and photos.subject_type = :item',
        {item: 'item'}
      )


    return paginate<Favorite>(favorites, options)

Output (GET):

    //...
    {
      "id": 32,
      "item": {
        "title": "Foo",
        "description": "Buzz",
        "price": 250.99,
        "stock": 10,
        "photos": {
          "id": 2,
          "createdAt": "2021-12-04T04:55:02.408Z",
          "url": "https://images.unsplash.com/photo-1633114128729-0a8dc13406b9?ixlib=rb-1.2.1&ixid=MnwxMjA3fDF8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=870&q=80",
          "width": 324,
          "height": 281,
          "size": 42,
          "entityId": 18,
          "entityType": "item",
          "redirectUrl": "https://facebook.com/"
        }
      }
    },
    //...

Expected Output:

//...
        {
      "id": 32,
      "item": {
        "title": "Foo",
        "description": "Buzz",
        "price": 250.99,
        "stock": 10,
        "photos": {
          "url": "https://images.unsplash.com/photo-1633114128729-0a8dc13406b9?ixlib=rb-1.2.1&ixid=MnwxMjA3fDF8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=870&q=80"
        }
      }
    },
//...

Any suggestions or changes that I should make to make the output as expected?

      .leftJoinAndMapOne(
        'items.image',
        PhotosEntity,
        'photos',
        'favorite.item_id = photos.subject_id and photos.subject_type = :item',
        {item: 'item'}
      )
      .addSelect('photos.url')

I tried with .select () or .addSelect () but it doesn't filter columns from photos.

1 Answers

I already resolved it

Query Builder:

    const favorites = await this.favoritesRepo.createQueryBuilder('favorite')
      .where('favorite.user_id = :token', { token })
      .leftJoinAndMapOne('favorite.item', 'favorite.item_id', 'items')
      .leftJoinAndMapOne(
        'items.photos',
        PhotosEntity,
        'photos',
        'favorite.item_id = photos.subject_id and photos.subject_type = :item',
        {item: 'item'}
      )
      .select(['favorite.id'])
      .addSelect([
        'items.title',
        'items.description',
        'items.price',
        'items.stock',
        'photos.url',
      ])
Related