Page 1 of 1

Getting last updated from table

Posted: Sat Nov 29, 2008 8:17 am
by panazoom
I am creating a mini forum and have become a bit stuck when trying to get when each thread was last updated.

At the moment I have a table called posts which looks like this:

Code: Select all

 
post_id  |  content  |  created_date  | created_by  |  updated_date  | update_by
---------------------------------------------------------------------------------
1        | ....      | 123456789      | 1           | 234567891      | 2
2        | ....      | 345678912      | 1           | 456789123      | 2
3        | ....      | 567891234      | 2           | 456789123      | 1
From the above table I would want to get the values 'last_updated_date' = 567891234 and 'last_created_by' = 2.

I am looking to, in one query, get when the thread was last updated, either through the creation date or the last updated date. The only way I can think of doing this is rather evil, having two subqueries finding the MAX created_date and the MAX updated_date and then doing an:

Code: Select all

IF created_max > updated_max THEN ......
However, that cannot possibly be the best solution and I would imagine not the most optimised. Would anyone even know where to begin on something like this? :s

Thanks for your help and ideas!

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 9:30 am
by Eran
Please post your full current queries and the tables structure

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 10:29 am
by panazoom
pytrin wrote:Please post your full current queries and the tables structure
Create table for the `thread` and `post` table.

Code: Select all

CREATE TABLE `thread` (
    `thread_id` int(10) unsigned NOT NULL auto_increment,
    `thread_title` varchar(255) character set utf8 collate utf8_bin NOT NULL,
    PRIMARY KEY  (`thread_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1;
 
CREATE TABLE `post` (
    `post_id` int(10) unsigned NOT NULL auto_increment,
    `thread_id` int(10) unsigned NOT NULL,
    `content` longtext character set utf8 collate utf8_bin NOT NULL,
    `created_date` int(10) NOT NULL,
    `created_by` int(10) unsigned NOT NULL,
    `updated_date` int(10) NOT NULL,
    `updated_by` int(10) unsigned NOT NULL,
    PRIMARY KEY  (`post_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1;
And the SQL query:

Code: Select all

SELECT      t.thread_id, t.thread_title,
            p.post_id, p.created_date, p.created_by 
FROM        `thread` t
                  LEFT JOIN `post` p ON p.thread_id = t.thread_id
GROUP BY    p.thread_id
ORDER BY    MAX(p.post_created) DESC
LIMIT       0, 25

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 12:15 pm
by Eran
How about this:

Code: Select all

 
SELECT      t.thread_id, t.thread_title,
            p.post_id, MAX(GREATEST(p.created_date,p.updated_date)) AS active_date,p.created_date, p.created_by
FROM        `thread` t
            LEFT JOIN `post` p ON p.thread_id = t.thread_id
GROUP BY p.thread_id
 

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 12:41 pm
by panazoom
pytrin wrote:How about this:

Code: Select all

 
SELECT      t.thread_id, t.thread_title,
            p.post_id, MAX(GREATEST(p.created_date,p.updated_date)) AS active_date,p.created_date, p.created_by
FROM        `thread` t
            LEFT JOIN `post` p ON p.thread_id = t.thread_id
GROUP BY p.thread_id
 
I haven't tested it out yet, but it doesn't appear to get either the `created_by` or `updated_by` depending on which happened most recently - that is the real problem I am facing.

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 12:42 pm
by Eran
Did you notice:

Code: Select all

MAX(GREATEST(p.created_date,p.updated_date)) AS active_date
This is the most recent of the two

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 1:17 pm
by panazoom
pytrin wrote:Did you notice:

Code: Select all

MAX(GREATEST(p.created_date,p.updated_date)) AS active_date
This is the most recent of the two
Yup. That gets the most recent date, but not the most recent person who last updated the thread (be it by the `created_by` or `updated_by`). Basically - what is the user ID of the person who posted?

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 1:20 pm
by Eran
I just modified your original query to fit your requirements. If you need more information, join the query with the users table against the user id to retrieve his name

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 1:28 pm
by panazoom
pytrin wrote:I just modified your original query to fit your requirements.
No offence meant by this, but it doesn't.

The user ID is stored in these two columns: `created_by` and `updated_by`.

I need to get only one of those depending on which timestamp is the most recent.

If `created_date` is most recent I need to return `created_by` and if `updated_date` is most recent then I need to return `updated_by`.

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 1:45 pm
by Eran
I'm sorry, but you weren't very clear in you description. Your original query didn't contain any data about the users and you didn't include the schema for the users table.

The way it's structured currently it will be difficult - I suggest that when you insert a new post record, set the updated_date to be the same as created_date, and do the same with the user fields (created by = updated by). You can then always join against the updated_by column, and in your application code you can compare the updated_date to the created_date to determine if this is a new record or an updated one.

Also, I suggest you use timestamp or other MySQL intrinsic date/time types to store your date information,

Re: Getting last updated from table

Posted: Sat Nov 29, 2008 1:57 pm
by panazoom
pytrin wrote:I'm sorry, but you weren't very clear in you description. Your original query didn't contain any data about the users and you didn't include the schema for the users table.
I did say that I needed the user part, you must have missed it. There is no need for the users CREATE table because it is outside the scope of this problem.
panazoom wrote:From the above table I would want to get the values 'last_updated_date' = 567891234 and 'last_created_by' = 2.
pytrin wrote:I suggest that when you insert a new post record, set the updated_date to be the same as created_date, and do the same with the user fields (created by = updated by).
That's a good idea, actually. I think I will do that.
pytrin wrote:Also, I suggest you use timestamp or other MySQL intrinsic date/time types to store your date information,
I do use the built in MySQL timestamp, but this is dealing with a legacy system with millions of posts stored in UNIX timestamp.