'How i don't know how to display data joined from three tables
Hello everyone I'm making react, postgres, node project but I have trouble with displaying data from three tables first table is users table second is movies table and third is a review table. My tables look like this :
CREATE TABLE users(
user_id SERIAL PRIMARY KEY,
user_name VARCHAR(255) NOT NULL,
user_email VARCHAR(255) NOT NULL,
user_password VARCHAR(255) NOT NULL
);
CREATE TABLE movies(
movie_id SERIAL PRIMARY KEY,
movie_name VARCHAR(255) NOT NULL,
movie_image VARCHAR(255) NOT NULL,
review_id INT UNIQUE
REFERENCES review(review_id)
ON DELETE CASCADE,
user_id INT
REFERENCES users(user_id)
ON DELETE CASCADE
);
CREATE TABLE review(
review_id SERIAL PRIMARY KEY,
movie_review VARCHAR(255) NOT NULL,
user_id INT
REFERENCES users(user_id)
ON DELETE CASCADE
);
And this query when runned in cmd returns me movies as I want:
SELECT movie_review, movie_name, movies.movie_image, user_name FROM review, movies
NATURAL JOIN users
WHERE review.review_id = movies.review_id
AND movies.user_id = 1;
And when i run similar query in node file i dont get anything back:
router.get("/movies", authorization, async (req, res) => {
try {
const movies = await pool.query(
"SELECT movie_review, movie_name, movies.movie_image, user_name FROM review, movies NATURAL JOIN users WHERE review.review_id = movies.review_id AND movies.user_id = $1"
[req.user]
);
console.log(req.user)
res.json(movies.rows);
} catch (error) {
console.error(error.message)
}
})
At first I made this project with review inside movie table (so 2 tables only), but i need to make this project with three tables it's an internship project and I'm new to node and postgres. Hope anyone can help.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
