michael
Explore posts from serversDTDrizzle Team
•Created by michael on 3/25/2024 in #help
Help with a complicated statement.
After learning about joins, I came up with this solution:
Not sure if there is a way to do this with only db.query instead of the reducer.
const rows = await db
.select({
id: publicLinks.id,
name: publicLinks.name,
fill: publicLinks.fill,
background: publicLinks.background,
path: publicLinks.path,
clicks: links.clicks,
slug: links.slug,
title: publicLinkEntries.title,
subtitle: publicLinkEntries.subtitle
})
.from(publicLinks)
.innerJoin(publicLinkEntries, eq(publicLinkEntries.linkId, publicLinks.id))
.innerJoin(links, eq(publicLinkEntries.slug, links.slug))
.all();
const results = rows.reduce<
{
name: string;
fill: string;
background: string;
path: string;
clicks: number;
slugs: {
slug: string;
title: string;
subtitle: string;
}[];
}[]
>((results, row) => {
let value = results[row.id];
if (value == null) {
value = {
name: row.name,
fill: row.fill,
background: row.background,
path: row.path,
clicks: 0,
slugs: []
};
results[row.id] = value;
}
value.clicks += row.clicks;
value.slugs.push({
slug: row.slug,
title: row.title,
subtitle: row.subtitle
});
return results;
}, []);
const rows = await db
.select({
id: publicLinks.id,
name: publicLinks.name,
fill: publicLinks.fill,
background: publicLinks.background,
path: publicLinks.path,
clicks: links.clicks,
slug: links.slug,
title: publicLinkEntries.title,
subtitle: publicLinkEntries.subtitle
})
.from(publicLinks)
.innerJoin(publicLinkEntries, eq(publicLinkEntries.linkId, publicLinks.id))
.innerJoin(links, eq(publicLinkEntries.slug, links.slug))
.all();
const results = rows.reduce<
{
name: string;
fill: string;
background: string;
path: string;
clicks: number;
slugs: {
slug: string;
title: string;
subtitle: string;
}[];
}[]
>((results, row) => {
let value = results[row.id];
if (value == null) {
value = {
name: row.name,
fill: row.fill,
background: row.background,
path: row.path,
clicks: 0,
slugs: []
};
results[row.id] = value;
}
value.clicks += row.clicks;
value.slugs.push({
slug: row.slug,
title: row.title,
subtitle: row.subtitle
});
return results;
}, []);
2 replies