[darkangel]

Hi,

I need to show threads which are either new or which have new posts.

CREATE TABLE `coaching_threads` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `title` varchar(45) NOT NULL,
  `featured` bit(1) NOT NULL default '\0',
  `coaching_post_count` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;

CREATE TABLE `coaching_posts` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `coaching_thread_id` smallint(5) unsigned NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `user_id` mediumint(8) unsigned NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;

Any ideas?

I thought of trying to get the most recent posts (by created date) with a distinct coaching_thread_id, but I'm not sure how to write that exactly.

_da.

[r937]

please indicate exactly which columns you need from each table

[darkangel]

Hi Rudy,

I need thread id, title, featured, coaching_post_count. I also need the user_id and body from the first post in the thread, but that can be done separately if necessary.

Regards,

_da.

[r937]

SELECT t.id
     , t.title
     , t.featured
     , t.coaching_post_count
     , p.user_id 
     , p.body 
  FROM coaching_threads AS p
LEFT OUTER
  JOIN ( SELECT coaching_thread_id
              , MAX(created) AS max_created
           FROM coaching_posts
         GROUP
             BY coaching_thread_id ) AS m
    ON m.coaching_thread_id = t.id
LEFT OUTER
  JOIN coaching_posts AS p
    ON p.coaching_thread_id = t.id
   AND p.created = m.max_created

by the way, why are created and modified both NOT NULL?

[darkangel]

Didn't work unfortunately.

I'm using a PHP framework (CakePHP), which doesn't support OUTER JOINS, SUB SELECTS, etc., so I'd have to use a custom query, which would make paging etc. more difficult, but that's not the end of the world.

created and modified are NOT NULL because as soon as the record is created, it should have non-null dates in those two fields. A NULL value would have no meaning.

Thanks,

_da.

[darkangel]

If you do something like:

SELECT * from coaching_threads
JOIN coaching_posts
ON coaching_threads.id = coaching_posts.coaching_thread_id
ORDER BY coaching_posts.created desc
LIMIT 5

Is it possible to prevent duplicate thread IDs from being returned?

_da.

[r937]

Originally Posted by darkangel

I'm using a PHP framework (CakePHP), which doesn't support OUTER JOINS, SUB SELECTS, etc.,

self := foot.shoot()

i'm not going to be able to help you

regarding the dates, well, i suppose you could consider that a post has been immediately modified the second it is created

however, i must disgree with you, a NULL in modified would have meaning -- it would mean the post has not been modifed

[darkangel]

Yes, I agree with regard to the modified date, but it's a framework thing again, the modified date is automatically set at creation time (which is pretty stupid actually).

Not the best framework, or even a programming language I'm that fond of, but that's what I have to use right now unfortunately.

[r937]

first of all, i know sql but not php (heck, i can barely spell php)

so i asked on another (more active) forum, and was given this link --

http://www.codeclippers.com/clipping...e:_FinderQuery

i must say that this whole "framework" idea smells awfully fishy if you can't write meaningful sql statements

[darkangel]

Thanks Rudy. Yes, you can write finder queries and custom queries, but I'm not sure how that will affect record paging and other framework functionality.

The query you wrote though didn't return the expected results.

Another option is to maintain an extra field in the thread table with the date of the last post ... I started with this but maintaining it was confusing when records were deleted, etc. ... I may have to return to this.

Do you know the answer to my question in post #6 above?

Appreciate your help.

_da.

[darkangel]

Rudy,

Do you think I should store a first_post_id and last_post_id in the thread table? Or any other data that is able to be selected from the existing data?

_da.

[r937]

should you? i don't know

adding redundant columns that have to be updated every time somebody posts sounds a lot like:

if self===foot.shoot() { self = wrists.slice() }

[darkangel]

I'm thinking about performance as well ... having to do a:

SELECT *
FROM coaching_posts AS p
WHERE p.coaching_thread_id = 123
ORDER BY p.created ASC/DESC
LIMIT 1

Just to retrieve the first/last post in a thread might not be the best idea.

_da.

[r937]

yeah, sorting the entire table just so that you can use LIMIT 1 on it, that seems excessive

what about a subquery?

WHERE created = ( SELECT MAX(created) FROM ... )

[darkangel]

Good idea, I can use that in other areas. However, it doesn't really solve the problem I have regarding selecting the threads with the most-recent posts.

_da.