2

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id but didn't know how to compare the users.user_id with friend.friend_id and friend.user_id in the same query. I could do that at development level but I don't want that because when I add LIMIT could be a horror iterate between results. Any help?

--
-- Table structure for table `default_comment`
--

CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Table structure for table `default_friend`
--

CREATE TABLE IF NOT EXISTS `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;

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`,     `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES 
(0, 1, 1, 0, '2012-08-13 18:15:54', 0, 1),
(2, 1, 1, 0, '2012-08-13 18:16:11', 0, 1);

--
-- Table structure for table `default_profiles`
--

CREATE TABLE IF NOT EXISTS `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  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

INSERT INTO `default_profiles` (`id`, `created`, `updated`, `created_by`, `ordering_count`, `user_id`, `display_name`, `first_name`, `last_name`, `updated_on`) 
VALUES
 (1, NULL, NULL, NULL, NULL, 1, 'Reynier Perez Mira', 'Reynier', 'Perez Mira', 1344889677),
 (2, '2012-08-13 11:44:29', NULL, 1, 1, 2, 'Demo', 'Demo', 'Demo', NULL);

--
-- Table structure for table `default_status`
--

CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Table structure for table `default_users`
--

CREATE TABLE IF NOT EXISTS `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  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='' AUTO_INCREMENT=3 ;

INSERT INTO `default_users` (`id`, `email`, `password`, `salt`, `group_id`, `ip_address`, `active`, `activation_code`, `created_on`, `last_login`, `username`, `forgotten_password_code`, `remember_code`) 
VALUES 
(1, '[email protected]', '87cdcd2c46479ef677689526cc1a71adebfdcde3', '07f7d', 1, '', 1, '', 1344872988, 1344893013, 'admin', NULL, NULL),
(2, '[email protected]', '2ff62db826f753e0f7712578eddcea778a452448', '05e9a0', 7, '127.0.0.1', 1, NULL, 1344890669, 1344891197, 'demo', NULL, NULL);

I build this one:

SELECT DISTINCT s.*, c.*, p.*, u.*, 
(SELECT f.friend_id FROM default_friend f WHERE (f.user_id = u.id AND f.friend_id = 1 ) OR (f.friend_id=u.id AND f.user_id=1) LIMIT 1)) AS f_friend_id,
(SELECT f.user_id FROM default_friend f WHERE (f.user_id = u.id AND f.friend_id = 1 ) OR (f.friend_id=u.id AND f.user_id=1) LIMIT 1)) AS f_user_id,
FROM profiles p, users u, status s 
LEFT JOIN comment c ON (c.status_id = s.status_id) 
WHERE s.user_id = 1

but I'm not so sure if it works and if it's optimized because I will have a lot of rows on those tables. Any advice or help or tip?

0

Your Answer

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