michael Posts - Answer Overflow

michael

Explore posts from servers

•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

Gaming

Programming