Can I sort children different than parent in CakePHP 3 threaded query?

Question

I have a questions regarding threaded comments. Is it possible to order comments DESC and child comments ASC from this query (or table level) or should I make a after query modification?

Below you can find my query that orders all to DESC.

```

$comments = $this->Comments
            ->find('threaded', ['order' => ['Comments.created' => 'DESC']])
            ->contain(['Users'])
            ->matching(
                'BoardItems',
                function ($q) use ($boardItemId) {
                    return $q->where(
                        [
                            'BoardItems.id' => $boardItemId
                        ]
                    );
                }
            )
            ->all();

```

score 2 · Accepted Answer · edited May 23 '17 at 10:28

On SQL Level

You should be able to apply the solution suggested in MySql: ORDER BY parent and child, which

uses COALESCE to group/sort the parents first
groups the children by testing for a non-NULL parent ID
sorts the grouped children

In your case you'd sort by created instead of id, ie something like

ORDER BY 
    COALESCE(Comments.parent_id, Comments.created) DESC, 
    Comments.parent_id IS NOT NULL, 
    Comments.created ASC

To build this in a proper query builder-ish fashion, you'd have to use the order() and orderDesc() methods, so that you can use query expressions, something along the lines of

$query = $this->Comments
    ->find('threaded');

$comments = $query
    // ->contain(...)
    // ->matching(...)

    // COALESCE(Comments.parent_id, Comments.created) DESC
    ->orderDesc($query->func()->coalesce([
        'Comments.parent_id' => 'identifier',
        'Comments.created' => 'identifier'
    ]))

    // Comments.parent_id IS NOT NULL
    ->order($query->newExpr()->isNotNull('Comments.parent_id'))

    // Comments.created ASC
    ->order(['Comments.created' => 'ASC'])

    ->all();

On PHP level

Sorting things afterwards would be an option too, for example using a recursive result formatter:

$sortChildren = function($row) use (&$sortChildren) {
    if (!empty($row['children'])) {
        $row['children'] =
            collection($row['children'])
                ->sortBy('created', SORT_ASC)
                ->map($sortChildren)
                ->toArray();
    }
    return $row;
};

$comments = $this->Comments
    ->find('threaded')
    // ->contain(...)
    // ->matching(...)
    ->order(['Comments.created' => 'DESC'])
    ->formatResults(function ($results) use ($sortChildren) {
        return $results->map($sortChildren);
    })
    ->all();

This would retrieve everything sort descending, and then sort all children arrays ascending by the created field. Similarily you could sort things before you output/use them in your views, depending on what exactly you're planning to do with the results.

If you want to keep things in the table, you could for example wrap this all up in a custom finder and/or retrieve the sort closure via a method on your table class.

Can I sort children different than parent in CakePHP 3 threaded query?

1 Answers1

On SQL Level

On PHP level