Jump to content

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


mentalist

Recommended Posts

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

?>
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
            ";
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.