How to use group_contact in cakephp query?

Question

SELECT GROUP_CONCAT(usertype_name SEPARATOR ','), customer_name FROM `pre`.`customer` AS `Viewcustomer` WHERE `Viewcustomer`.`customer_status` = 0 AND `Viewcustomer`.`customer_type` = 'A1'  
GROUP BY `customer_id`  DESC

How to write this query in cakephp?

Kindly help me to write above query in cakephp. I don't know how to write group_contact in cakephp query. I am using cakephp 2x

Try Google or official website of CakePHP. – Bhavik Shah Apr 12 '16 at 07:50 — Bhavik Shah, Apr 12 '16 at 07:50

score 3 · Answer 1 · answered Apr 13 '17 at 21:26

3

In CakePHP 3.X

$query = $this->Model->find('all');
$query->select(['field1',
                'field2' => 'group_concat(field2)',
                'field3'])
        ->group('field2');

answered Apr 13 '17 at 21:26

Juan Giménez

39
5

score 1 · Answer 2 · answered Apr 12 '16 at 08:13

1

Try this

$this->ModelName->query('SELECT GROUP_CONCAT(usertype_name SEPARATOR ','), customer_name FROM `pre`.`customer` AS `Viewcustomer` WHERE `Viewcustomer`.`customer_status` = 0 AND `Viewcustomer`.`customer_type` = 'A1'
GROUP BY `customer_id`  DESC');

For reference check cakephp doc http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#model-query

answered Apr 12 '16 at 08:13

Akshay Sharma

1,042
1
8
21

I know this method .. Is it there any other option like $this->Model->find() – disha Apr 12 '16 at 09:34
no there are not. you can also try this $db = $this->getDataSource(); $db->fetchAll(' SELECT GROUP_CONCAT(usertype_name SEPARATOR ','), customer_name FROM `pre`.`customer` AS `Viewcustomer` WHERE `Viewcustomer`.`customer_status` = 0 AND `Viewcustomer`.`customer_type` = 'A1' GROUP BY `customer_id` DESC') ); – Akshay Sharma Apr 12 '16 at 09:38
In query I renamed GROUP_CONCAT(usertype_name SEPARATOR ',') as 'usertypename' . But in view part it is showing error .. Undefined index. – disha Apr 12 '16 at 09:52
plz print and check your array. I guess 'usertypename' will be at zero index not in your array. – Akshay Sharma Apr 12 '16 at 10:50

score 0 · Answer 3 · answered Feb 07 '23 at 13:47

it worked for me

$rooms = $this->Rooms->find('all', [
    'fields' =>
    [
        'roomName',
        'tiporoom_id',
        'id',
        'roomPriority'
    ],
    'contain' => 
    [
        'PhotosRooms' =>
        [   
            'sort' => 
            [
                'room_photos_priority' => 'DESC'
            ]
        ],
        'Typerooms' =>
        [
            'fields' =>
            [
                'typeName',
                'qtd_min',
                'qtd_max'
            ]
        ]
    ],
    'order' =>
    [
        'typeroom_id' => 'ASC',
        'roomPriority' => 'DESC'
    ],
    'group_concat' =>
    [
        'typeroom_id'
    ]
]);

How to use group_contact in cakephp query?

3 Answers3

Linked