Sohaib’s Blog - A Cake-PHP Web Developer, Pakistan

Using MySQL INNER JOIN in CakePHP Pagination

First of all, I've got to hand it over to Matt he really did a BBBIIIGGG favor to the CakePHP community by publishing his guide to advanced CakePHP Techniques. This guide / book will give a great insight into the framework to anyone who is a seasoned programmer and is picking up Cake for the first or so time. And I'm going to be floating ideas to compliment the advanced techniques and all in all promote good programming practices of what I'm aware of.

Now, I've seen a lot of shitty code when it comes to CakePHP. Yes, I've even seen mysql_query() calls in views ! ... yes, I've lived that day and kept my sanity in tact. But I can't blame the programmers too because obviously they were newbies and were under a lot of pressure to "get things going" by their blood sucking employers. Anyhoo ... this might be the subject of another post, BUT I really had to get it out of my system ... *phew* ... feel so light now

So, MySQL INNER JOINS ... when should you use them ? - simple answer: when you want to filter out data in your result set. And it's quicker than filtering out results in the "WHERE" clause. Don't have any metrics to show to support this conclusion right now, but I speak in the light of many tests I've conducted on large datasets. A more simpler theory is that the "WHERE" clause needs to filter out a lot more rows in a result-set obtained as a result of using "LEFT JOIN". CakePHP's logic however is sound to use LEFT JOIN as the intention is not to filter out the records, it's merely to include whichever records belongs to the conditions you supply. That's why it's "Containable" behavior is so cool (special thanks to Felix on that for maturing it and making a part of the Cake's core).

The more you familiarize yourself with Cake's datasource classes the better. The most excellent example was published on the bakery by Nate on how to use JOINs in CakePHP. I think this should be made part of the documentation too. This could actually make you get rid of "overriding" Controller::paginate() function. When you come to know about the flexibility offerred by the datasource class you love it even more - a simple example:

 
class PostsController extends AppController {
 
  public function by_tag ( $tag ) {
    /**
    * This will fetch Posts tagged $tag (say, 'PHP')
    */
    $this->paginate['Post'] = array(
      'limit' => 10
      , 'contain' => ''
      , 'conditions' => array(
       'Post.published' => 1
      )
      , 'fields' => array('Post.*', 'Tag.*')
      , 'joins' => array(
          array(
            'table' => 'posts_tags'
            , 'type' => 'INNER'
            , 'alias' => 'PostTag'
            , 'conditions' => array(
                'Post.id = PostTag.post_id'
            )
          )
          , array(
            'table' => 'tags'
            , 'alias' => 'Tag'
            , 'type' => 'INNER'
            , 'conditions' => array(
                "PostTag.tag_id = Tag.id AND Tag.name = '$tag'"
            )
          )
        )
      );
 
      $data = $this->paginate('Post');
      $this->set(compact('data'));
  }
}
 

This is just a simple example of what you can achieve by adding simple joins in your Model::find() conditions and of course in the paginate part. I've stretched it a bit further. I've actually used sub-queries and sub-joins, really complex stuff when paginating some complex data sets. Thanks to the 'joins' I never had to override the Controller::paginate() method ever. Just for the sake of example, let's say I want to retrieve posts tagged in 'PHP' and 'CakePHP' written by users who have a rating above 3. Of course this can be done in other ways, here is one using a sub-query join in CakePHP elegantly:

 
 
// work this out wherever you want it - in your model or controller
// but if I were you, I'd put this in my model
 
$join = "SELECT posts.id AS POST_ID FROM posts JOIN authors ON (posts.author_id = authors.id)";
$join = $join.' '."JOIN users_ratings ON (authors.user_id = users_ratings.user_id AND users_ratings.rating > 3)"
$join = $join.' '."WHERE 1=1";
 
// in your controller
$this->paginate['Post'] = array(
  'limit' => 10
  , 'contain' => ''
  , 'conditions' => array(
   'Post.published' => 1
  )
  , 'fields' => array('Post.*', 'Tag.*')
  , 'joins' => array(
      array(
        'table' => 'posts_tags'
        , 'type' => 'INNER'
        , 'alias' => 'PostTag'
        , 'conditions' => array(
            'Post.id = PostTag.post_id'
        )
      )
      , array(
        'table' => 'tags'
        , 'alias' => 'Tag'
        , 'type' => 'INNER'
        , 'conditions' => array(
            "PostTag.tag_id = Tag.id AND Tag.name IN('PHP', 'CakePHP')"
        )
      )
      , array(
        'table' => '('.$join.')'
        , 'alias' => 'FILTERED_RESULTS'
        , 'type' => 'INNER'
        , 'conditions' => array(
            "Post.id = FILTERED_RESULTS.POST_ID"
        )
      )
    )
);
 

And this will elegantly filter out the posts you need

