SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order Comments using Doctrine

    Hi I m using MySql with Doctrine to order all the comments a specified user has (either ones created by the user or a reply to a comment posted by the user)

    Here's the table structure in YAML
    Code:
    Comment:
        actAs:
            Timestampable:
        columns:
            id:
                type: integer(5)
                unsigned: true
                primary: true
                autoincrement: true
            text:
                type: clob(65532)
            user_id:
                type: integer(3)
                unsigned: true
            comment_type_id:
                type: integer(1)
                unsigned: true
                default: 0
            comment_reply_id:
                type: integer(5)
                unsigned: true
        relations:
            User:
                foreignAlias: Comment
                local: user_id
                foreign: id
                type: one
                foreignType: many
                onDelete: CASCADE
                onUpdate: CASCADE
            Comment:
                local: comment_reply_id
                foreign: id
                foreignAlias: Comment
                type: one
                foreignType: many 
                onDelete: SET NULL
                onUpdate: CASCADE
            CommentType:
                local: comment_type_id
                foreign: id
                foreignAlias: Comment
                type: one
                foreignType: many
                onUpdate: CASCADE
    
    CommentType:
        columns:
            id:
                type: integer(1)
                unsigned: true
                primary: true
                autoincrement: true
            name:
                type: string(50)
                unique: true
            regex:
                type: string(255)
                unique: true
            notifications:
                type: boolean
        indexes:
            type_index:
                fields: 
                    notifications: []
    To get all the comments that reference a specified user I can do:
    PHP Code:
            $q=Doctrine_Query::create()
                ->
    select('c.*, COUNT(c.id) AS ctr')
                ->
    from('Comment c')
                ->
    leftJoin('c.Comment d')
                ->
    innerJoin('c.CommentType t')
                ->
    where('c.user_id = ?'$user->getId())
                ->
    orWhere('d.user_id = ?'$user->getId()); 
    But the problem is there is no order to that query. The result (ideally) should be like this:

    id text reply_id created_at
    1 a null time()
    2 b 1 time()+1
    3 c 1 time()+2
    4 d null time()-3

    I've experimented with a few different order by and group by functions but I can't get it to work. One major problem is that if the comment does not have a reply_id it needs to be ordered by the date DESCENDING. But if there is a reply_id then the date needs to be ASCENDING.

    I'm not sure if this is possible using just queries. But any help would be greatly appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by leosurf91 View Post
    One major problem is that if the comment does not have a reply_id it needs to be ordered by the date DESCENDING. But if there is a reply_id then the date needs to be ASCENDING.
    that doesn't make sense

    at least, not to me

    perhaps you can show some actual sample rows and how you would like to see them ordered?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yah I can put up some rows but its just kinda hard to format em.

    Basically think of Facebook. The Comments on a User's page are ordered by the date created DESCENDING. But reply comments are ASCENDING.

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like I said, this may be impossible with a query. In which case I can just order it with some php and throw it in a json/xml template.

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ALright here's a few sample comments in YAML format.

    Code:
    Comment:
        UserComment:
            text: |
                Hey this is a test comment from cameron.j.moore@gmail.com...isn't this cool.
            User: CameronMoore
            CommentType: Basic
        UserCommentReply:
            text: |
                This is a reply to the first comment
            User: CameronMoore
            Comment: UserComment
            CommentType: Basic
        UserCommentSecondReply:
            text: |
                This is another reply to the first comment
            User: CMoore
            Comment: UserComment
            CommentType: Basic
        AnotherComment:
            text: |
                Second Comment here.
            User: CameronMoore
            CommentType: Basic
        SecondUserComment:
            text: |
                Hey another test
            User: CMoore
            CommentType: Basic
    When I query for comments that are by or reference User CameronMoore I would like the result to be ordered like this:

    UserComment
    UserCommentReply
    UserCommentSecondReply
    AnotherComment

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't understand yaml, but i can sort of figure it out, and it appears you have left off the timestamps, so of course the ORDER BY is difficult to visualize

    your ORDER BY clause just needs two columns --
    Code:
    ORDER BY datecreated DESC, replycommentdate ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh sorry the:
    actAs:
    Timestampable:

    takes care of adding two datetime fields (created_at, and updated_at)

    Let me just get the SQL and post that.

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    CREATE TABLE `comment` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `text` text,
      `user_id` mediumint(8) unsigned DEFAULT NULL,
      `comment_type_id` tinyint(3) unsigned DEFAULT '0',
      `comment_reply_id` bigint(20) unsigned DEFAULT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `user_id_idx` (`user_id`),
      KEY `comment_reply_id_idx` (`comment_reply_id`),
      KEY `comment_type_id_idx` (`comment_type_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
    
    --
    -- Dumping data for table `comment`
    --
    
    INSERT INTO `comment` VALUES(9, 'Hey this is a test comment from cameron.j.moore@gmail.com...isn''t this cool.\n', 5, 3, NULL, '2010-03-11 13:13:18', '2010-03-11 13:13:18');
    INSERT INTO `comment` VALUES(10, 'This is a reply to the first comment\n', 5, 3, 9, '2010-03-11 13:13:18', '2010-03-11 13:13:18');
    INSERT INTO `comment` VALUES(11, 'This is another reply to the first comment\r\n', 6, 3, 9, '2010-03-11 13:11:18', '2010-03-11 13:11:18');
    INSERT INTO `comment` VALUES(12, 'Second Comment here.\r\n', 5, 3, NULL, '2010-03-11 13:05:18', '2010-03-11 13:13:18');
    INSERT INTO `comment` VALUES(13, 'Hey another test\n', 6, 3, NULL, '2010-03-11 13:13:18', '2010-03-11 13:13:18');
    
    --
    -- Constraints for dumped tables
    --
    
    --
    -- Constraints for table `comment`
    --
    ALTER TABLE `comment`
      ADD CONSTRAINT `comment_comment_reply_id_comment_id` FOREIGN KEY (`comment_reply_id`) REFERENCES `comment` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
      ADD CONSTRAINT `comment_comment_type_id_comment_type_id` FOREIGN KEY (`comment_type_id`) REFERENCES `comment_type` (`id`) ON UPDATE CASCADE,
      ADD CONSTRAINT `comment_user_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT main.updated_at AS sortkey1
         , NULL            AS sortkey2
         , main.text
         , main.user_id
      FROM comment AS main
     WHERE main.comment_reply_id IS NULL
    UNION ALL
    SELECT main.updated_at AS sortkey1
         , reply.updated_at AS sortkey2
         , reply.text
         , reply.user_id
      FROM comment AS main
    INNER
      JOIN comment AS reply
        ON reply.comment_reply_id = main.id
     WHERE reply.comment_reply_id IS NOT NULL
    ORDER
        BY sortkey1 DESC
         , sortkey2 ASC
    unable to confirm that this actually works as expected because all your sample data had the same timestamps!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll have to test it a bit more but that definitely seems to work. Thanks a lot!

    I didn't know about the UNION clause, that's pretty nifty!


Bookmarks

Posting Permissions

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