Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

    Unanswered: Selecting threads with the most-recent posts

    Hi,

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

    Code:
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please indicate exactly which columns you need from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.

  6. #6
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    If you do something like:

    Code:
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.

  11. #11
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.
    Last edited by darkangel; 08-19-08 at 14:49.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    should you? i don't know

    adding redundant columns that have to be updated every time somebody posts sounds a lot like:
    Code:
    if self===foot.shoot() { self = wrists.slice() }
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    I'm thinking about performance as well ... having to do a:

    Code:
    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.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ... )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •