10/10/2018, 11:26

select trong 3 table trong php&mysql

hi các bạn , mình đang gặp vấn đề khó khăn cần giúp. lâu nay mình query chỉ độc nhất trong 1 table hoặc nhìu lắm là 2 table cùng lúc. nhưng bây giờ do bài toán đòi hỏi nên phải select trong 3 table ràng buộc lẫn nhau , nên cần các bạn giúp.

table structure:

PHP Code:
table user // chứa thông tin user
user_id user_name
example 
nguyen viet ngoc anh 
PHP Code:
table friendship // thông tin về quan hệ bạn bè 
user_id friend_id 
example 

PHP Code:
table genre // thông tin về sở thích cá nhân
user_id genres
i:1,i:2,i:3  ,   i:1,i:
với i:1,i:3 là mảng đã được mã hóa lại , nghĩa là cá nhân này có genre_id 1 và 3

yêu cầu là lấy các friend của 1 user có user_id = 1, các friend này có tên chứa string "a" và ccó sở thích cá nhân genre = 1

câu query của mình như vậy nhưng có vẻ ko đúng
PHP Code:
select user.user_id user.user_name 
from user 
friendship genre
where 
friendship
.user_id 1
and 
friendship.friend_id user.user_id
and 
user.user_name '%a%'
and 
friendship.friend_id genre.user_id
and 
genre.genres like '%i:1%' 
nhờ các bạn giúp mình ????
VnVision viết 13:31 ngày 10/10/2018
Thường thì quan hệ 1 - n trong relational DB ko nên thiết kế như vậy, cứ theo chuẩn mà normalize mỗi genre là một row (sau này khi phải optimize thì hẵng refactor lại sau). Nhưng giả sử làm theo cách bạn thì query sẽ là:

Code:
SELECT u.user_id, u.user_name FROM user u
LEFT JOIN friendship f ON u.user_id = f.friend_id AND f.user_id = 1
LEFT JOIN genre g ON g.user_id = u.user_id AND g.genres LIKE '%i:1%'
WHERE u.user_name LIKE '%a%'
Lâu ko dùng SQL nên ko chắc query trên đúng ko, cậu cứ chạy thử xem. Nhưng nếu work đi nữa thì với data lớn thì chưa chắc performance của query trên đã tốt, nên thử tách ra thành các query nhỏ hơn có lẽ sẽ chạy nhanh hơn nhiều.
ngoc_viet08 viết 13:37 ngày 10/10/2018
genre có information riêng và nó còn lại 1 table riêng nữa. vì ko giới hạn được số genre được chọn nên mình phải tạo thành array và lưu nó trong 1 field.
ngoc_viet08 viết 13:38 ngày 10/10/2018
có cách nào tối ưu hơn ko cậu , nếu ko phải re-design lại cấu trúc à
VnVision viết 13:41 ngày 10/10/2018
Tức là một ng có nhiều genres, một genres có thể thuộc về nhiều ng phải ko? Nếu vậy theo đúng chuẩn quan hệ n-n thì tạo thêm một table user_genre để map giữa user và genre. Table này có 2 column là user_id và genre_id, nguyên tắc cũng tương tự table friendship của c thôi, nhưng thay vì map giữa 2 user trong cùng table user, table này map giữa user và genre ở 2 table tương ứng.

Cách làm đúng chuẩn là vậy, còn cách làm tối ưu thì còn phải tuỳ vào yêu cầu cụ thể của ứng dụng.
VnVision viết 13:41 ngày 10/10/2018
Nói thêm nữa là khi kết hợp nhiều dữ liệu vào một row mà vẫn muốn có thể search được, thường người ta sẽ lưu dưới dạng csv với 2 dấu "," ở đầu và cuối. Chẳng hạn trong trường hợp của cậu thay vì lưu dưới dạng "i:1,i:2,i:3", dữ liệu nên đc lưu dưới dạng ",1,2,3,". Vì mệnh đề where này:

WHERE genres LIKE '%i:1%'

sẽ trả về cả các record có chứa i:1, i:11, i:123, hay i:154353

Nếu lưu ở dạng csv với 2 dấu "," ở đầu và cuối, câu lệnh có thể viết lại thành:

WHERE genres LIKE '%,1,%'

mà ko sợ query cả các record ko mong muốn.
Profit viết 13:38 ngày 10/10/2018
Có 3 chuẩn trong thiết kế database bạn nên xem qua trước khi thiết kế kiến trúc
haicop viết 13:33 ngày 10/10/2018
table genre // thông tin về sở thích cá nhân
user_id , genres
1 - i:1,i:2,i:3 , 2 - i:1,i:3

Theo mình bạn không nên viết kiểu này sau này xử lý rất mệt, tốt nhất chuẩn hóa tách tiếp table này ra làm 2 table. Tối kị kiểu viết này. Join nhiều bảng tí có vấn đề gì đâu.

Do bạn chưa normalize table genres nên bạn mới khó khăn trong query. Bạn tách ra rồi join lại xem query trên rất đơn giản.
ngoc_viet08 viết 13:41 ngày 10/10/2018
do yêu cầu ban đầu ít có xử lý tới genre nên mình xây dựng như vậy. giờ mới lòi thêm yêu cầu tìm kiếm kết hợp chung với genre nên nó cũng hơi nhức tí. để mình try
ngoc_viet08 viết 13:34 ngày 10/10/2018
mình thử cách left join này , rút gọn query lại tuy nhiên vẫn ko đạt được kết quả mong muốn.

ở đây mình cần làm 1 dạng như phân trang kiểu show more trên facebook. nghĩa là cứ mỗi lần click "show more" thì sẽ hiện ra 1 lượng tin mới nữa.

cái khó là mỗi lần click "show more" phải truy xuất ra đươc đủ 1 số lượng tin đã định sẵn . ví dụ mỗi lần click show more friends , thì phải show ra đủ 10 friends đạt đủ điều kiện là "friend" và có cùng genre đã chọn.

với kiểu query này và những cách thông thường với 2-3 query thì sẽ lấy được 10 friends nhưng trong 10 friend này ko đảm bảo là có genre như yêu cầu. nên kết quả trả về có thể chỉ còn ít hơn .

ai có cao kiến về vụ này giúp mình với?
Bài liên quan
0