 |
[PHP]
MySQL join 3 table จะเลือกค่า Max(id) GROUP user_id ยังไงครับ ............................................... |
| |
 |
|
|
 |
 |
|
Code (SQL)
code sql
01.const [data] = await pool.query(` SELECT upp.id as user_profile_picture_id, upp.profile_picture_url ,f.*,u.*
02. FROM user_profile_picture upp
03. RIGHT JOIN friend_request f
04. ON upp.user_id = f.request_user_id
05. INNER JOIN user u
06. ON f.request_user_id = u.id
07. LEFT JOIN user_profile up
08. ON u.id = up.user_id
09. WHERE f.response_user_id = ? `
10. ,[userId])
*Output
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"}]
OUTPUT ที่ผมต้องการ
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"}]
ผมต้องการแค่ข้อมูล user_profile_picture_id ที่มีค่ามากสุด ของแต่ละ user_id เพื่อแสดงรูปโปรไฟล์ล่าสุด ทำยังไงครับ
Tag : PHP, MySQL
|
ประวัติการแก้ไข
2019-12-10 11:33:27
|
 |
 |
 |
 |
| Date :
2019-12-10 11:32:40 |
By :
nobparad |
View :
1549 |
Reply :
7 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
ขอปิดกระทู้ ได้แล้วครับ
Code (SQL)
01.const [data2] = await pool.query(` SELECT u.username, u.firstname, u.lastname, upp.profile_picture_url
02. FROM user_profile_picture upp
03. RIGHT JOIN friend_request f
04. ON upp.user_id = f.request_user_id
05. INNER JOIN user u
06. ON f.request_user_id = u.id
07. WHERE
08. (
09. upp.id IN (SELECT MAX(id) AS id FROM user_profile_picture
10. GROUP BY user_id)
11. OR upp.id IS NULL
12. )
13. AND f.response_user_id = ?
14. LIMIT ?,? `
15. ,[userId, (rowStart - 1), perPage[0]])
|
ประวัติการแก้ไข
2019-12-11 12:51:25
2019-12-11 13:06:35
| |
|
|
| |
| Date :
2019-12-10 12:14:34 |
By :
nobparad |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตอนแรกมึนตึ๊บ await pool.query();
เริ่มจะเข้าใจแล้วว่า Node.js มันไม่ธรรมดา
|
| |
|
|
| |
| Date :
2019-12-10 19:34:32 |
By :
ผ่านมา |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตอนแรกผมมองไม่ออกว่า Node.js มันจะจัดการ RDBMS อย่างไร?
--- คำถามและคำตอบของคุณ มันช่วยผมได้มากมากมาก ครับ (ผมเป็นคนหัวไว เห็นแป๊บทะลุทะลวง)
ขอบคุณครับ
Code (JavaScript)
01.const { Pool } = require('pg');
02.
03.let config;
04.if (process.env.NODE_ENV === 'production' || process.env.NODE_ENV === 'staging') {
05. config = { connectionString: process.env.DATABASE_URL, ssl: true };
06.} else {
07. config = {
08. host: 'localhost',
09. user: 'myuser',
10. database: 'mydatabase',
11. };
12.}
13.
14.const pool = new Pool(config);
15.
16.async function getAllUsers() {
17. let response;
18. try {
19. response = await pool.query('select * FROM users');
20. } catch (error) {
21. throw error;
22. }
23. return response.rows;
24.}
25.
26.async function getAllUsers() {
27. let response;
28. try {
29. response = await pool.query('select * FROM notable');
30. } catch (error) {
31. throw error;
32. }
33. return response.rows;
34.}
|
| |
|
|
| |
| Date :
2019-12-10 19:45:22 |
By :
ผ่านมา |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
