CakePHP: Paginating Queries With GROUP BY clause and getAffectedRows()

In CakePHP v1.3 (and probably earlier), you need to work a little more to correctly paginate queries that use GROUP BY clause. This is because Model::find('count') doesn't get the record count right when with that. The most common solution I found online to get the correct count was to add a paginateCount() method on the model and use Model::getAffectedRows(), like what was done in this article: http://wiltonsoftware.com/posts/view/custom-group-by-pagination-and-a-calculated-field.

Pagination was fine until I encountered a case where the solution did not seem to work. I traced the problem down to the wrong count given by Model::getAffectedRows(). Digging inside CakePHP's Model source reminded me that Model::afterFind() callbacks (including those provided by any Behaviors attached to the model) are also executed. If there was at least one callback that queried the db, calling Model::getAffectedRows() after a Model::find('count') would give a different result.

To workaround this, I had to disable callbacks before doing the count:

	
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    $parameters = compact('conditions');
    $this->recursive = $recursive;
		
    if ( isset($extra['group']) ) {
        $extra['callbacks'] = false; // Disable callbacks
        $count = $this->find('count', array_merge($parameters, $extra));
        $count = $this->getAffectedRows();
    } else {
        $count = $this->find('count', array_merge($parameters, $extra));
    }

    return $count;
}

The solution varies depending on the callbacks: how they affect the number of records Model::find('count') returns and/or if the make any queries to the database that can change the count from Model::getAffectedRows(). One might have to detach some behaviors and leave others to run their afterFind() callback, or manually do the callback after the call to Mode::getAffectedRows() has been made. In my case, I can simply disable all callbacks.

 

CakePHP: Paginating Model Multiple Times in the Same View

At work, I encountered a scenario where we needed to paginate a model over different sets of conditions. Multiple calls to $this->paginate() on the controller somewhat works, but the problem is generating the pagination links and determining which set of conditions is being paginated.

Let's say, I have a Post belongsTo Category model relationship. I want to paginate post by Category in the same page.

Somewhere in the controller, I might have something like:

	
$phpCategoryPosts = $this->paginate('Post', array('Post.category_id' => 1));
$jsCategoryPosts = $this->paginate('Post', array('Post.category_id' => 2));

Then, to generate pagination links in the view, I might do something like:

// For PHP posts
if($this->Paginator->hasNext('Post')) {
    echo $this->Paginator->next('Next', array('model' => 'Post')); 
}
	
// For Javascript posts
if($this->Paginator->hasNext('Post')) {
    echo $this->Paginator->next('Next', array('model' => 'Post')); 
}

The paginator will just use the info from the most recent call to paginate() for that model in the controller. So if there are no PHP posts while there are 10 Javascript posts with 5 items per page, both PHP and Javascript posts will have a "Next" button. So how can CakePHP tell which is which?

My solution was to generate (on the fly) different models that I can use to paginate:

eval('class PhpPost extends Post {}');		
eval('class JavascriptPost extends Post {}');		
$this->loadModel('PhpPost');
$this->loadModel('JavascriptPost');
$phpCategoryPosts = $this->paginate('PhpPost', array('Post.category_id' => 1));
$jsCategoryPosts = $this->paginate('JavascriptPost', array('Post.category_id' => 2));

And in the view, just specify the which model to use:

// For PHP posts
if($this->Paginator->hasNext('PhpPost')) {
    echo $this->Paginator->next('Next', array('model' => 'PhpPost')); 
}
	
// For Javascript posts
if($this->Paginator->hasNext('JavascriptPost')) {
    echo $this->Paginator->next('Next', array('model' => 'JavascriptPost')); 
}
 

New /home

Hello there my little blog. It's been a quite a while since I used you. The projects/work I had for the couple of months really got my attention and interest. There were many times that I wanted to blog about some stuff I encounter in a project. But even before I am able write, I already find myself working at another project.

I was finally able to get a VPS to put you on. DigitalOcean's plan is really affordable and its features were comparable to other top VPS providers. Setup was a breeze because of their guide/tutorials, but had to rebuild several times (remind me to finish a working setup first before making my OWN customized settings). Do you like your new home? Hopefully, you'll no longer be alone - I can now put up other apps/stuff I'm working on the needs server tweaking that can't be done with shared hosting.

We also just moved to a new home, too. All of my work and efforts for the couple of years have paid off. But there still lot things to do and fix, I still may not be able to see you often.

 

DOMPDF : Missing Images and Unapplied Stylesheets

I'm currently using DOMPDF to convert an HTML page to PDF. The generated PDFs are fine, except that some styles doesn't seem to get applied and some images ended up missing. After setting $_dompdf_show_warnings to true to debug the process, I found out that relative paths with leading slashes are interpreted as absolute file paths in the local file system. For example, if my document root is at /var/www/html/ and the relative path of the stylesheet (in the html markup) is /css , DOMPDF interprets it as /css instead of /var/www/html/css. The same happens with the case of images.

