how to sort array date wise in cakephp

Question

I have 2 table booking and message , now I want to show booking request and message in inbox at a time.

$this->paginate = array(
            'conditions' => $conditions,'limit' =>10,
        'order'=>array('Booking.created'=>'DESC'));
    $bookings = $this->paginate('Booking');

    $this->paginate = array(
            'conditions' => $conditions,'limit' =>10,
        'order'=>array('MessageDetail.created'=>'DESC'));
    $messages = $this->paginate('MessageDetail');

i have merge both table data ( array_merge($bookings, $messages); ) now i want to it sort date wise (or any conditions)

Array
(
[0] => Array
    (
        [Booking] => Array
            (
                [id] => 4
                [host_id] => 21
                [place_id] => 10
                [room_id] => 13
                [user_id] => 12
                [message_detail_id] => 16
                [created] => 2013-04-23 14:44:03
                [accept_date] => 
                [cancel_date] => 
            )

    )

[1] => Array
    (
        [Booking] => Array
            (
                [id] => 3
                [host_id] => 21
                [place_id] => 10
                [room_id] => 13
                [user_id] => 12
                [message_detail_id] => 13
                [created] => 2013-04-15 14:10:59
                [accept_date] => 2013-04-15 14:40:47
                [cancel_date] => 
            )

    )


[2] => Array
    (
        [MessageDetail] => Array
            (
                [id] => 17
                [message_id] => 2
                [user_id] => 12
                [sender_id] => 21
                [unread] => 0
                [created] => 2013-04-24 12:11:47
            )

    )

[3] => Array
    (
        [MessageDetail] => Array
            (
                [id] => 15
                [message_id] => 2
                [user_id] => 12
                [sender_id] => 21
                [booking_id] => 3
                [unread] => 0
                [created] => 2013-04-15 15:01:12
            )

    )

  )

Thanks in advance.

@YogeshSaroya Do you have an association between Booking and Message? By the sounds of it, this is probably something you would want the database to do for you instead of manually doing within PHP? — Sam Delaney, Apr 25 '13 at 14:52
What @SamDelaney said in the previous comment is probably the way to go. If you post your original queries to retrieve the data and the relationships between the Models, someone would probably be able to help you more. Adding a sql tag to the question might also help attract the attention of some sql gurus on here. — obsirdian, Apr 25 '13 at 15:29

score 0 · Answer 1 · edited May 23 '17 at 11:43

Option #1: Create a third Model named "BookingAndMessage". You could use the Model's afterSave method (on both Booking and Message) to create a duplicate record in your new model. You could then query the BookingAndMessage in the proper sort order.

Option #2: To solve the problem, as it stands, you will want to use PHP's usort function (also explained here PHP Sort a multidimensional array by element containing date).

<?php

$BookingsAndMessages = array_merge($bookings, $messages);

function date_compare($a, $b)
{   
   $modelKeyA = array_key_exists('Booking',$a) ? 'Booking' : 'MessageDetail';
   $modelKeyB = array_key_exists('Booking',$b) ? 'Booking' : 'MessageDetail';
   $t1 = strtotime($a[$modelKeyA]['created']);
   $t2 = strtotime($b[$modelKeyB]['created']);
   return $t1 - $t2;
}

usort($BookingsAndMessages, 'date_compare');
// Would sort the merged records by `created` date
?>

The downside to option 2 is, you are going to have to create rules for each field (and sort direction).

score 0 · Answer 2 · answered Oct 26 '21 at 15:17

Perhaps you should Union your queries first, the Order.

$bookings = $this->Bookings->find('all', [
        'conditions' => $conditions,'limit' =>10,
]);

$messages = $this->find('all', [
        'conditions' => $conditions,'limit' =>10
]);

$data = $bookings->union($messages);
// This works if your first selected field is the one you want to sort. 
$data->epilog('ORDER BY 1 ASC');

reference: How do you modify a UNION query in CakePHP 3?

how to sort array date wise in cakephp

2 Answers2