FB lajkovanje...

[sinisake]

Pokusavam ovih dana da napravim neki social networking sajt, uz pomoc Codeignitera, neku obogaljenu i ogoljenu varijantu gejsbuka, sa osnovnim funkcijama (zarad vezbe, nije nikakav posao). Postojeca baza izgleda ovako:

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 28, 2011 at 12:40 PM
-- Server version: 5.5.8
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `socialize`
--

-- --------------------------------------------------------

--
-- Table structure for table `ci_sessions`
--

CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '0',
  `user_agent` varchar(120) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(30) NOT NULL,
  `logged_in` int(11) NOT NULL,
  PRIMARY KEY (`session_id`),
  KEY `last_activity_idx` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ci_sessions`
--

INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`, `username`, `logged_in`) VALUES
('49903a602dc97815b09afa668c5633aa', '127.0.0.1', 'Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.63 Safari/535.7', 1324982478, 'zozo', 1),
('b9c91a6c0295e735eb7924fc7e6d6e8d', '127.0.0.1', 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30)', 1325010798, 'testo', 1),
('ccd724a8c9c85a7da0392c131acfa1c1', '127.0.0.1', 'Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20100101 Firefox/8.0', 1324982287, 'sinisake', 1);

-- --------------------------------------------------------

--
-- Table structure for table `likes`
--

CREATE TABLE IF NOT EXISTS `likes` (
  `lid` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `like_user` varchar(30) NOT NULL,
  PRIMARY KEY (`lid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `likes`
--

INSERT INTO `likes` (`lid`, `post_id`, `like_user`) VALUES
(1, 25, 'sinisake'),
(2, 25, 'zozo'),
(3, 12, 'sinisake'),
(4, 12, 'zozo');

-- --------------------------------------------------------

--
-- Table structure for table `posts`
--

CREATE TABLE IF NOT EXISTS `posts` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`pid`, `title`, `content`, `user_id`, `time`) VALUES
(1, 'Great song!', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/y0LgDlvtgYs'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 18:37:24'),
(2, '35553', 'Write something here...', 1, '2011-12-27 18:51:18'),
(4, 'Novi post', 'A jbg... valda radi...', 2, '2011-12-27 19:21:43'),
(5, 'Sonic ljut!', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/RtWU_X_61A8'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 19:41:34'),
(11, '', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/y0LgDlvtgYs'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 23:34:49'),
(12, 'Master of Stratocaster', '<iframe width=''200'' height=''150'' src=''http://www.youtube.com/embed/1x_IVpr1oso'' frameborder=''0'' allowfullscreen></iframe>', 1, '2011-12-27 23:36:27'),
(25, '', 'Jos jedan...', 1, '2011-12-28 00:08:52'),
(26, 'Ovo je test post', 'Mislim da bi ovo trebalo da funkcionise...no, vidicemo vec...', 3, '2011-12-28 09:37:20');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(32) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `email` varchar(30) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `show_name` tinyint(1) NOT NULL,
  `image` varchar(100) NOT NULL,
  `about` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `first_name`, `last_name`, `email`, `active`, `show_name`, `image`, `about`) VALUES
(1, 'sinisake', '8aa87050051efe26091a13dbfdf901c6', 'Sinisa', 'Test', 'trutru@yahoo.com', 1, 1, 'sinisake1324983446.png', 'Something against you...'),
(2, 'testo', '69e153e4d7add22f245e24de590eec21', 'testo', 'testic', 'test@test.com', 1, 1, 'no_image.gif', ''),
(3, 'zozo', 'bdc7f4fae58fa4d5b4b48226896aeea9', 'zozo', 'zozoic', 'zozo@zozo.net', 1, 1, 'zozo1324982557.jpg', 'Ja sam Zozo...');

E, sad... imam problem sa prikazom... hteo bih da na strani na kojoj se nalaze postovi (statusi, sta god) imam i one standardne podatke - ko je lajkovao, koliko lajkova ima post, da se na klik ispod posta otvaraju komentari (dakle, najmanje 4 tabele bi trebalo spojiti jednim upitom...

Ovaj upit delimicno resava stvar:

SELECT *
FROM posts
JOIN users ON users.id = posts.user_id
LEFT JOIN likes ON likes.post_id = posts.pid
LIMIT 0 , 30

ali, onda u prikazu imam problem, jerbo dobijam onoliko postova koliko ima lajkova (postovi se dupliraju).

Da li je ovo moguce resiti jednim sveobuhvatnim upitom (ukljucujuci i komentare), ili cu morati da idem na vise upita (napravim posebne funkcije get_likes() i get_comments(), pa ih pozivam tokom ispisa (sto bi verovatno bila smrt za server?) Da li je redizajn baze resenje?

Hvala!

P.S. Naravno da ne bih odradio SELECT *, izvukao bih samo ono sto treba, ali ovo je zbog testa i preglednosti....