MySQL join 3 table จะเลือกค่า Max(id) GROUP user_id ยังไงครับ ...............................................
Code (SQL)
const [data] = await pool.query(` SELECT upp.id as user_profile_picture_id, upp.profile_picture_url ,f.*,u.*
FROM user_profile_picture upp
RIGHT JOIN friend_request f
ON upp.user_id = f.request_user_id
INNER JOIN user u
ON f.request_user_id = u.id
LEFT JOIN user_profile up
ON u.id = up.user_id
WHERE f.response_user_id = ? `
,[userId])
Code [{"user_profile_picture_id":32,"profile_picture_url":"http://localhost:3001/user_profile/44addd7f-2fec-4cdc-a97e-857d17c321d5101120191023386891.jpg","id":49,"request_user_id":49,"response_user_id":51,"email":"2@2","username":"sam.nob2","password":"$2b$10$4JvfY05u7uhP9qq3tiIhZ.FAw.wYIlu9KL.GShWVU6D4qO4OzLKnq","firstname":"sam","lastname":"nob2","birthday":"22/22/22","gender":"male"},
{"user_profile_picture_id":33,"profile_picture_url":"http://localhost:3001/user_profile/a980dbdf-a5b6-4a41-9bad-db4651f302ed10112019102344458images.jpg","id":49,"request_user_id":49,"response_user_id":51,"email":"2@2","username":"sam.nob2","password":"$2b$10$4JvfY05u7uhP9qq3tiIhZ.FAw.wYIlu9KL.GShWVU6D4qO4OzLKnq","firstname":"sam","lastname":"nob2","birthday":"22/22/22","gender":"male"}]
Code {"user_profile_picture_id":33,"profile_picture_url":"http://localhost:3001/user_profile/a980dbdf-a5b6-4a41-9bad-db4651f302ed10112019102344458images.jpg","id":49,"request_user_id":49,"response_user_id":51,"email":"2@2","username":"sam.nob2","password":"$2b$10$4JvfY05u7uhP9qq3tiIhZ.FAw.wYIlu9KL.GShWVU6D4qO4OzLKnq","firstname":"sam","lastname":"nob2","birthday":"22/22/22","gender":"male"}]
Tag PHP, MySQL
ประวัติการแก้ไข
Date :
2019-12-10 11:32:40By :
nobparadView :
1496Reply :
7
ขอปิดกระทู้ ได้แล้วครับ Code (SQL)
const [data2] = await pool.query(` SELECT u.username, u.firstname, u.lastname, upp.profile_picture_url
FROM user_profile_picture upp
RIGHT JOIN friend_request f
ON upp.user_id = f.request_user_id
INNER JOIN user u
ON f.request_user_id = u.id
WHERE
(
upp.id IN (SELECT MAX(id) AS id FROM user_profile_picture
GROUP BY user_id)
OR upp.id IS NULL
)
AND f.response_user_id = ?
LIMIT ?,? `
,[userId, (rowStart - 1), perPage[0]])
ประวัติการแก้ไข
Date :
2019-12-10 12:14:34By :
nobparad
ตอนแรกมึนตึ๊บ await pool.query();
Date :
2019-12-10 19:34:32By :
ผ่านมา
ตอนแรกผมมองไม่ออกว่า Node.js มันจะจัดการ RDBMS อย่างไร?ขอบคุณครับ Code (JavaScript)
const { Pool } = require('pg');
let config;
if (process.env.NODE_ENV === 'production' || process.env.NODE_ENV === 'staging') {
config = { connectionString: process.env.DATABASE_URL, ssl: true };
} else {
config = {
host: 'localhost',
user: 'myuser',
database: 'mydatabase',
};
}
const pool = new Pool(config);
async function getAllUsers() {
let response;
try {
response = await pool.query('select * FROM users');
} catch (error) {
throw error;
}
return response.rows;
}
async function getAllUsers() {
let response;
try {
response = await pool.query('select * FROM notable');
} catch (error) {
throw error;
}
return response.rows;
}
Date :
2019-12-10 19:45:22By :
ผ่านมา
@นพรัตน์ แฝงแก้ว จาก #NO1 ถ้าคุณจะกรุณา ผมรบกวนช่วยรันคำสั่งนี้หน่อยได้ไหมครับCode (SQL)
IF EXISTS(SELECT * FROM user_profile_picture)
THEN
DROP TABLE user_profile_picture;
ELSE
SELECT 1
Date :
2019-12-10 19:57:40By :
ผ่านมา
Server 05
Load balance : Server 05
