Recent Topics

[F-R] post_datelastcomment

Started by on Nov 09, 2006 – Contents updated: Nov 09, 2006

Nov 09, 2006 14:49    

Could we get a extra field in evo_posts called post_datelastcomment so when a comment is added to a post the date gets stuck in there.

Or possible even better;
post_lastcomment which would contain the ID of the last comment added to the post, that way we can use a sql join on it and get the same info.

Nov 09, 2006 21:18

You should be able to just get the last comment of an item from T_comments directly, ordering by date, shouldn't you?

Nov 10, 2006 03:44

well i want to be able to sort the posts by;
the post's date
or the post's last comment date
depending on which one is more recent.

Nov 10, 2006 16:55

Then try it with IF() in the SQL to select the higher of the two and than order by that field/alias.

There should be really no need to duplicate this info!

Nov 10, 2006 18:16

:( but i would have no idea how to do this in sql...

Nov 10, 2006 18:22

How about


SELECT IF( comments.datetime > items.datetime, comments.datetime, items.datetime ) AS order_datetime, other, fields
FROM comments INNER JOIN items ON comments.item_ID = items.ID
ORDER BY order_datetime


for a start? :)

That's all pseudo and might not work, but something like that should do it.

Nov 10, 2006 18:33

Ok i made

SELECT IF( `evo_comments`.`comment_date` > `evo_posts`.`post_datecreated` , `evo_comments`.`comment_date` , `evo_posts`.`post_datecreated` ) AS `order_date` , `evo_posts`. * 
FROM `evo_posts` 
INNER JOIN `evo_comments` ON `evo_comments`.`comment_post_ID` = `evo_posts`.`post_ID` 
ORDER BY `order_date` DESC 
LIMIT 0 , 30

Except, it has duplicate results....

Nov 10, 2006 18:51

Changing it to a LEFT JOIN works fine, i have no idea what the different types of joins do, but it appears to work with LEFT...

SELECT IF( `evo_comments`.`comment_date` > `evo_posts`.`post_datecreated` , `evo_comments`.`comment_date` , `evo_posts`.`post_datecreated` ) AS `order_date` , `evo_posts`. * 
FROM `evo_posts` 
LEFT JOIN `evo_comments` ON `evo_comments`.`comment_post_ID` = `evo_posts`.`post_ID` 
ORDER BY `order_date` DESC 
LIMIT 0 , 30

Nov 10, 2006 19:24

Ok blueyed, how would i go about getting the posts to order like this when using a particular skin?

Nov 10, 2006 19:36

See the ItemList(2) class. You would have to inject the SQL there probably somehow. Probably only the additional SELECT and ORDER BY.
I fear, it's not possible without touching the class itself and may be more hackier with ItemList, which gets used in 1.8.x as with ItemList2, which gets used since 1.9 IIRC.

Sorry..

Nov 10, 2006 19:42

So what about hacking up the core, so it does not create a itemlist if this certain skin/plugin is used.

And then i just have a while(getrecord) $Posts[$row['post_ID']] = new Post($row['post_ID']);

well along those lines...

Nov 10, 2006 19:56

Looking at it now, i could just hack up the "Get ID list for Item List (Main|Lastpostdate)" Query...

Any reasons why this would not work?

Nov 10, 2006 20:43

Yes, the "Get ID list for Item List (Main|Lastpostdate)" query seems to be the one to hack to death.. ;)


Form is loading...

powered by b2evolution – This forum is powered by b2evolution CMS, a complete engine for your website.