Unable to delete a postgreSQL row that has a LEFT JOIN attached

Question

I have created a database using PostgreSQL. I have used LEFT JOIN to join a comments table to a reviews table. The frontend is created using React. I can only delete a review if it has 0 comments, if there are any comments on the review when I try to delete it a 500 internal server error throws. I was sure the LEFT JOIN would delete a review with or without comments, so not sure why this isn't working?

Any suggestions are appreciated, thank you.

https://nc-games-kirsty-richmond.netlify.app

// seed.js file

// Create Reviews Table //

const seed = async (data) => {
  const { categoryData, commentData, reviewData, userData } = data;

  await db.query(`DROP TABLE IF EXISTS comments, reviews, users, categories;`);


  await db.query(`
      CREATE TABLE reviews (
        review_id SERIAL PRIMARY KEY,
        title VARCHAR(75) NOT NULL,
        designer VARCHAR(55) NOT NULL,
        owner VARCHAR(180) REFERENCES users(username),
        review_img_url VARCHAR(500) DEFAULT 
            'https://images.pexels.com/photos/163064/play-stone-network-networked-interactive-163064.jpeg',
        review_body VARCHAR(1000) NOT NULL,
        category VARCHAR(75) REFERENCES categories(slug),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        votes INT DEFAULT 0
        );
      `);

  // Create Comments Table //

  await db.query(`
      CREATE TABLE comments (
        comment_id SERIAL PRIMARY KEY,
        body VARCHAR(1000) NOT NULL,
        votes INT DEFAULT 0,
        author VARCHAR(75) REFERENCES users(username) NOT NULL,
        review_id INT REFERENCES reviews(review_id) ON DELETE CASCADE,
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
      `);

// review-models.js

exports.selectReviewById = async (review_id) => {
  const review = await db.query(
    `SELECT reviews.*, COUNT(comment_id)
    AS comment_count
    FROM reviews
    LEFT JOIN comments
    ON comments.review_id = reviews.review_id
    WHERE reviews.review_id = $1
    GROUP BY reviews.review_id;`,
    [review_id]
  );
  return review.rows[0];
};

exports.removeReview = async (review_id) => {
  removeComment();

  const review = await db.query(
    `DELETE FROM reviews
      WHERE review_id = $1;`,
    [review_id]
  );
  return review.rows[0];
};

Errors in console:

DELETE http://be-nc-games-app.herokuapp.com/api/reviews/114 500 (Internal Server Error)
dispatchXhrRequest @ xhr.js:210
xhrAdapter @ xhr.js:15
dispatchRequest @ dispatchRequest.js:58
request @ Axios.js:112
Axios. @ Axios.js:136
wrap @ bind.js:9
deleteReview @ api.js:116
handleDelete @ ReviewCard.jsx:55
onClick @ ReviewCard.jsx:77
callCallback @ react-dom.development.js:3945
invokeGuardedCallbackDev @ react-dom.development.js:3994
invokeGuardedCallback @ react-dom.development.js:4056
invokeGuardedCallbackAndCatchFirstError @ react-dom.development.js:4070
executeDispatch @ react-dom.development.js:8243
processDispatchQueueItemsInOrder @ react-dom.development.js:8275
processDispatchQueue @ react-dom.development.js:8288
dispatchEventsForPlugins @ react-dom.development.js:8299
(anonymous) @ react-dom.development.js:8508
batchedEventUpdates$1 @ react-dom.development.js:22396
batchedEventUpdates @ react-dom.development.js:3745
dispatchEventForPluginEventSystem @ react-dom.development.js:8507
attemptToDispatchEvent @ react-dom.development.js:6005
dispatchEvent @ react-dom.development.js:5924
unstable_runWithPriority @ scheduler.development.js:468
runWithPriority$1 @ react-dom.development.js:11276
discreteUpdates$1 @ react-dom.development.js:22413
discreteUpdates @ react-dom.development.js:3756
dispatchDiscreteEvent @ react-dom.development.js:5889

Uncaught (in promise) Error: Request failed with status code 500
    at createError (createError.js:16:1)
    at settle (settle.js:17:1)
    at XMLHttpRequest.onloadend (xhr.js:66:1)
createError @ createError.js:16
settle @ settle.js:17
onloadend @ xhr.js:66
Promise.then (async)
handleDelete @ ReviewCard.jsx:55
onClick @ ReviewCard.jsx:77
callCallback @ react-dom.development.js:3945
invokeGuardedCallbackDev @ react-dom.development.js:3994
invokeGuardedCallback @ react-dom.development.js:4056
invokeGuardedCallbackAndCatchFirstError @ react-dom.development.js:4070
executeDispatch @ react-dom.development.js:8243
processDispatchQueueItemsInOrder @ react-dom.development.js:8275
processDispatchQueue @ react-dom.development.js:8288
dispatchEventsForPlugins @ react-dom.development.js:8299
(anonymous) @ react-dom.development.js:8508
batchedEventUpdates$1 @ react-dom.development.js:22396
batchedEventUpdates @ react-dom.development.js:3745
dispatchEventForPluginEventSystem @ react-dom.development.js:8507
attemptToDispatchEvent @ react-dom.development.js:6005
dispatchEvent @ react-dom.development.js:5924
unstable_runWithPriority @ scheduler.development.js:468
runWithPriority$1 @ react-dom.development.js:11276
discreteUpdates$1 @ react-dom.development.js:22413
discreteUpdates @ react-dom.development.js:3756
dispatchDiscreteEvent @ react-dom.development.js:5889

user_17296084 · Answer

If you can delete only records that are not commented.

delete from reviews where id in (
    select a.id  
    from reviews rv 
    left join comments cm on rv.id = cm.rev_id 
    where cm.id is null
)

Left join will be select all records. But if reviews are not commented these records showed as NULL. So adding the CM.ID IS NULL condition will filter only no comments records. After then using the subquery we can delete needed records.

Unable to delete a postgreSQL row that has a LEFT JOIN attached

1 Answers