robboten
robboten
PostsComments
DTDrizzle Team
Created by robboten on 8/11/2023 in #help
explain relationName please
Am trying to understand how to use with, so far some progress but not fully there. Especially with many to many I get the values from the junction table instead of the one I want to connect with on the other side. But will look thru your examples. Thank you again!
11 replies
DTDrizzle Team
Created by robboten on 8/11/2023 in #help
explain relationName please
haha, thx!
11 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
If you figure it out, please share here!
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
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
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
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.
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
not the "best" way, but the only way I could figure out
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
where images are actually the right format too.
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
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, ...
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
Oh, I'm daft... I did say it should be a string myself in the sql<string>... duh...
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
there are backticks in there, not sure how to escape those in discord
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
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
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
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);
18 replies
DTDrizzle Team
Created by brynthrax on 8/11/2023 in #help
How to flatten select results from query with left joins (one-to-many)?
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.
18 replies