Using JOIN and GROUP, and returning all entries without group item individually

[mentalist]

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
    */
}

?>

[mentalist]

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

[mentalist]

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
            ";

[requinix]

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