Mysql
需要有關此查詢的幫助獲取我和我所有朋友的所有狀態及其相關評論
我花了幾天時間處理這個查詢，但無法讓它按我的意願工作。這篇文章與這篇文章相關來自不同表的幾個值，我在其中很難找到解決方案，但我完全錯了並且迷失了:(。所以基本上與另一篇文章的架構相同：5 個表：default_users、default_profiles、default_status、default_comment和 default_friend。這是每個的 SQL：
default_comment
CREATE TABLE `default_comment` ( `comment_id` int(11) NOT NULL AUTO_INCREMENT, `friend_id` int(11) NOT NULL, `message` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `status_id` int(11) NOT NULL, `device` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`comment_id`) ) ENGINE=InnoDB AUTO_INCREMENT=13007 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cidefault_friend
CREATE TABLE `default_friend` ( `friend_id` int(8) NOT NULL, `user_id` int(8) NOT NULL, `is_suscriber` tinyint(1) NOT NULL DEFAULT '1', `privacy` tinyint(1) NOT NULL DEFAULT '0', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `friend_list_id` int(4) NOT NULL, `approved` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cidefault_profiles
CREATE TABLE `default_profiles` ( `id` int(9) NOT NULL AUTO_INCREMENT, `created` datetime DEFAULT NULL, `updated` datetime DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `ordering_count` int(11) DEFAULT NULL, `user_id` int(11) unsigned NOT NULL, `display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `updated_on` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci預設狀態
CREATE TABLE `default_status` ( `status_id` int(11) NOT NULL AUTO_INCREMENT, `message` text COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `privacy` tinyint(1) DEFAULT NULL, `user_id` int(11) NOT NULL, `is_reply` tinyint(1) NOT NULL DEFAULT '0', `device` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`status_id`) ) ENGINE=InnoDB AUTO_INCREMENT=13005 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci預設使用者
CREATE TABLE `default_users` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `salt` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `group_id` int(11) DEFAULT NULL, `ip_address` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, `active` int(1) DEFAULT NULL, `activation_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `created_on` int(11) NOT NULL, `last_login` int(11) NOT NULL, `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `forgotten_password_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `remember_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Registered User Information'所以我建立了這個查詢
SELECT DISTINCT u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message, f.friend_id, f.user_id, UNIX_TIMESTAMP(s.created_at) AS created_at FROM default_users u LEFT JOIN default_friend f ON ((f.friend_id = 1 OR f.user_id = 1) AND f.approved = 1) LEFT JOIN default_status s ON (u.id = s.user_id OR f.friend_id = s.user_id OR f.user_id = s.user_id) LEFT JOIN default_profiles p ON (s.user_id = p.user_id) WHERE u.id = 1 ORDER BY s.created_at DESC LIMIT 0 , 10這是結果：
+---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+ | user_id | email | display_name | first_name | last_name | status_id | message | friend_id | user_id | created_at | +---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+ | 1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier | Perez Mira | 12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w | 2 | 1 | 1345055203 | | 1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier | Perez Mira | 12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w | 1 | 3 | 1345055203 | | 1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier | Perez Mira | 12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w | 1 | 4315 | 1345055203 | | 1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier | Perez Mira | 12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w | 6380 | 1 | 1345055203 | | 1 | reynierpm@gmail.com | Demo | Demo | Demo | 10484 | ivp1e40f350bf6ifplzsd560k6e5mvgvdq91q7j2k5oldraoftbd2k2eirtdihh7fbqwq2mkohqi05d3fw0sawvqmabah979updo | 2 | 1 | 1345055064 | | 1 | reynierpm@gmail.com | User 1 | User | User | 10494 | arstvbkjrea725bea4mv1kv1qaelt669n7mequh5j6n5uzh8a3voy7w6lvebphrhkhnks9fzos46p8sqptud1mb3wysjpge653qe | 1 | 3 | 1345055064 | | 1 | reynierpm@gmail.com | user502bc8a4942d1 | user502bc8a4942d1-firstname | user502bc8a4942d1-lastname | 7796 | vmkhv266hu8s7czpy1j2m1cvi3j1i3jr35hl76fsdqdv1cacv5etylls3q6yqrm7r2nrymjcqrvtzu267huajs5g9j9t3kdz6f9l | 1 | 4315 | 1345054882 | | 1 | reynierpm@gmail.com | user502bc90a00ba4 | user502bc90a00ba4-firstname | user502bc90a00ba4-lastname | 6103 | c3mmq89ucbcjd0w1pdak0tgtioyfy5loa3z18ecmkwukusojbr6qwosbham8d132fzq1pn6ei3h5dgoabyvytyyuiqm2k09fmudy | 6380 | 1 | 1345054774 | | 1 | reynierpm@gmail.com | Demo | Demo | Demo | 3828 | hky500btaj5rq96inod22k1zpyh1uz65892mwanuloqkqgjo1h3u2f0ikery3pe3olwoqbqq08t301vqpuhirvddp3lq6w5ppoep | 2 | 1 | 1345054631 | | 1 | reynierpm@gmail.com | Reynier Perez Mira | Reynier | Perez Mira | 2364 | zfe25at726i4bsju21v3ipcpp6bhetdrqva7m4ceh0k2pcdj6equbnq89mlg892eh5eqq1l1514qir76pqq5u7qnqyai6dqnv45n | 2 | 1 | 1345054536 | +---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+ 10 rows in set (0.08 sec)但是，如果我執行此查詢來獲取我的朋友：
SELECT * FROM default_friend WHERE user_id = 1 OR friend_id = 1這是結果
+-----------+---------+--------------+---------+---------------------+----------------+----------+ | friend_id | user_id | is_suscriber | privacy | created_at | friend_list_id | approved | +-----------+---------+--------------+---------+---------------------+----------------+----------+ | 2 | 1 | 1 | 0 | 2012-08-13 13:46:11 | 0 | 1 | | 1 | 3 | 1 | 0 | 2012-08-14 14:46:11 | 0 | 1 | | 1 | 4315 | 1 | 0 | 2012-08-15 11:57:51 | 0 | 1 | | 6380 | 1 | 1 | 0 | 2012-08-15 11:58:47 | 0 | 1 | +-----------+---------+--------------+---------+---------------------+----------------+----------+ 4 rows in set (0.01 sec)另外，如果我執行這個 other 來獲取我和我所有朋友的消息：
SELECT * FROM default_status WHERE user_id = 1 OR user_id = 3 OR user_id = 4315 OR user_id = 6380我得到這個結果：
+-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+ | status_id | message | created_at | privacy | user_id | is_reply | device | +-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+ | 1 | dasdasdasdasdasd | 2012-08-13 15:15:37 | NULL | 1 | 0 | | | 3 | dasdsad344hbvnbnhjhgjhjghjhj | 2012-08-13 17:24:53 | NULL | 1 | 0 | | | 4 | dasdsad344hbvnbnhjhgjhjghjhjsdfsdfsdfsdfdsfsdfsdfsdfsdfsdfsdfsdfsdfsdfsdfsdf | 2012-08-13 17:24:53 | NULL | 3 | 0 | | | 834 | pgmafjmzicgdqu1fo0fv3sg4qipq4dm3qnubpo4gpyfz5y7q0p9kl75c18c9tsr01u92fqfoyyw12vl7zn1bht4vhnyf2b5cldrp | 2012-08-15 13:43:57 | NULL | 3 | 0 | | | 2364 | zfe25at726i4bsju21v3ipcpp6bhetdrqva7m4ceh0k2pcdj6equbnq89mlg892eh5eqq1l1514qir76pqq5u7qnqyai6dqnv45n | 2012-08-15 13:45:36 | NULL | 1 | 0 | | | 6103 | c3mmq89ucbcjd0w1pdak0tgtioyfy5loa3z18ecmkwukusojbr6qwosbham8d132fzq1pn6ei3h5dgoabyvytyyuiqm2k09fmudy | 2012-08-15 13:49:34 | NULL | 6380 | 0 | | | 7796 | vmkhv266hu8s7czpy1j2m1cvi3j1i3jr35hl76fsdqdv1cacv5etylls3q6yqrm7r2nrymjcqrvtzu267huajs5g9j9t3kdz6f9l | 2012-08-15 13:51:22 | NULL | 4315 | 0 | | | 10494 | arstvbkjrea725bea4mv1kv1qaelt669n7mequh5j6n5uzh8a3voy7w6lvebphrhkhnks9fzos46p8sqptud1mb3wysjpge653qe | 2012-08-15 13:54:24 | NULL | 3 | 0 | | | 12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w | 2012-08-15 13:56:43 | NULL | 1 | 0 | | +-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+ 9 rows in set (0.02 sec)那麼在這個解釋之後，我的第一個查詢出了什麼問題？我在第一個查詢中不存在的其他選擇中得到什麼結果？（加入的那個），有什麼幫助或建議嗎？非常重要的測試文件在我的 Dropbox https://www.dropbox.com/s/tu4rb7osppyd8un/comvivem_db.sql
好的，所以根據我們的對話，如果您確保在創建朋友的請求時您還創建了另一個方向的條目：（即 Friend_A 詢問 Friend_B，但您還需要 Friend_B 和 Friend_A 之間的關係）然後您可以執行以下查詢以獲得您需要的結果：
select distinct s.user_id, u.username, p.display_name, p.first_name, p.last_name, s.message from default_status as s join default_friend as f on f.user_id = s.user_id join default_users as u on u.id = s.user_id join default_profiles as p on p.user_id = u.id where (f.user_id = 1 or f.friend_id = 1) and f.approved = 1 order by s.created_at desc;這也將使獲取所有朋友的列表變得更容易：
select * from default_users as u join default_friends as f on f.friend_id = u.id where f.user_id = 1;只要記住在預設的friends_table中為user_id和friend_id建立一個索引，否則你會發現你的查詢可能需要一段時間：
create unique index idx_friend on default_friend(user_id,friend_id);我希望這可以幫助你。;-)