0
votes

I have a little problem figuring out how to get data from a linked table using queryBuilder

Currently i have this code:

const users = await databaseConntection
      .getRepository(User)
      .createQueryBuilder("user")
      .select(["images.author_id", "user.id", "user.username"])
      .leftJoinAndSelect(
        (queryBuilder) =>
          queryBuilder
            .select(["title", "updated_at", "author_id"])
            .from(Image, "image")
            .orderBy({ updated_at: "ASC" })
            .take(5),
        "images",
        "images.author_id = user.id"
      )
      .andWhere("username LIKE :profile")
      .setParameters({
        profile: `%${profile}%`,
      })
      .groupBy("images.author_id")
      .take(limit)
      .skip(page * limit - limit)
      .getRawMany();

Current result:

column "user.id" must appear in the GROUP BY clause or be used in an aggregate function

Expected result:

[
   {
     "id": 1,
     "username": "some username",
     "images": [
        {
           "title": "some image title",
           "updated_at": "2021-05-21T03:49:44.299Z",
           "author_id": 1
        }
        ...rest 4 images
     ]
   }
   ...rest users
]

How i can achieve this result using query builder?

Thanks for any helping.

1

1 Answers

0
votes

I don't know what is wrong with my code, but i was migrate to prisma orm, and he work like as expected.

Here the code using prisma orm, and this return the expected result (without ids):

const users = await prisma.user.findMany({
      where: {
        username: {
          contains: profile as string,
        },
      },
      select: {
        username: true,
        images: {
          select: {
            title: true,
            updated_at: true,
          },
          orderBy: {
            updated_at: "asc",
          },
          take: 5,
        },
      },
      take: limit,
      skip: page * limit - limit,
    });