Page 1 of 1

Get distinct recent updates

Posted: Mon Feb 15, 2010 6:37 am
by Weiry
I currently have a function which returns messages directed to a user that were created within the past 2 days of the current date.
This part of the code works fine, although if a multiple messages are created to a particular user, all the dates within the past 2 days will be displayed.
I am trying to return only the newest distinct result per conversation.
Example:
Todays date: 15/02/2010
To user1: Message 1 - 12/02/2010
From user2: Message 2 - 13/02/2010
To user1: Message 3 - 13/02/2010
To user1: Message 4 - 14/02/2010

In the above example only records within the past 2 days of todays date will show to user1 (Message 3/4)
However, because Message 3 is NOT the newest message, only Message 4 should be shown.

This is the current code:
I have tried a couple of things, but now when i stare at it, i can't seem to think how to accomplish this easily without exploding the amount of code required.

Code: Select all

    public function getRecentUpdate($userID){
        $userID = mysql_real_escape_string($userID);
        $q = "SELECT `user_id` FROM ".TBL_ADMIN." WHERE `user_id` = '{$userID}'";
        if($this->database->query($q)){
            //$q = "SELECT DISTINCT `jobID`,`topic`,`dateTime` FROM ".TBL_JOBS_POSTS." ORDER BY `dateTime` ASC";
            $q = "SELECT * FROM ".TBL_JOBS_POSTS." WHERE `toUser` = '{$userID}' ORDER BY `dateTime` DESC";
            $r = $this->database->query($q);
            if($r){
                $i=0;
                while($row = $this->database->fetchArray($r)){
                    $twoDaysAgo = date( "Y-m-d G:i:s",strtotime ( '-2 day' .date ( "Y-m-d G:i:s" )));
                    if($row['dateTime'] > $twoDaysAgo){
                        $recentArr[] = $row;
                    }
                }
                //print "<pre>";print_r($recentArr);print "</pre>";
                if(empty($recentArr)){
                    print "No Recent Updates.";
                    return false;
                }
                    //print "<pre>";print_r(array_unique($recentArr));print "</pre>";
                foreach($recentArr as $recent){
            
                    $search = array_search($recent['topic'],$recentArr[$i]);
                    if($search){
                        //print " I FOUND ONE ";
                        //print $search."<br/>";
                        $recentArr[$i] = $recent;
                    }
                    $i++;
                }
                return array_reverse($recentArr);
            }else{
                print "No Recent Updates.";
                return false;
            }
        }
        return false;
    }
cheers.

Re: Get distinct recent updates

Posted: Mon Feb 15, 2010 3:13 pm
by Christopher
I think you need to both ORDER BY DESC and LIMIT 1.

Re: Get distinct recent updates

Posted: Mon Feb 15, 2010 6:07 pm
by Weiry
If i were to use LIMIT 1, wouldn't that only return 1 result regardless on how many unique updates there were?
I am trying to eliminate duplicate jobID's appearing in the list, not limit the list itself.

The example i posted are messages for only 1 job.. however there can be many jobs, say 20 unique jobs each to be displayed with their own unique update. The same job should only appear once.

Re: Get distinct recent updates

Posted: Mon Feb 15, 2010 8:00 pm
by Christopher
Then you probably want to GROUP BY jobID.

Re: Get distinct recent updates

Posted: Mon Feb 15, 2010 8:51 pm
by Weiry
Ok that seems to have cleared up part of the problem, however it creates a new problem.
This is the updated query:
[sql]SELECT * FROM ".TBL_JOBS_POSTS." WHERE `toUser` = '{$userID}' GROUP BY `jobID` ORDER BY `dateTime` DESC[/sql]

This returns a list of what i need just fine except for one small problem.
GROUP BY will only return the the first entry of the group in order of appearance in the database.

As i explained in the example above:
because Message 3 is NOT the newest message, only Message 4 should be shown.
Currently the query only returns Message 3, not Message 4.

So if i have an entry like this:

Code: Select all

`id`     `jobID`     `toUser`     `fromUser`     `dateTime`          `topic`     `message`
1          4          2               1     '2010-02-15 10:53:27'     'RE: Blah'     'Message 1'
2          4          1               2     '2010-02-15 13:02:32'     'RE: Blah'     'Message 2'
3          4          1               2     '2010-02-15 13:33:33'     'RE: Blah'     'Message 3'
 
The query will return the entry with `id` 2 rather than 3 because it appears first in the list. And changing ASC/DESC only applies after the grouping has been completed.

Re: Get distinct recent updates

Posted: Wed Feb 17, 2010 1:21 am
by Weiry
I really don't want to have to bump my own thread here.

Short of creating two separate functions to gather all the data and then somehow grouping the `jobID`'s in an php function, does anyone know how to only GROUP BY in reverse order?
So that only the most recent posts are grouped first rather than the older ones.

As i mentioned previously, adding ORDER BY **** DESC doesn't fix the problem because it only reverses the order after the grouping is completed.

Re: Get distinct recent updates

Posted: Wed Feb 17, 2010 2:03 am
by John Cartwright
I've come across this issue many times before: a quick search reveals these links.. which led me to this link.

Re: Get distinct recent updates

Posted: Wed Feb 17, 2010 9:33 pm
by Weiry
i took me a little bit to get it working, partly due to being pretty tired but it works wonders. here is the updated SQL query:

Code: Select all

$q = "SELECT *
      FROM ".TBL_JOBS_POSTS."
      WHERE `id` = (
          SELECT max(`id`) 
          FROM ".TBL_JOBS_POSTS." AS f 
          WHERE f.`jobID` = `jobs_posts`.`jobID`
        )";

Re: Get distinct recent updates

Posted: Thu Feb 18, 2010 2:42 am
by Eran
While this will work, it is a correllated sub-query which can have bad performance on MySQL (at least, currently). You might to rewrite this condition as a join, as is shown in the link John posted.

Re: Get distinct recent updates

Posted: Thu Feb 18, 2010 2:58 am
by Weiry
While in dev, the correlated sub query will do while the whole app gets finished.
After it is finished i still have to go back over all the code and remove redundant and depreciated methods. This is partly due to using a modified version of classes from another project.
Not to mention i haven't worked with joins yet and would rather learn about it first before i implement it (which is yes, on my to do list)