1

I have the following schema and some data inserted into the DB.

Schema (PostgreSQL v14)

CREATE TABLE users(
    id INT NOT NULL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    is_staff BOOLEAN DEFAULT FALSE,
    is_superuser BOOLEAN DEFAULT FALSE
);
INSERT INTO users 
                (id, email, password, first_name, last_name, is_active, is_staff, is_superuser) 
            VALUES (1, '[email protected]', '23456ses', 'John', 'Idogun', true, true, true);
INSERT INTO users 
                (id, email, password, first_name, last_name, is_active, is_staff, is_superuser) 
            VALUES (2, '[email protected]', '23456ses', 'Nelson', 'Idogun', true, true, true);
-- articles table
CREATE TABLE IF NOT EXISTS articles(
    id INT NOT NULL PRIMARY KEY,
    fore_image TEXT NULL,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT NOT NULL,
    is_published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO articles (id, title, slug, content, is_published, user_id) VALUES (1, 'Upgrading from v0.4 to v0.5 of rust rocket', 'Upgrading from v0.4 to v0.5 of rust rocket', 'Upgrading from v0.4 to v0.5 of rust rocket', true, 1);
INSERT INTO articles (id, title, slug, content, is_published, user_id) VALUES (2, 'Upgrading from v0.3 to v0.4 of rust rocket', 'Upgrading from v0.3 to v0.4 of rust rocket', 'Upgrading from v0.3 to v0.4 of rust rocket', true, 1);
INSERT INTO articles (id, title, slug, content, is_published, user_id) VALUES (3, 'Upgrading from v0.2 to v0.3 of rust rocket', 'Upgrading from v0.2 to v0.3 of rust rocket', 'Upgrading from v0.2 to v0.3 of rust rocket', true, 2);
-- Tags table
CREATE TABLE IF NOT EXISTS tags(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT NULL
);
INSERT INTO tags (id, name, description)
    VALUES (1, 'dev', 'Internet dev...'), (2,
            'rust',
            'Confident and safe systems language.'
        ),(3,
            'python',
            'import this. Beautiful is better than ugly...'
        );
-- articles and tags table
CREATE TABLE IF NOT EXISTS articles_tags(
    article_id INT NOT NULL references articles(id) ON DELETE CASCADE,
    tag_id INT NOT NULL references tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

INSERT INTO articles_tags(article_id, tag_id) VALUES (1, 1), (1, 2), (2, 3), (2, 2), (2, 1), (3, 1), (3, 3);

I also have this query to get a tag and then output all articles having that tag in a list of objects. Each article should in turn have its respective authors. The query did output that but what I want is slightly different.

Query and Current Result

SELECT
    t.id AS t_id,
    t.name AS t_name,
    t.description AS t_description,
    json_agg(
    jsonb_build_object(
        'id', a.id,
        'fore_image', a.fore_image,
        'title', a.title,
        'slug', a.slug,
        'content', a.content,
        'is_published', a.is_published,
        'created_at', a.created_at,
        'updated_at', a.updated_at, 
        'author', jsonb_build_object(
          'id', u.id,
          'email', u.email,
          'first_name', u.first_name,
          'last_name', u.last_name,
          'is_active', u.is_active,
          'is_staff', u.is_staff,
          'is_superuser', u.is_superuser
        )
    )) AS articles
FROM tags AS t
JOIN articles_tags AS a_t ON a_t.tag_id = t.id
JOIN articles AS a ON a_t.article_id = a.id
JOIN users AS u ON a.user_id = u.id
GROUP BY t.id, a.id
HAVING t.id = 1;
t_id t_name t_description articles
1 dev Internet dev... [{"id":1,"slug":"Upgrading from v0.4 to v0.5 of rust rocket","title":"Upgrading from v0.4 to v0.5 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.4 to v0.5 of rust rocket","created_at":"2023-02-11T08:07:56.098667+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.098667+00:00","is_published":true}]
1 dev Internet dev... [{"id":2,"slug":"Upgrading from v0.3 to v0.4 of rust rocket","title":"Upgrading from v0.3 to v0.4 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.3 to v0.4 of rust rocket","created_at":"2023-02-11T08:07:56.10037+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.10037+00:00","is_published":true}]
1 dev Internet dev... [{"id":3,"slug":"Upgrading from v0.2 to v0.3 of rust rocket","title":"Upgrading from v0.2 to v0.3 of rust rocket","author":{"id":2,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"Nelson","is_superuser":true},"content":"Upgrading from v0.2 to v0.3 of rust rocket","created_at":"2023-02-11T08:07:56.100741+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.100741+00:00","is_published":true}]

Expected result

t_id t_name t_description articles
1 dev Internet dev... [{"id":1,"slug":"Upgrading from v0.4 to v0.5 of rust rocket","title":"Upgrading from v0.4 to v0.5 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.4 to v0.5 of rust rocket","created_at":"2023-02-11T08:07:56.098667+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.098667+00:00","is_published":true}, {"id":2,"slug":"Upgrading from v0.3 to v0.4 of rust rocket","title":"Upgrading from v0.3 to v0.4 of rust rocket","author":{"id":1,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"John","is_superuser":true},"content":"Upgrading from v0.3 to v0.4 of rust rocket","created_at":"2023-02-11T08:07:56.10037+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.10037+00:00","is_published":true}, {"id":3,"slug":"Upgrading from v0.2 to v0.3 of rust rocket","title":"Upgrading from v0.2 to v0.3 of rust rocket","author":{"id":2,"email":"[email protected]","is_staff":true,"is_active":true,"last_name":"Idogun","first_name":"Nelson","is_superuser":true},"content":"Upgrading from v0.2 to v0.3 of rust rocket","created_at":"2023-02-11T08:07:56.100741+00:00","fore_image":null,"updated_at":"2023-02-11T08:07:56.100741+00:00","is_published":true}]

How can I achieve this result with optimal performance? Any modification to the query?

View on SQL Fiddle

1 Answer 1

1

There seems no need to group by a.id. And that HAVING should probably be a WHERE, although that itself won't make a difference to the results, only the performance.

So simply change this

GROUP BY t.id, a.id
HAVING t.id = 1;

to this:

WHERE t.Id = 1
GROUP BY t.id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.