Conclusion:  you can really write any kind of a query and really devise a condition based system that would add filters auto-magically. (I will present such a system in another post) - Remember, CakePHP is all about auto-magic ! ... which is actually the culmination of "convention over configuration" so use it to the fullest !

CakePHP Archivable Behavior

Alright folks ... yeh I know I've been out of the picture really long and me blog is looking deserted for real now. Anyhow, I've got a bunch of posts in the pipeline. Thanks to Ahmed of SoccerLens for convincing me to start posting again :).

Enough chit chat ... so the cool thing I bring you here my friends is this Behavior I just baked up for a client. The Archivable Behavior. I love CakePHP's behavior architecture and had Mariano Iglesias's SoftDeletable behavior in mind before baking this baby.

What it does ?

It simply puts the record you want to delete in another table. I see no use bloating my existing table by adding a "deleted" field, especially when it would need to go through this process many times. Imagine, how big your table would get when you simply soft delete a record and it just sits there and is rarely used. I've seen this kind of methodology run into trouble when you are search the table. MySQL has to go through a lot of unwanted, deleted records and extract the active ones, it slows down the search process, especially when you are using UUIDs.

Usage:

This example follows for a Model, say, "MyPosts".


public $actsAs = array('Archivable'=>array('table'=>'my_posts_archives'));

If you don't specify a the table key, it will simply look for the table, 'my_posts_archived'. The schema for the archives table is simple. It's the same as the table my_posts but it has an additional field, my_post_id. You're going to have to create that table yourself. If you're like me and use PhPMyAdmin it should be really simple by going into Table -> Operations -> copy.

Once you're done with setting up the table and attaching the Archivable behavior with your model, anytime you execute the statement $this->MyPost->del($id); it will simply move the record from my_posts table to my_posts_archives table.
You can also use $this->MyPost->unarchive($id); to achieve reverse, i.e. remove the archived record from the archive table and move it back to the main table. As simple as that !

Notes:

I've baked this on CakePHP 1.3.0.0 with PHP 5.2.4. Although this should run smooth on CakePHP 1.2.x too but if you are using PHP 5.3 you're gonna have to make some adjustments when objects are passed via reference in the code.

Download the Behavior

Have fun ya'all

Integrating MediaWiki With CakePHP

Hi Folks. As you might have guessed from the title, I was up trying to integrate MediaWiki with a CakePHP application of mine. Yeah, all hell did break loose while I was at it. As naive as it may sound, I was dreaming something like an API bridge between the MediaWiki and CakePHP. But like I said, it was a dream, and as the notion suggests, "so you wanna build an API bridge between MediaWiki and CakePHP in one night ?" - "DREAM ON SUCKER !" because you'll never find the time and the nerve to do it ... doh !

May be some day, if no one beats me to it, I'd get that bridge up and running. But till then let me explain a nifty way of doing this.

Your best chance to include a MediaWiki with your CakePHP application is via some RewriteRule magic in your .htaccess file. You simply need to specify which URL you want to be pointed to your wiki application. In my case, I simply wanted all http://mysite.com/wiki/ requests to map to /doc-root/wiki instead of /doc-root/app.

I usually don't keep the App folder type of structure in my CakePHP apps but this particular application was an exception. The concept is to:

"read the URL you want pointing to the MediaWiki and change your document root to the folder where you are keeping the MediaWiki"

Now, here is the file structure of my the application I was working on:

• / < app_name >
  • / branches
    • / sohaib
      • / app
      • .htaccess
    • / wiki
      • index.php
      • .htaccess
      • ....

Say, like me, you want to get all URLs with "/wiki" to be directed to the MediaWiki app, then a small change you'll make int the .htaccess file highlighted in red above would be:


RewriteEngine on
RewriteRule ^(.*)/wiki/(.*) wiki/ [NC]
RewriteRule ^(.*)/wiki/(.*) wiki/$1 [NC]
RewriteRule ^$ app/webroot/ [L]
RewriteRule (.*) app/webroot/$1 [L]


This tells Apache to first look for URLs containing /wiki - and redirect these to to the /sohaib/wiki folder where another .htaccess file awaits the request (highlighted in green above). This file is identical to the one you can find in /webroot folder of your app. You can copy paste this file to your wiki folder.


RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^(.*)$ index.php?url=$1 [QSA,L]


This connects your /wiki requests to the index.php file of your media wiki. And thats it as non-technical as I could be in trying to explain what you can do. I am not going into the exaplaination of the code in the .htaccess files. You'd be able to find good resources via google. But if you're like me, you'll settle for the documentation on mod_rewrite on Apache.org

But let me tell you, this is a very simple demonstration of what you can achieve with mod_rewrite ... this thing is COMPLEX and way too much voodoo, especially when regular expressions come into play. So be careful, it's addictive - Hope this helps anyone out there trying to integrate a MediaWiki with their App. Over and OUT !

