0

I have got the schema below and some queries as well:

Schema (PostgreSQL v15)

CREATE TABLE IF NOT EXISTS users(
    id SERIAL 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,
    thumbnail TEXT NULL,
    date_joined TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add up migration script here
CREATE DOMAIN phone AS TEXT CHECK(
    octet_length(VALUE) BETWEEN 1
    /*+*/
    + 8 AND 1
    /*+*/
    + 15 + 3
    AND VALUE ~ '^\+\d+$'
);
-- User details table (One-to-one relationship)
CREATE TABLE users_detail (
    id SERIAL PRIMARY KEY,
    user_id SERIAL NOT NULL UNIQUE,
    job_title TEXT NOT NULL,
    summary TEXT NULL,
    summary_raw TEXT NULL,
    highest_qualification CHAR (1) NULL,
    phone_number phone NULL,
    location TEXT NULL,
    birth_date DATE NULL,
    github_link TEXT NULL,
    twitter_link TEXT NULL,
    youtube_link TEXT NULL,
    linkedin_link TEXT NULL,
    instagram_link TEXT NULL,
    facebook_link TEXT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS users_detail_id_user_id ON users_detail (id, user_id);
-- User education table
CREATE TABLE users_education (
    id SERIAL PRIMARY KEY,
    user_id SERIAL REFERENCES users(id) ON DELETE CASCADE,
    school_name TEXT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NULL,
    description TEXT NULL,
    description_raw TEXT NULL
);
CREATE INDEX IF NOT EXISTS users_education_id_user_id ON users_education (id, user_id);
-- User experience table
CREATE TABLE users_experience (
    id SERIAL PRIMARY KEY,
    user_id SERIAL REFERENCES users(id) ON DELETE CASCADE,
    company_name TEXT NOT NULL,
    job_title TEXT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NULL,
    description TEXT NULL,
    description_raw TEXT NULL
);
CREATE INDEX IF NOT EXISTS users_experience_id_user_id ON users_experience (id, user_id);
-- User publications table
CREATE TABLE users_publication (
    id SERIAL PRIMARY KEY,
    user_id SERIAL REFERENCES users(id) ON DELETE CASCADE,
    citation TEXT NOT NULL,
    -- citation_raw TEXT NOT NULL,
    url TEXT NULL
);
CREATE INDEX IF NOT EXISTS users_publication_id_user_id ON users_publication (id, user_id);
-- User awards table
CREATE TABLE users_award (
    id SERIAL PRIMARY KEY,
    user_id SERIAL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    award_date DATE NULL
);
CREATE INDEX IF NOT EXISTS users_award_id_user_id ON users_award (id, user_id);

Query #1

INSERT INTO users (email, password, first_name, last_name) VALUES ('[email protected]', 'john', 'John', 'Idogun');

INSERT INTO users_detail (user_id, job_title) VALUES (1, 'Software Engineer');

INSERT INTO users_education (user_id, school_name, from_date) VALUES (1, 'FUTA', '2021-10-20'), (1, 'SBU', '2023-10-20');

INSERT INTO users_experience (user_id, company_name, job_title, from_date) VALUES (1, 'FUTA', 'Undergraduate','2021-10-20'), (1, 'SBU', 'PhD student','2023-10-20');

INSERT INTO users_publication (user_id, citation) VALUES (1, 'FUTA'), (1, 'SBU');

INSERT INTO users_award (user_id, title) VALUES (1, 'FUTA'), (1, 'SBU');

Then, I have this query that uses LATERAL subqueries to fetch related table data. This was to eradicate duplicates which were introduced by LEFT JOINs. The issue is if only the users table is populated while others are empty, the query returns nothing. How can I return the user data even if all other tables are empty? Can the query below be optimized for better performance?


Query #2

SELECT 
  json_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, 
    'date_joined', u.date_joined
  ) AS basic,
  user_add.additional AS additional,
  user_edu.educations AS educations,
  user_exp.experiences AS experiences,
  user_pub.publications AS publications,
  user_aw.awards AS awards
FROM 
  users u CROSS 
  JOIN LATERAL (
    SELECT 
      row_to_json(users_detail) AS additional 
    FROM 
      users_detail 
    WHERE 
      users_detail.user_id = u.id
  ) user_add CROSS 
  JOIN LATERAL (
    SELECT 
      json_agg(users_education ORDER BY users_education.from_date DESC) AS educations 
    FROM 
      users_education 
    WHERE 
      users_education.user_id = u.id
  ) user_edu CROSS 
  JOIN LATERAL (
    SELECT 
      json_agg(users_experience ORDER BY users_experience.from_date DESC) AS experiences 
    FROM 
      users_experience 
    WHERE 
      users_experience.user_id = u.id
  ) user_exp CROSS 
  JOIN LATERAL (
    SELECT 
      json_agg(users_publication) AS publications 
    FROM 
      users_publication 
    WHERE 
      users_publication.user_id = u.id
  ) user_pub CROSS 
  JOIN LATERAL (
    SELECT 
      json_agg(users_award ORDER BY users_award.award_date DESC) AS awards 
    FROM 
      users_award 
    WHERE 
      users_award.user_id = u.id
  ) user_aw 
WHERE 
  u.id = 1;

View on DB Fiddle

5
  • Please consider reading this advice
    – mustaccio
    Commented Apr 4, 2023 at 19:40
  • 1
    Assuming your tables are indexed correctly I can't see that much improvement could be made. Do you have a specific performance concern? Commented Apr 4, 2023 at 21:29
  • @Charlieface I included exactly how the tables are indexed.
    – Sirjon
    Commented Apr 4, 2023 at 21:34
  • If all foreign keys to users are indexed (too lazy to check), nothing much can go wrong with this query. It would perform slightly better to use a single table for users and users_detail, but perhaps you have a good reason for such a data model. Commented Apr 5, 2023 at 1:28
  • I have edited the question.
    – Sirjon
    Commented Apr 5, 2023 at 8:17

1 Answer 1

1

Use a left join lateral. The would-be-join-condition is already in the WHERE clause of the lateral query, so the ON clause could just be a dummy:

LEFT JOIN LATERAL (SELECT ...) user_aw ON TRUE

Now if you also want to optimize, that would be a different question. Post it as a different question, and include the necessary info.

4
  • What would be the point of changing a CROSS JOIN LATERAL to LEFT JOIN LATERAL ... ON true? Doesn't seem like it would make much of an improvement.
    – Andriy M
    Commented Apr 5, 2023 at 15:03
  • @AndriyM the point would be "How can I return the user data even if all other tables are empty"
    – jjanes
    Commented Apr 5, 2023 at 16:47
  • Good point, although you only need to replace the first CROSS JOIN LATERAL like that. The others are using aggregation without GROUP BY, which means they are expected to return exactly one row (albeit containing a null). The first one is not using aggregation, which I missed. You do need an outer join for it if the detail table has no matching row.
    – Andriy M
    Commented Apr 5, 2023 at 18:51
  • 1
    @AndriyM True, only really necessary on the user_add. But I would tend to do it for all of them, as that makes the intention clearer. Otherwise if someone decides to rewrite one of them into ORDER BY ... LIMIT 1 rather than the aggregate, they may end up with a surprise.
    – jjanes
    Commented Apr 5, 2023 at 19:40

Your Answer

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

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