Drizzle Team•15mo ago

How to flatten select results from query with left joins (one-to-many)?

Is there a way to achieve this? Current behavior in drizzle works like this (assuming we're printing the variable "result" which is supposed to be an array of person with all their hobbies, one to many:

// NOTE: I've read a portion of the docs and it says something like //using a reduce function. I'm looking for a solution that is more // of an abstraction that works out of the box - just like how
// things are when you use a relational query (i.e., findMany)
[
  {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H1", ... }
  },
 {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H2", ... }
  },
{
    person: {
      id: "P3",
      name: "Bob Smith"
      ...
    }
    hobby: { id: "H5", ... }
  }
]

// NOTE: I've read a portion of the docs and it says something like //using a reduce function. I'm looking for a solution that is more // of an abstraction that works out of the box - just like how
// things are when you use a relational query (i.e., findMany)
[
  {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H1", ... }
  },
 {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H2", ... }
  },
{
    person: {
      id: "P3",
      name: "Bob Smith"
      ...
    }
    hobby: { id: "H5", ... }
  }
]

What I want to achieve is an array wherein these relations are flattened, such as:

[
  {
    "id": "P1",
    "name": "Jane Doe"
    "hobby": [
      { "id": "H1", ...  },
      { "id": "H2", ...  }
    ],
    ...
  },
  {
    "id": "P2",
    "name": "Bob Smith"
    "hobby": [
      { "id": "H5", ...  }
    ],
    ...
  }
]

[
  {
    "id": "P1",
    "name": "Jane Doe"
    "hobby": [
      { "id": "H1", ...  },
      { "id": "H2", ...  }
    ],
    ...
  },
  {
    "id": "P2",
    "name": "Bob Smith"
    "hobby": [
      { "id": "H5", ...  }
    ],
    ...
  }
]

7 Replies

robboten•15mo ago

I came here for just the same thing. I have managed to get it working with sql query like this:

 const articlesWithImages = await db.execute(
    sql`SELECT "Articles".*, 
    json_agg("Images".*) AS images FROM public."Articles"
    LEFT JOIN
    "Images" ON "Images".id = ANY("Articles"."imageIds")
    GROUP BY
    "Articles".id
    ORDER BY
    "Articles".name;
`
  );

 const articlesWithImages = await db.execute(
    sql`SELECT "Articles".*, 
    json_agg("Images".*) AS images FROM public."Articles"
    LEFT JOIN
    "Images" ON "Images".id = ANY("Articles"."imageIds")
    GROUP BY
    "Articles".id
    ORDER BY
    "Articles".name;
`
  );

but am stuck on how to translate it to the drizzle form since I can't find how to do ANY in drizzle. there is an inArray, but that doesn't seem to work. this is where I ended up for now. Not sure if it can be improved on by someone more familiar with drizzle than I am.

.select({
      articles,
      images: sql<string>`json_agg("Images".*) AS images`,
    })
    .from(articles)
    .leftJoin(images, sql`"Images".id = ANY("Articles"."imageIds")`)
    .groupBy(articles.id);

.select({
      articles,
      images: sql<string>`json_agg("Images".*) AS images`,
    })
    .from(articles)
    .leftJoin(images, sql`"Images".id = ANY("Articles"."imageIds")`)
    .groupBy(articles.id);

only problem with it is that I have no idea how to actually get the right schema for the images included.

await db
    .select({
      articles,
      images: sql<string>`json_agg(${images})`,
    })
    .from(articles)
    .leftJoin(images, sql`${images.id} = ANY(${articles.imageIds})`)
    .groupBy(articles.id);

await db
    .select({
      articles,
      images: sql<string>`json_agg(${images})`,
    })
    .from(articles)
    .leftJoin(images, sql`${images.id} = ANY(${articles.imageIds})`)
    .groupBy(articles.id);

gives images as strings instead of the actual image type

const articlesWithImages2: {
    articles: {
        id: number;
        name: string;
        createdAt: string;
        description: string | null;
        type: "PRODUCT" | "SERVICE" | null;
        storeId: number;
        ean: string | null;
        articleId: string | null;
        articleTagIds: number[] | null;
        ... 7 more ...;
        imageIds: number[] | null;
    };
    images: string;
}[]

const articlesWithImages2: {
    articles: {
        id: number;
        name: string;
        createdAt: string;
        description: string | null;
        type: "PRODUCT" | "SERVICE" | null;
        storeId: number;
        ean: string | null;
        articleId: string | null;
        articleTagIds: number[] | null;
        ... 7 more ...;
        imageIds: number[] | null;
    };
    images: string;
}[]

However, now I have objects with {articles:..., images:... } instead of {articles: ..., images:[]}. So no idea how to achieve the wanted result with just drizzle there are backticks in there, not sure how to escape those in discord Oh, I'm daft... I did say it should be a string myself in the sql<string>... duh...

IstoraMandiri•15mo ago

for code blocks you can encapsulate in 3 backticks, and can add format to the top

```typescript

```typescript

brynthrax•15mo ago

thanks a lot for sharing. were you now able to parse the JSON string as an actual JSON object?

robboten•15mo ago

I ended up doing this :

export type ArticleWithImages = Article & {
  images: Image[];
};
 const articlesWithImages = (await db.execute(
    sql`SELECT "Articles".*, 
    json_agg(${images}) AS images FROM public."Articles"
    LEFT JOIN
    ${images} ON ${images.id} = ANY( ${articles.imageIds})
    WHERE ${articles.categoryId} = ${categoryId}
    GROUP BY
    ${articles.id}
    ORDER BY
    ${articles.name};
`
  )) as ArticleWithImages[];

export type ArticleWithImages = Article & {
  images: Image[];
};
 const articlesWithImages = (await db.execute(
    sql`SELECT "Articles".*, 
    json_agg(${images}) AS images FROM public."Articles"
    LEFT JOIN
    ${images} ON ${images.id} = ANY( ${articles.imageIds})
    WHERE ${articles.categoryId} = ${categoryId}
    GROUP BY
    ${articles.id}
    ORDER BY
    ${articles.name};
`
  )) as ArticleWithImages[];

then I get the output like this in js: { customer:dev: id: 16684, customer:dev: name: 'Tasty Rubber Soap', customer:dev: createdAt: 2023-08-11T05:05:56.582Z, customer:dev: storeId: 21, customer:dev: EAN: null, customer:dev: articleId: null, customer:dev: articleTagIds: null, customer:dev: description: 'Andy shoes are designed to keeping in mind durability as well as trends, the most stylish range of shoes & sandals', customer:dev: isInventoryItem: false, customer:dev: isSecondHand: false, customer:dev: purchasePrice: 624, customer:dev: quantity: 26, customer:dev: salesPrice: 5369, customer:dev: type: 'PRODUCT', customer:dev: vatId: 21, customer:dev: categoryId: 656, customer:dev: imageIds: [ 167, 175, 174, 171 ], customer:dev: images: [ [Object], [Object], [Object], [Object] ] customer:dev: }, customer:dev: { customer:dev: id: 16683, customer:dev: name: 'Unbranded Rubber Towels', customer:dev: createdAt: 2023-08-11T05:05:56.582Z, customer:dev: storeId: 21, customer:dev: EAN: null, customer:dev: articleId: null, customer:dev: articleTagIds: null, customer:dev: description: 'Andy shoes are designed to keeping in mind durability as well as trends, ... where images are actually the right format too. not the "best" way, but the only way I could figure out

brynthrax•15mo ago

hmmm so you ended up writing raw SQL anyway. I hope there could be an actual workaround for this in the next releases 😀

robboten•15mo ago

yeah, me too. Tried the thing I mentioned before, but could only get images as a sibling to articles that way, not as child. Really irritating. it depends on this part: sql`SELECT "Articles".*, json_agg(${images}) AS images and I can't figure out how to do that in drizzle If you figure it out, please share here!

brynthrax•15mo ago

One thing I know is that if you're using the select method, you can achieve the parent-child format by manually doing this thing:

.select({
      articleId: articles.id,
      articleName: articles.name,
      articleDate: articles.date,
      // some other article fields here        // manually destructured
      images: sql<string>`json_agg("Images".*) AS images`,
    })

.select({
      articleId: articles.id,
      articleName: articles.name,
      articleDate: articles.date,
      // some other article fields here        // manually destructured
      images: sql<string>`json_agg("Images".*) AS images`,
    })

but if you would like to do it in a less hassle way without those manual destructuring, you can do a relational query via the findMany method. however, I'm trying to stay away from this as there are no native joins yet, they use subqueries under the hood. and for the part wherein the json is returned as a string rather than an actual native json object, I gave up trying to solve it. I was browsing through the source code and here are my findings. the relational query (findMany) I mentioned before uses the same json_agg function (and others) you mentioned earlier. and when I looked through the source code, it seems like that they call the native JSON.parse method on that JSON string result. maybe that's why findMany is able to return them as JSON objects rather than strings. for reference in source code, try searching for mapRelationalRow and sqlite-core/query-builders/query.ts so I guess with that said, there's really no library-specific workaround for now except for findMany 😕

Gaming

Programming

How to flatten select results from query with left joins (one-to-many)?