0

I'm developing a social application with cakephp 3.

I'm working on the messanging function right now. It works this way, that on the left side one gets displayed the users, who one has conversations with and in the middle is the conversation.

I want the users to be sorted by last messaging date. Meaning, if Peter was the last one I wrote to, then Peter should appear on top and so on.

I have the following query:

$query = $this->Messages
    ->find('all', [
        'fields' => [
            'MAX(`messages`.`max_date`)',
            'id',
            'sender_id',
            'reciever_id',
        ]
    ])
    ->where([
        'sender_id = ' => $active_user_id
    ])
    ->orWhere([
        'reciever_id = ' => $active_user_id
    ])
    ->group([
        'sender_id',
        'reciever_id'
    ])
    ->order([
        'id' => 'DESC'
    ]);

For performance reasons I added a grouping by sender_id and reciever_id, since I don't want to recieve all messages, but only find the users, that I wrote with so far.

The problem is that the group-statement destroys the sorting. So I thought about a max-aggregate-function to preserve the order.

However I get the following error-message by the cakephp framework:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX(`messages`__`max_date`), Messages.id AS `Messages__id`, Messages.sender_id A' at line 1

So does anyone know, what is wrong with my query? I gave my best to follow the example in the documentation.

Why does it say messages__max_date and not messages.max_date?

ndm
  • 59,784
  • 9
  • 71
  • 110
ArchLinuxTux
  • 840
  • 1
  • 11
  • 28
  • Not entirely sure, since I'm not very familiar with `mysql`, but I would think `date` is a reserved key word. – HoneyBadger Jan 11 '16 at 09:23
  • http://stackoverflow.com/questions/27854333/cakephp-3-using-sqlserver-as-datasource-server-bake-error-database-error/27859881#27859881 – ndm Jan 11 '16 at 09:26
  • please look at the edit. So I removed the AS date and renamed to column in mysql from date_time to max_date. I still get an error. So the problem is not a reserved keyword I guess. – ArchLinuxTux Jan 11 '16 at 09:38

1 Answers1

0

That's simply not how you define computed fields and correspondig alias, this needs to be done in a key => value fashion, ie

'fields' => [
    'max_date' => 'MAX(`Messages`.`date_time`)',
    // ...
]

which would result in an SQL fragment like 

MAX(`Messages`.`date_time`) AS `max_date`

See Cookbook > Database Access & ORM > Query Builder > Selecting Data

Also as a tip, you might want to use expressions instead of simple strings, so that CakePHP can properly create platform specific SQL in case required. MAX isn't affected AFAIR, so this is just a general hint.

$query = $this->Messages->find();
$query
    ->select([
        'max_date' => $query->func()->max($this->Messages->aliasField('date_time'))
        // ...
    ])
    // ...

See Cookbook > Database Access & ORM > Query Builder > Using SQL Functions

ndm
  • 59,784
  • 9
  • 71
  • 110