michael
michael
Explore posts from servers
DTDrizzle Team
Created by michael on 3/25/2024 in #help
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:
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()
});
I would like a statement to return the data in this format:
[
{
"name": "...",
"fill": "...",
"background": "...",
"path": "...",
"clicks": 5,
"links": [
{
"slug": "...",
"title": "...",
"subtitle": "..."
}
]
}
]
[
{
"name": "...",
"fill": "...",
"background": "...",
"path": "...",
"clicks": 5,
"links": [
{
"slug": "...",
"title": "...",
"subtitle": "..."
}
]
}
]
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.
2 replies