The application is created with the Yii Framework and uses pretty urls (the usual stuff you see), so it will be almost impossible to use paths relative to the current directory (../). I needed to them to point relative to the top level (/), keeping the leading slashes.

Read the rest of this entry »

 

PHP 5 Reflection API and Custom-Built ACL for CodeIgniter

One of the projects I'm currently handling was built using CodeIgniter and uses Ben Edmunds Ion Auth for user authentication. I gave another programmer instructions on how to implement a controller-action based access control list (similar to one of CakePHP's ACL implementation). Though not exactly done according to my specs and required some fixes/enhancements, it was fit for consumption 🙂 .

While fixing/debugging the implementation, I had to whip up some function to list all available controllers and actions saving them into the appropriate tables required by the ACL system along with default permissions. I think I requested this feature when I gave out the instructions (no body would want to manually browser and enter all those actions).

Though the administration panel for setting controller-action permissions was easy to use, the client wanted the names of the actions more descriptive so the users have a clear idea on that an action does. The names were the actual action (method) name and should be left untouched, though. Some action names are self-explanatory (edit, send_mail, etc) but it does help if the user can get more info about it. Incidentally, a description field is provided but it is left empty for manual input. Now, the challenge was to find a way to eliminate manual entry of descriptions for each action.

Read the rest of this entry »

 

Using components inside a Cake shell

You can easily include and use models inside cake shells/tasks as you would normally do inside a controller - via the $uses class attribute. But the cake shell doesn't give you the same functionality for loading components. So how do you load and use them inside shells/tasks?

Read the rest of this entry »

 

jQuery Validation Plugin: Setting error message for remote method

The jQuery Validation Plugin, by default, provides a way to validate fields using server-side resource via Ajax call. This is through the use of the remote rule/method.

According to the documentation (or from how I understand the stuff written about it), the script response is evaluated as a JSON. Returning true implies that the field passed validation while return values such a false, undefined or null would mean it failed. The documentation also seemed to mean that I can return a string that will be used and displayed as its error message. Good, I'm running several server side validations on a particular field. It's best that I display the details on which validation check that field failed.

My script did return a string but it wasn't being used by the validation plugin - no error messages are being displayed. I knew, though, that validation failed since the form did not continue to submit. The PHP snippet kinda looked like this:
==

<br />
&lt;?php<br />
echo 'Ugh... that is not allowed';</p>

<p>

==

Peeking at the plugin and jQuery source took me to the jQuery.parseJSON() utility function. Reading the documentation for that function gave me a clue on what I was doing wrong. Echoing 'true' resulted to true and echoing 'false' resulted to false - as if the string was eval()ed. If I were to eval() 'some string', it would give me an error. But if I eval() '"some string"' (note the double quotes) instead, it evaluates as a string.

So, in order for my message to be parsed as string, I just wrap the output inside double quotes:
==

<br />
&lt;?php<br />
echo '"Ugh... that is not allowed"';</p>

<p>

==

And that fixed my problem. Note that you should enclose it ONLY with double quotes, not single quote. jQuery.parseJSON() requires the input to be a well-formed JSON string in order to parse it. For a valid JSON of a string, the string must be wrapped inside double-quotes (see http://json.org/).

 

jQuery Datepicker: Prevent hide on date select

A user from PHP User Group Philippines posted a question on the forum asking how to prevent the jQuery Datepicker dialog from closing upon selection of a date. The date picker had the button panel enabled where clicking "Done" closes the dialog. It seems that the Datepicker plugin didn't have a readily available way of enforcing the behavior.

Other users posted solutions - all making use of the onSelect callback. I tried to solve it too using the onSelect callback, but to no avail. I even tried to fool the datepicker to make it think that it was configured to be displayed inline (calling it on div instead of an input element makes it inline - always visible) but didn't work (even if it did, I wouldn't advice it since it is ugly). A solution from another user seemed to work but it was gave an error.
Read the rest of this entry »

 

Site Update

New look, same old content. Sigh Just testing out Facebook integration.

 

Testing…

Hmmm... blog seems to be working just fine. Just upgraded to the latest version of WordPress.

It's been more than a year since I last blogged on my homepage. I no longer remember how to use Textile :D. Have been trying to blog, been inspired to blog, but then my volatile thoughts just evaps away when starting to type. That, and my right hands sometimes hurts at the end of the day (hope it's not CTS ). Well, good thing my wife got me a Track Ball mouse:

It seems to lessen the strain on my hand.

Also, there's been quite a problem on my host so posting and managing my blog became a bit a pain in the arse. Which made me think of moving to Slicehost. But I guess that will have to wait until my finances stabilizes and my credit card application gets approved :P.

Let's see if I can get back to blogging this year. By the way, please do check on a blog post I made at the Promet's CakePHP Blog : CakePHP 1.2: Using Auth with ACL.

 
 
 

Resources