Bindable now Containable Behavior in CakePHP Core

Behold! One of the coolest behaviors "Bindable" is now included in CakePHP's core. Many thanks to Mariano and Felix for their efforts and including this in CakePHP's core as "Containable" behavior. I have found this behavior to be the most useful by far. I remember the nightmares I used to forgo in controlling model bindings in CakePHP 1.1, specially when coding DustJacketReview. We had to pull up so much data for a single page that we were forced to write custom queries to make it as light weight as possible. But ever since I shifted to CakePHP 1.2, things got a lot better. I have to admit that I had to find some workarounds for some situations when working with CakePHP 1.1 and specially for multiple Ajax paging within a single page. But now these nightmares are over!

Although one thing I am still concerned about is the built in pagination. It's brilliant no doubt but still I can't control the model bindings when paginating any model. I observed the code and found out that it instantiates a new instance of the model when paginating it, so even if I set the bindings to persistent, it has no effect as they are not persistent with the new object. Anyhow, a post on this matter is in the pipeline, I still need some more information to actually formally voice the issue on cake's trac. Currently I have a workaround that's working really good for me. Let's just say that I wanted more than "paginate" function to be specified within a model, and I got it

CakePHP 1.2 Pagination explained

Today I spent a lot of time trying to figure out how to have CakePHP 1.2 pagination with all the flexibility. Because all I wanted to do was to filter the records of my model AND also unbind ALL associated models. I found the information in tits and bits a bit scattered around. So I've decided to make one spot for it, at least for my own future reference. Here is what I've understood:

There are 2 parts you have to take care of, one for the component and the other for the helper. Here is a generic example for the Post model,

/** function index () in your Posts controller **/
$this->paginate['Post'] = array(
'limit'=> 20,
'order'=> "Model.field ASC", // for example i.e.
'fields'=> array(
'Post.id',
'Post.name', //.....
),
'url'=> array(
'controller'=> 'Posts', // could be $this->name
'action'=> 'index', // could be $this->action
'created'=> '02-02-2008',
'active'=> 1, // ... modelField/Value pairs you'd like to set as filters
)
);
$posts = $this->paginate(
'Post',
array(
         "Post.created = '02-02-2008' AND Post.active = 1"
      )
);
$this->set('posts', $posts);
$this->set('paginationUrl', array('url'=>$this->paginate['Post']['url']));

This will let the component do the math and calculate how many rows are being returned. Since pagination component executes a simple "findAll()", so it will treat all modelField / Value pairs it finds in the URL key as search criteria i.e. conditions just like you'd specify conditions as array in any "findAll()" call.

To keep or switch between the modelField / Value pairs throughout the pages, you can pass these as URL parameters. So your URL should look kind of like this:

http://yourhost/cake_root/controller/action/page:2/modelField:value/modelField:value/ ....

Now for the pagination helper, we'd use $paginationUrl array that we set for our view:

/** after rendering all posts **/
pr($posts);   /* or you can use Cake's built in function to display the $posts array  */
echo $paginator->prev("<< Prev", array("url"=>$paginationUrl));
echo $paginator->numbers(array("url"=>$paginationUrl));
echo $paginator->next("Next >>", array('url'=>$paginationUrl));

And that's it, now the paginator links would render your given modelField / Value pair. But there is or "was" a downside about it that my db fields would be seen via the URL. For example, the URL for the above action would be like this:

root/controller/action/page:2/created:02-02-2008/active:1

and that was unacceptable for me, so I created a masking scheme for my field / value pairs. May be I'd make a component out of it, but that won't be as generic as you'd still have to pass the Masks / Fields pairs as an array.

Ahh !! so what about unbinding associated models ? ... well ... what I do is that I use the cool unbindAll function by Othman Ouihibi. I've been using it since CakePHP 1.1 days. But there's a behavior that supports this functionality too on the Bakery, pretty cool. So I just unbindAll models except the ones I need before every paginate call in my controller and that does the trick. Any suggestions / comments would be highly appreciated.

This article was originally made by collecting information from the comments here, so I'd strongly advise anyone who wants to know advance paging to read that article.

HAPPY BAKING !!!

A Full blown CakeBlog is upcoming!

Alright! so now I've got this blasted itchy craving to deploy this blog in CAKE!!!! MMMMmmmMMMmmmMMMmm ! Let's see how well it goes.

Yeesh! sometimes I think I might become really fat (im fat already) - to be honest, all that cake is like an AFRO-DEZIAC for me ..... yum yum !

CakePHP Google Geocoder

This is a very cool Geocoder component for google maps. You can use this directly without any hassle to integrate in your CakePHP projects. All you'd have to do is give it addresses and it will return you their respective Longitudes and Lattitudes from google. So no hassle no nothing ! - simply plug it in and PLAY AWAY !!!

View it here