Mysql – Need some help with this query get all status and their associated comments of me and all the friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was totally wrong and lost :(. So basically is the same schema as the other post: 5 tables: default_users, default_profiles, default_status, default_comment and default_friend. This are the SQL for each:

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_ci

default_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_ci

default_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

default_status

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

default_users

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'

So I build this query

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

And this is the result:

+---------+---------------------+--------------------+-----------------------------+----------------------------+-----------+------------------------------------------------------------------------------------------------------+-----------+---------+------------+
| 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)

But if I run this query to get my friends:

SELECT 
    *
FROM
    default_friend
WHERE
    user_id = 1 OR friend_id = 1

This is the result

+-----------+---------+--------------+---------+---------------------+----------------+----------+
| 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)

Also if I run this other to get messages from me and all my friends:

SELECT 
    *
FROM
    default_status
WHERE
    user_id = 1 OR user_id = 3 OR user_id = 4315 OR user_id = 6380

I get this results:

+-----------+------------------------------------------------------------------------------------------------------+---------------------+---------+---------+----------+--------+
| 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)

So after this explanation what's wrong in my first query? What I get results in other select that aren't present in the first query? (the one with JOIN), any help or advice? Very important the file for testing is here at my Dropbox https://www.dropbox.com/s/tu4rb7osppyd8un/comvivem_db.sql

Best Answer

Ok, so based on our conversation if you make sure that when a friend's request is created you also create an entry in the other direction: (i.e. Friend_A asks Friend_B, but you also need a relationship between Friend_B and Friend_A) then you can run the following query to obtain the results that you need:

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;

This will also make it easier to obtain a list of all of a persons friend's:

select *
from default_users as u
join default_friends as f on f.friend_id = u.id
where f.user_id = 1;

Just remember to put an index over user_id and friend_id in the default friends_table otherwise you will find your query may take a while:

create unique index idx_friend on default_friend(user_id,friend_id);

I hope this helps you. ;-)

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Are two indexes needed

Related Question