[SOLVED] latest posts/all info in single query?

[redbullmarky]

*sigh*

It's been a while since I've gotten properly stuck into MySQL queries so i'm a little rusty.

 

I'm attempting to create a mini forum using just a single table.

 

anyway, here's my schema

CREATE TABLE `board_messages` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(10) unsigned default '0',
  `created_at` int(11) unsigned default NULL,
  `created_by` int(10) unsigned default NULL,
  `sticky` enum('y','n') default 'n',
  `locked` enum('y','n') default 'n',
  `subject` varchar(255) default NULL,
  `body` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

 

the actual parent topic makes use of all of these fields and has a parent_id of 0.

replies, however, dont use 'subject' and set parent_id to the id of the main parent topic. Is it possible in a single query to get a single list of topics, ordered primarily by 'sticky' being set to 'y', and then ordered by the last reply (if any).

 

One query or a couple?

 

Cheers!

Mark

[shoz]

You should be able to use

SELECT
b.*, MAX(b2.created_at) AS last_reply_date
FROM
board_messages AS b
LEFT JOIN
board_messages AS b2
ON
b.id = b2.parent_id
WHERE
b.parent_id = 0
AND
b2.parent_id > 0
GROUP BY
b2.parent_id
ORDER_BY
b.sticky DESC,
last_reply_date DESC

 

In MYSQL 4.1 or higher the following may be faster provided their's a multi-column index on (parent_id, created_at) and (sticky, parent_id). This is an educated guess, you'll have to play around with it to be sure of course.

 

SELECT
b.*
FROM
board_messages AS b
LEFT JOIN
(
    SELECT
    parent_id, MAX(created_at) AS last_reply_date
    FROM
    board_messages
    WHERE
    parent_id > 0
    GROUP BY
    parent_id
) AS l
ON
b.id = l.parent_id
WHERE
b.parent_id = 0
ORDER BY
b.sticky DESC,
l.last_reply_date DESC
LIMIT ...

[shoz]

I noticed an error in query 1.

GROUP BY
b.parent_id

 

should be

 

GROUP BY
b2.parent_id

 

I've made the change above.

[redbullmarky]

oops sorry, me firing off too soon.

 

now i get all the regular posts and one sticky. there are 4 stickys.

 

here's the query as i've used it:

 

	SELECT
	b.*, MAX(b2.created_at) AS last_reply_date
	FROM
	board_messages AS b
	LEFT JOIN
	board_messages AS b2
	ON
	b.id = b2.parent_id
	WHERE
	b.parent_id = 0
	AND
	b2.parent_id > 0
	GROUP BY
	b2.parent_id
	ORDER BY
	b.sticky ASC,
	last_reply_date DESC

[redbullmarky]

it seems to be working, UNLESS a topic has no replies, in which case the topic is ignored.

[shoz]

it seems to be working, UNLESS a topic has no replies, in which case the topic is ignored.

 

GROUP BY b.id instead

 

GROUP BY
b.id

[redbullmarky]

just gave that a go - exactly the same results came back, so still missing topics not replied to

[shoz]

Hmm, I think I see the problem. Try the following.

 

SELECT
b.*, IFNULL(MAX(b2.created_at), b.created_at) AS last_reply_date
FROM
board_messages AS b
LEFT JOIN
board_messages AS b2
ON
b.id = b2.parent_id
WHERE
b.parent_id = 0
GROUP BY
b.id
ORDER BY
b.sticky ASC,
last_reply_date DESC

[shoz]

The query has been edited. Note the removal of the WHERE clause condition for b2 and the addition of the IFNULL() in the coiumn list. Both address two separate problems.

[redbullmarky]

you're a legend. works a treat, thanks mate!

 

whilst i'm here, there's two more related things i'm wondering.

 

1, is it easy enough to change so that only the posts get reordered, not the stickies?

 

2, to get the created_by of the last replier, is it just a case of IFNULL(b2.created_by, b.created_by) ? i'm only testing it out with a couple of created_by user id's, so it's hard to tell right now if the result will be the same once things get up and rocking properly.

[shoz]

1, is it easy enough to change so that only the posts get reordered, not the stickies?

If the stickies should instead be ordered by their creation date, then you could try this ORDER BY.

 

ORDER BY
b.sticky ASC,
IF(b.sticky = 'y',  b.created_at, last_reply_date) DESC

 

2, to get the created_by of the last replier, is it just a case of IFNULL(b2.created_by, b.created_by) ? i'm only testing it out with a couple of created_by user id's, so it's hard to tell right now if the result will be the same once things get up and rocking properly.

 

2) In older versions of MYSQL you'd need two queries. One to create a temporary table with the results of the first query (with the LIMIT ...) and the other to JOIN the temp table with the messages table to retrieve the "created_by" of the latest reply.

 

SELECT t.*, IFNULL(b.created_by, t.created_by) AS created_by
FROM
temp_table AS t
LEFT JOIN
messages AS b
ON
t.id = b.parent_id
AND
t.last_reply_date = b.created_at

[redbullmarky]

so essentially, when MAX(b2.created_at) is found, it's not possible to find out the record that that particular maximum came from and return its corresponding created_by without using a temporary table?

 

i'm on mysql 4.1 here, though I used your very first suggestion instead just so's i could get my head around it.

[shoz]

so essentially, when MAX(b2.created_at) is found, it's not possible to find out the record that that particular maximum came from and return its corresponding created_by without using a temporary table?

 

Yes, once you do the GROUP BY the b2.created_by user will be somewhat arbitrary and should not be relied upon to hold the corresponding value to the MAX(created_at)

 

i'm on mysql 4.1 here, though I used your very first suggestion instead just so's i could get my head around it.

 

You can use this SUBSELECT instead then.

SELECT
b.*, IFNULL(l.created_by, b.created_by) AS created_by, IFNULL(l.last_reply_date, b.created_at) AS last_reply_date
FROM
board_messages AS b
LEFT JOIN
(
    SELECT
    sb.*, sbmax.last_reply_date
    FROM
    board_messages AS sb
    INNER JOIN
    (
        SELECT
        parent_id, MAX(created_at) AS last_reply_date
        FROM
        board_messages
        WHERE
        parent_id > 0
        GROUP BY
        parent_id
    ) AS sbmax
    ON    
    sb.parent_id = sbmax.parent_id
    AND
    sb.created_at = sbmax.last_reply_date
    WHERE
    sb.parent_id > 0
    ) AS l
ON
b.id = l.parent_id
WHERE
b.parent_id = 0
ORDER BY
b.sticky ASC,
l.last_reply_date DESC
LIMIT ...

 

There may be syntactical or logical errors. I've looked it over a couple of times to be sure there aren't any but I could have made a mistake.

 

Note that it should be possible to further optimize the query but I thought I'd give a working one and if performance becomes an issue deal with that at a later date.

[redbullmarky]

alright you've been a huge help! I've quickly knocked up something using a seperate query to get latest poster for the time being, the important thing being that my forums are now in order. I'll have a good play around with the subselects anyway. Thanks for the help!

 

Cheers

Mark

[redbullmarky]

ps FYI, I've just tried the latest query you posted and it seems to be working also - both the ordering and last replier are showing correctly.