I am working on a CakePHP project these days, today i ran into a different problem with CakePHP. Sometimes i feel that there are small-small things that are sometimes missed by the Cake developers, but thank God that there are some workarounds available to them.  Here is the problem statement that i encountered, i had a table in which i was having duplicate records and i wanted to show only distinct records in the data grid which was using the CakePHP paginator class to create a paginated data grid.

Now to show only the distinct rows i used the group by clause to eliminate the duplicate records, the records were coming fine but when i saw the paging it was not working right at all. The problem was that the paginator’s “paginateCount” function was also considering the “Group By” clause while doing the counting of the total records. This was crazy.

I searched through Google to get a solution to that and found this link, where people have already reported a his error to the cake developers. The solution that worked for me was that i needed to override the “paginateCount” function and use a different function in my model to get the correct number of total records. I added this function to my model and whola it worked for me. Here is the function that needs to be added model to get things right.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
		$parameters = compact('conditions', 'recursive');
 
		if (isset($extra['group'])) {
			$parameters['fields'] = $extra['group'];
 
			if (is_string($parameters['fields'])) {
				// pagination with single GROUP BY field
				if (substr($parameters['fields'], 0, 9) != 'DISTINCT ') {
					$parameters['fields'] = 'DISTINCT ' . $parameters['fields'];
				}
				unset($extra['group']);
				$count = $this->find('count', array_merge($parameters, $extra));
			} else {
				// resort to inefficient method for multiple GROUP BY fields
				$count = $this->find('count', array_merge($parameters, $extra));
				$count = $this->getAffectedRows();
			}
		} else {
			// regular pagination
			$count = $this->find('count', array_merge($parameters, $extra));
		}
		return $count;
	}