mysqlcakephp

one query for all array data! result is by separating for each id of array


I am searching all names for each transaction id. Eg., id 1 have user A, B. id 2 have user A. id 3 have user A, C.

I can get user names which belong to each Id like that

foreach ($Ids in $id){
    $query = [
            'fields' => [
                'USER.name'
            ],
            'joins' => [
                [
                    'type' => 'LEFT',
                    'table' => 'users',
                    'alias' => 'USER',
                    'conditions' => [
                        'TransactionUser.user_id=USER.id'
                    ]
                ]
            ],
            'conditions' => [
                'TransactionUser.transaction_id' => $id
            ]
        ];
    $this->find('all', $query);
}

But the problem is I don't want to loop query and want to do with only one query. So I try like this.

        $query = [
                'fields' => [
                    'USER.name',
                    'TransactionUser.transaction_id'
                ],
                'joins' => [
                    [
                        'type' => 'LEFT',
                        'table' => 'users',
                        'alias' => 'USER',
                        'conditions' => [
                            'TransactionUser.user_id=USER.id'
                        ]
                    ]
                ],
                'conditions' => [
                    'TransactionUser.transaction_id' => $Ids
                ]
            ];
        $this->find('all', $query);

It's work in cakePHP and result is like that.

Array
(
    [0] => Array
        ( ([name] => A), ([transaction_id] => 1))
    [1] => Array
        ( ([name] => B), ([transaction_id] => 1))
    [2] => Array
        ( ([name] => A), ([transaction_id] => 2))
    [3] => Array
        ( ([name] => A), ([transaction_id] => 3))
    [4] => Array
        ( ([name] => C), ([transaction_id] => 3))
)

But I want data like that somehow.

Array
(
    [0] => Array
        ( ([name] => A, B), ([transaction_id] => 1))
    [1] => Array
        ( ([name] => A), ([transaction_id] => 2))
    [2] => Array
        ( ([name] => A, C), ([transaction_id] => 3))
)

I try to use 'group' in query but it's not work well. I'm newbee in cakePHP query. Please help me if you know howto.


Solution

  • I just want to know some Query keyword of cakePHP for this case to be smart but it's look like not have. So just loop result for desired array.