Order Comments using Doctrine

PHP
1

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


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:

		$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

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?

3

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

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

ALright here’s a few sample comments in YAML format.

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

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 –

ORDER BY datecreated DESC, replycommentdate ASC
7

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 

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.\
', 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\
', 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\
', 6, 3, 9, '2010-03-11 13:11:18', '2010-03-11 13:11:18');
INSERT INTO `comment` VALUES(12, 'Second Comment here.\\r\
', 5, 3, NULL, '2010-03-11 13:05:18', '2010-03-11 13:13:18');
INSERT INTO `comment` VALUES(13, 'Hey another test\
', 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 
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!

:slight_smile:

10

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!