How to join one to many relationship using db.select api and get nested array in result

[shellking4]

So let's suppose I have tables users and posts where a user owns many posts
How to retrieve a user with his posts using db.select and get back a result like
[
{ userId: "sjvhbdsh", posts: [ {"postId": "sdbdjvbsjbv"} ] }
]
And this without any js transformatio, just with specifying fields in the select method

[sbscan]

I could not succeed and plan to change from mariadb to mysql just to be able to user query syntax to get this nested object. It's very convenient for JS devs.

[hyunbinseo]

I don't think you can achieve it with the .select() API, unless you use raw SQL.

Even the relational API uses json_array under the hood.

// { id: number; organization: { id: number } }[];
await db.query.studentTable.findMany({
  columns: { id: true },
  with: { organization: { columns: { id: true } } },
});

db.query.studentTable.findMany({
 // ...
}).toSQL().sql

select
  "id",
  (
    select
      json_array ("id") as "data"
    from
      (
        select
          *
        from
          "organization" "studentTable_organization"
        where
          "studentTable_organization"."id" = "studentTable"."organization_id"
        limit
          ?
      ) "studentTable_organization"
  ) as "organization"
from
  "student" "studentTable"