2

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 | [email protected] | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |         2 |       1 | 1345055203 |
|       1 | [email protected] | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |         1 |       3 | 1345055203 |
|       1 | [email protected] | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |         1 |    4315 | 1345055203 |
|       1 | [email protected] | Reynier Perez Mira | Reynier                     | Perez Mira                 |     12686 | c1aqq8fy7lp8c2gvph4j3mwllqrtvohf2oto2dsgv6qkgsdf98w7029vpriju7p87h0zvrt7nqrp7v61v3zw7a5zg96sl0mfts4w |      6380 |       1 | 1345055203 |
|       1 | [email protected] | Demo               | Demo                        | Demo                       |     10484 | ivp1e40f350bf6ifplzsd560k6e5mvgvdq91q7j2k5oldraoftbd2k2eirtdihh7fbqwq2mkohqi05d3fw0sawvqmabah979updo |         2 |       1 | 1345055064 |
|       1 | [email protected] | User 1             | User                        | User                       |     10494 | arstvbkjrea725bea4mv1kv1qaelt669n7mequh5j6n5uzh8a3voy7w6lvebphrhkhnks9fzos46p8sqptud1mb3wysjpge653qe |         1 |       3 | 1345055064 |
|       1 | [email protected] | user502bc8a4942d1  | user502bc8a4942d1-firstname | user502bc8a4942d1-lastname |      7796 | vmkhv266hu8s7czpy1j2m1cvi3j1i3jr35hl76fsdqdv1cacv5etylls3q6yqrm7r2nrymjcqrvtzu267huajs5g9j9t3kdz6f9l |         1 |    4315 | 1345054882 |
|       1 | [email protected] | user502bc90a00ba4  | user502bc90a00ba4-firstname | user502bc90a00ba4-lastname |      6103 | c3mmq89ucbcjd0w1pdak0tgtioyfy5loa3z18ecmkwukusojbr6qwosbham8d132fzq1pn6ei3h5dgoabyvytyyuiqm2k09fmudy |      6380 |       1 | 1345054774 |
|       1 | [email protected] | Demo               | Demo                        | Demo                       |      3828 | hky500btaj5rq96inod22k1zpyh1uz65892mwanuloqkqgjo1h3u2f0ikery3pe3olwoqbqq08t301vqpuhirvddp3lq6w5ppoep |         2 |       1 | 1345054631 |
|       1 | [email protected] | 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

Improve this question
7
  • Back again? I'm just on the way back from work. Once I'm home let's chat and fix this before you have to raise a fourth question :-)
    – Mr.Brownstone
    Commented Aug 16, 2012 at 15:36
  • @mr-brownstone thanks but I really need help on this, as you notice I'm doing my home tasks but can't get this to work :( where are U from? Or better which time is there now and what time you'll be in your home
    – ReynierPM
    Commented Aug 16, 2012 at 15:41
  • Will be back home in about 30 minutes, it is 16:45 in the UK. When is your deadline?
    – Mr.Brownstone
    Commented Aug 16, 2012 at 15:43
  • @Mr.Brownstone Ohhh I'm in Caracas, Venezuela and here is 11:18 AM so it's working time yet :) but don't worry about that because this is part of my job the dawn query !!! Sorry for that comment!
    – ReynierPM
    Commented Aug 16, 2012 at 15:47
  • Ok, let's make sure we solve it once and for all today.
    – Mr.Brownstone
    Commented Aug 16, 2012 at 15:50

1 Answer 1

Reset to default
5

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. ;-)

Improve this answer
2
  • @mr-brownstone still having problems :( ping!!
    – ReynierPM
    Commented Aug 16, 2012 at 20:43
  • @mr-browstone ok, for some reason and also changing from my approach (which use BuddyPress by the way) to yours I still getting duplicates on the main query, the big one, also I notice that trying to COUNT the results for use as pagination parameter I get 25 records instead of 12 and doesn't know if I can use DISTINCT in a COUNT(*) sentence, this are the issues
    – ReynierPM
    Commented Aug 17, 2012 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.