node.js - 打字机。为什么 createQueryBuilder leftJoinAndSelect 有条件和无条件但在哪里有不同的结果？

Question

带有示例项目的回购：https ://github.com/Fblfbl/backend-movies

Nest.js 和 typeorm (postgreSQL) 上的项目

有3个实体：

1. 电影
2. 用户
3. UserDetails（用户最喜欢/观看的电影）

仅在用户将电影添加到收藏夹/观看后才创建电影（和用户）的用户详细信息我想为具有用户详细信息的用户获取所有电影，而没有它则通过左连接。

它仅在我使用此存储库方法时才有效：

async findAllByUser(id: number) {
    return this.createQueryBuilder('movie')
      .leftJoinAndSelect(
        'movie.userDetails',
        'userDetails',
        `movie.id = userDetails.movieId AND userDetails.userId = ${id}`,
      )
      .getMany();
  }

这是示例结果：

[
    {
        "id": 1,
        "title": "test1",
        "genre": "test1",
        "userDetails": {
            "id": 1,
            "isFavorite": false,
            "isWatched": true,
            "isInWatchlist": false,
            "watchingDate": "2021-07-25T19:29:18.510Z",
            "userId": 1,
            "movieId": 1
        }
    },
    {
        "id": 2,
        "title": "test2",
        "genre": "test2",
        "userDetails": {
            "id": 2,
            "isFavorite": true,
            "isWatched": true,
            "isInWatchlist": false,
            "watchingDate": "2021-07-26T13:08:21.533Z",
            "userId": 1,
            "movieId": 2
        }
    },
    {
        "id": 3,
        "title": "test3",
        "genre": "test3",
        "userDetails": {
            "id": 3,
            "isFavorite": true,
            "isWatched": true,
            "isInWatchlist": true,
            "watchingDate": "2021-07-26T13:09:11.852Z",
            "userId": 1,
            "movieId": 3
        }
    },
    {
        "id": 4,
        "title": "test4",
        "genre": "test4",
        "userDetails": null
    }
]

但是，如果我使用下两种方法之一而不是前一种方法：

  async findAllByUser(id: number) {
    return this.createQueryBuilder('movie')
      .leftJoinAndSelect('movie.userDetails', 'userDetails')
      .where(`movie.id = userDetails.movieId`)
      .andWhere('userDetails.userId = :userId', { userId: id })
      .getMany();
  }

async findAllByUser(id: number) {
    return this.find({
      relations: ['userDetails'],
      join: {
        alias: 'movie',
        leftJoinAndSelect: { userDetails: 'movie.userDetails' },
      },
      where: (qb) => {
        qb.where('userDetails.userId = :userId', { userId: id });
      },
    });
  }

我明白了（没有 id 为 4 且没有用户的 userDetails 的电影）：

[
    {
        "id": 1,
        "title": "test1",
        "genre": "test1",
        "userDetails": {
            "id": 1,
            "isFavorite": false,
            "isWatched": true,
            "isInWatchlist": false,
            "watchingDate": "2021-07-25T19:29:18.510Z",
            "userId": 1,
            "movieId": 1
        }
    },
    {
        "id": 2,
        "title": "test2",
        "genre": "test2",
        "userDetails": {
            "id": 2,
            "isFavorite": true,
            "isWatched": true,
            "isInWatchlist": false,
            "watchingDate": "2021-07-26T13:08:21.533Z",
            "userId": 1,
            "movieId": 2
        }
    },
    {
        "id": 3,
        "title": "test3",
        "genre": "test3",
        "userDetails": {
            "id": 3,
            "isFavorite": true,
            "isWatched": true,
            "isInWatchlist": true,
            "watchingDate": "2021-07-26T13:09:11.852Z",
            "userId": 1,
            "movieId": 3
        }
    }
]

所有方法都使用左连接，但为什么结果不一样？你能解释一下吗

标签： node.jspostgresqlormtypeorm