1

I am building a comment, with replies system using CakePHP and Backbone. But I am having some issues with my CakePHP query, that does not order in the way it needs to be.

So this is database system I have right now,

Posts
   ID (Auto, PK)
   MainPost (Long Text)
   Timestamp
   User_id

Comments 
   ID (Auto, PK)
   SubComment (Long Text)
   Timestamp
   User_id
   Post_id (FK, linking to the Posts table)

Now I think my model is ok (if needed I can post that code). I can save and update the posts and comments. But what I need to when a new comment is added to the system. That whole post is moved to the top.

This is the query I have done on my Posts Table,

    $Table->find('all')
          ->contain(['Comments',
                     'Comments.Users',
                    ])
        // ->order(['Posts.Comments.timestamp' => 'DESC'])
        // ->matching('Comments', function ($q) {
                  // return $q->where(['Comments.timestamp' => 'DESC']);
            // })

           ->toArray();

So I want to do a sort, like my order looks above, I basically want to order my Posts Table based on the timestamp of my Comments Table.

When I run this query, with order enabled, it just says that "Unknown column" but I don't know why? I have a LeftJoin in my model linking the Comments to my Posts table.

I have tried doing sorts and orders within the contain call of the query, however these just sort/order the comments and not the posts!

So what am I doing wrong?

C0ol_Cod3r
  • 909
  • 2
  • 15
  • 35
  • Possible duplicate of http://stackoverflow.com/questions/38135670/sort-query-results-by-nested-association-in-cakephp-3 – ndm Jul 21 '16 at 14:58
  • @ndm - tried giving your answer on that question (or the SQL bit of it) a go. It still come back with 'unknown column' for 'Comments.timestamp'. Maybe my models / tables are not set up right. But I can save / update the data, not sure what is wrong. – C0ol_Cod3r Jul 21 '16 at 15:17
  • `timestamp != Timestamp`. Depending on the used DBMS and configuration, identifiers can be case sensitive. Also you may need to clear your model cache if the field was added after the model has been created. – ndm Jul 21 '16 at 15:25
  • Thank you for pointing out your answer on the other post, but I just don't understand how it works. I can not get what I need to work. I cant see why I just cant use 'dot' syntax to access a model that is linked, ordering is meant to be a simple thing – C0ol_Cod3r Jul 21 '16 at 16:01
  • You can't because that kind of association is being retrieved in a separate query. Check the first paragraph of my answer. – ndm Jul 21 '16 at 16:17
  • I have got a workaround in place, a bit of a dirty hack. But I have put an 'updated' timestamp field on the Posts table, then I sort that! - A hack, but it makes it work for now - still lots of stuff to get working :) - Many thanks for the help – C0ol_Cod3r Jul 21 '16 at 16:18

0 Answers0