1

By using group in cakePHP, we can get the unique records but I need to know how many records are duplicated? I have tried ->formatResults, ->virtualFields, 

'fields' => array('Table.column', 'count(*) as virtualColumn'),
'group' => array('Table.column HAVING COUNT(*) >= 1'),

But this won't help either. I need to get this :

Array ( [A] => 10 [B] => 20 [C] => 30 )

Below is the code which worked for me :

$conditions = ['TableA.AnyColumn'=> $id];
$this->loadModel('TableA');

$this->TableA->belongsTo('TableB', [
    'className' => 'TableB',
    'propertyName' => 'table_b',
    'foreignKey' => false,
    'joinType' => 'LEFT',
    'conditions' => ['TableB.any_column = TableA.any_column', 'TableB.some_other_column = TableA.some_other_column']
]);

------- Here I'm getting the count -------

$count = $this->TableA->find()->where($conditions)->group(['columnToBeGroupBy']);
$count = $count->select(['columnToBeGroupBy', 'count' => $count->func()->count('TableA.columnToBeGroupBy')])->toArray();

------- Here I'm getting the count -------

$this->paginate = [
    'contain' => ['TableC' => ['fields'=>['fields required']]],
    'conditions' => $conditions,
    'group' => 'TableA.columnToBeGroupBy',
    'limit' => 20,
    'order' => ['TableC.modified' => 'DESC']

];

$result = $this->paginate($this->TableA);

$data['result'] = $result;
$data['pagination'] = $this->request->getParam('paging.TableA');
$data['count'] = $count;

Is there any other way in which I can reduce my code like using virtual fields or anything?

rahim.nagori
  • 636
  • 8
  • 20
  • The `key => value` syntax always binds the value for casting/escaping/quoting. Use the functions builder: **https://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions** – ndm Jun 18 '19 at 14:11
  • Thank you for response @ndm. I got the counting now. I need to attach that counting to result and return it to view and I'm unable to do it. – rahim.nagori Jun 18 '19 at 14:27

2 Answers2

0

did you try

$this->loadModel('Articles');
$query = $this->Articles->find()->where($conditions)->group(['anyColumn']);
$query->select(['count' => $query->func()->count('Articles.anyColumn')])->toArray();

$this->paginate($query);
rajith1986
  • 33
  • 7
  • 1
    Yes thank you for responding. I did try this and this worked as I mentioned above. Let me share my paginate. Please see the question above. – rahim.nagori Jun 20 '19 at 13:25
-1

You can try this:

In controller in Paginator you can use like this:

  $itemads = $this->paginate($itemads, ['contain' => ['Departments', 'Stores'],'group'=>'upc','fields'=>array("upc_count"=>"COUNT(`upc`)")]);
Lokendra Singh Panwar
  • 503
  • 9
  • 27