Help with a complicated statement.
This is my first time using SQL, I would like help with how I should write the statement to get my data in the desired format. For example I have this schema:
I would like a statement to return the data in this format:
The most important part, where "clicks" is the sum of clicks from each slug from each entry of each public link. If that makes sense.
export const links = sqliteTable('links', {
slug: text('slug').primaryKey(),
url: text('url').notNull(),
clicks: integer('clicks').notNull().default(0)
});
export const publicLinks = sqliteTable('public_links', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
fill: text('fill').notNull(),
background: text('background').notNull(),
path: text('path').notNull()
});
export const publicLinkEntries = sqliteTable('public_link_entries', {
id: integer('id').primaryKey(),
linkId: integer('link_id')
.notNull()
.references(() => publicLinks.id),
slug: text('slug')
.notNull()
.references(() => links.slug),
title: text('title').notNull(),
subtitle: text('subtitle').notNull()
});
export const links = sqliteTable('links', {
slug: text('slug').primaryKey(),
url: text('url').notNull(),
clicks: integer('clicks').notNull().default(0)
});
export const publicLinks = sqliteTable('public_links', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
fill: text('fill').notNull(),
background: text('background').notNull(),
path: text('path').notNull()
});
export const publicLinkEntries = sqliteTable('public_link_entries', {
id: integer('id').primaryKey(),
linkId: integer('link_id')
.notNull()
.references(() => publicLinks.id),
slug: text('slug')
.notNull()
.references(() => links.slug),
title: text('title').notNull(),
subtitle: text('subtitle').notNull()
});
[
{
"name": "...",
"fill": "...",
"background": "...",
"path": "...",
"clicks": 5,
"links": [
{
"slug": "...",
"title": "...",
"subtitle": "..."
}
]
}
]
[
{
"name": "...",
"fill": "...",
"background": "...",
"path": "...",
"clicks": 5,
"links": [
{
"slug": "...",
"title": "...",
"subtitle": "..."
}
]
}
]
1 Reply
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;
}, []);