mentalist Posted July 31, 2015 Share Posted July 31, 2015 I've made this into a demo, basically there's 3 tables, one which has posts, the second is user info and the third holds rating information. I'm after bringing back all the posts (with username) and their rating. However some posts have no rating, and those get grouped together and come back as one, how to stop that from happening. I've also tried UNION, UNION ALL, but am either getting it wrong or its not doing what I'm expecting it too. To recap, return all posts individually and not group the posts without ratings. Cheers <?php echo "<h2>TESTS</h2>"; include("db.php"); // DB SETUP $prefix="tests_"; $host="localhost"; $uname="user"; $pass="password"; $dbname="dbtest"; $db=new cDB($prefix,$host,$uname,$pass,$dbname); // INSTALL do_install($db); // GET TABLE $tn=$db->prefix."post"; // id,post,uid $tn2=$db->prefix."user"; // id,uname $tn3=$db->prefix."rate"; // id,pid,num $sql="SELECT t1.id,t1.post,t1.uid,t2.uname,SUM(num) as n FROM ".$tn." AS t1 LEFT OUTER JOIN ".$tn2." AS t2 ON t1.uid=t2.id LEFT OUTER JOIN ".$tn3." AS t3 ON t1.id=t3.pid GROUP BY t3.pid "; $a=$db->raw($sql); echo "<table>"; foreach($a as $e){ echo "<tr><td>".$e['id']."</td><td>".$e['post']."</td><td>".$e['uname']."</td><td>".$e['n']."</td></tr>\n"; } echo "</table>"; function do_install($db){ // tests_post $tn=$db->prefix."post"; // id,post,uid $s="`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `post` TEXT NOT NULL, `uid` INT NOT NULL"; $db->table_drop($tn); $db->table_create($tn, $s); $db->table_insert($tn, "'','Tell me all about it',1"); // 1 $db->table_insert($tn, "'','I bet its interesting',2"); // 2 $db->table_insert($tn, "'','Today is yesterday tomorrow',3"); // 3 $db->table_insert($tn, "'','Tomorrow is yesterday in a couple of days',4"); // 4 $db->table_insert($tn, "'','Another day is yet to pass',1"); // 5 $db->table_insert($tn, "'','Another day is yet to pass again',1"); // 6 // tests_user $tn=$db->prefix."user"; // id,uname $s="`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `uname` VARCHAR(32) NOT NULL"; $db->table_drop($tn); $db->table_create($tn, $s); $db->table_insert($tn, "'','Alice'"); // 1 $db->table_insert($tn, "'','Bob'"); // 2 $db->table_insert($tn, "'','Carol'"); // 3 $db->table_insert($tn, "'','Derrick'"); // 4 // tests_rate $tn=$db->prefix."rate"; // id,pid,num $s="`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `pid` INT NOT NULL, `num` INT NOT NULL"; $db->table_drop($tn); $db->table_create($tn, $s); $db->table_insert($tn, "'',1,1"); $db->table_insert($tn, "'',2,-1"); $db->table_insert($tn, "'',1,1"); $db->table_insert($tn, "'',3,1"); $db->table_insert($tn, "'',4,1"); $db->table_insert($tn, "'',4,-1"); $db->table_insert($tn, "'',1,1"); $db->table_insert($tn, "'',3,-1"); /* 1: 1,1,1=3 2: -1=-1 3: 1,-1=0 4: 1,-1=0 */ } ?> Quote Link to comment Share on other sites More sharing options...
mentalist Posted July 31, 2015 Author Share Posted July 31, 2015 Ooooh... $sql="SELECT t1.id,t1.post,t1.uid,t2.uname,SUM(num) as n FROM ".$tn." AS t1 LEFT OUTER JOIN ".$tn2." AS t2 ON t1.uid=t2.id LEFT OUTER JOIN ".$tn3." AS t3 ON t1.id=t3.pid GROUP BY COALESCE(t3.pid,t1.id) "; I think the COALESCE did the trick... Quote Link to comment Share on other sites More sharing options...
mentalist Posted July 31, 2015 Author Share Posted July 31, 2015 I also needed the default count to be 0 to make the ordering correct, again COALESCE came in handy: $sql="SELECT t1.id,t1.post,t1.uid,t2.uname,COALESCE(SUM(num),0) AS n FROM ".$tn." AS t1 LEFT OUTER JOIN ".$tn2." AS t2 ON t1.uid=t2.id LEFT OUTER JOIN ".$tn3." AS t3 ON t1.id=t3.pid GROUP BY COALESCE(t3.pid,t1.id) ORDER BY n DESC "; Quote Link to comment Share on other sites More sharing options...
requinix Posted August 1, 2015 Share Posted August 1, 2015 I'm not sure why you're grouping by the rating ID. Shouldn't you be using the post ID? The only thing you should care about in the rating table is its post ID (to link to the post) and the "num" (rating value or whatever). Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.