Daves dev warblings

For all the things I never remember

Installing and using pt-visual-explain

Why?

So I attended CakeFest this year and it was brilliant. One of the talks which I took at homework was the Profiling and Optimisation: A practical approach by Mark Story.

One the tools mentioned was pt-visual-explain, and I’ve been playing with it to see how it can help me better understand my queries and what is happening when I’m getting data.

I thought that I would document the process so I don’t forget it.

Install

Go grab your specific platform download of Percona Toolkit. Personally I used the tarball option for my work machine as it’s OS X.

Mac OSX

Here are the instructions for installation on a Mac. I’ve not tried it on my Ubuntu laptop, but when I do, I’ll update this post.

Then I unzipped the tarball into a folder. You’ll notice that there is a Makefile.PL, this is a Perl script for building the various executables you’ll need.

Make sure you have Perl installed, perl --version, which you should be default I think. Then it’s time to build the various executables.

1
2
3
4
5
$ cd percona-toolkit-2.2.10
$ perl Makefile.PL
$ sudo make
$ sudo make test
$ sudo make install

Then you’ll probably find some warnings or errors about a missing Perl package called DBD::MySQL. So let’s install this. I used this guide, but I will summarise it here.

1
$ perl -MCPAN -e 'shell'

If this is the first time you’ve used the Cpan module on your Mac you will be asked about configuring your Cpan set-up.

1
2
3
4
5
6
7
cpan> get DBI
cpan> get DBD::mysql
cpan> exit

$ sudo perl -MCPAN -e 'install DBI'
$ cd ~/.cpan/build/DBD-mysql-x.xxx/
$ perl Makefile.PL --testuser='daz' --testpassword='YOUR_PASSWORD'

Don’t include the --testpassword='' argument if your database doesn’t have a password.

1
2
3
$ make
$ make test
$ sudo make install

So now it’s time to run it and see what our queries look like.

1
$ pt-visual-explain --connect --host=localhost --user=root --database=myDatabase ~/Percona/MyExampleQuery.sql

You should get a nice visual output of what is going on with your query. At this point you can add indexes, change the query and keep running the visual explainer against it to see how things are changing.

Done

That’s it! Pretty simple really, apart from all that crap with Perl ;)

Sorting multi-dimension model data

Scenario

So often you have a large collection of collated data, probably with some calculated fields and you want to sort one of the dimensions by a certain value. Pretty hard using regular array methods, unless you start slicing arrays out of their dimension, sort them and inject them back in. We don’t want to do that because it’s fiddly.

Solution

So the solution I like to use is to use usort(). Which is a handy function allowing you to sort an array using your own function. Perfect if you want to sort a related models data.

Example

So for the example we’ll assume that we have a League model which hasMany LeaguesUser. We want to count the number of points a user has and order the data accordingly.

When returned from a Cake find() we’ll end up with related data in dimensions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
array (size=2)
  'League' =>
    array (size=9)
      'id' => string '1' (length=1)
      'name' => string 'Examples!' (length=9)
      'slug' => string 'examples' (length=9)
      'description' => string 'The league for people who like examples' (length=45)
      'cover' => string '1000x1000.jpg' (length=13)
      'image_dir' => string '1' (length=1)
      'join_code' => string 'da39a3ee5e6b4b0d3255bfef95601890afd80709' (length=40)
      'created' => string '2014-08-04 10:45:45' (length=19)
      'modified' => string '2014-08-11 14:54:34' (length=19)
  'LeaguesUser' =>
    array (size=2)
      0 =>
        array (size=5)
          'id' => string '1' (length=1)
          'league_id' => string '1' (length=1)
          'user_id' => string '2' (length=1)
          'admin' => boolean true
          'User' =>
            array (size=5)
              'id' => string '2' (length=1)
              'email' => string 'test@example.com' (length=16)
              'username' => string 'testuser' (length=8)
              'predictions' => int 2
              'correct' => int 0
      1 =>
        array (size=5)
          'id' => string '2' (length=1)
          'league_id' => string '1' (length=1)
          'user_id' => string '1' (length=1)
          'admin' => boolean false
          'User' =>
            array (size=5)
              'id' => string '1' (length=1)
              'email' => string 'testuser1@example.com' (length=20)
              'username' => string 'testuser1' (length=8)
              'predictions' => int 2
              'correct' => int 1

So let’s sort that LeaguesUser['User'] dimension by the number of correct predicitons.

Firstly, we’ll want to create a new private function sortByCorrect($a, $b) in our controller. Then we just need to sort using it in our currenct controller method.

The important thing to note is that the callable function passed to usort() is an array containing the current controller as the first item. usort($data, [$this, 'callableFunction']) without including $this you’ll get an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?php
public function view($slug) {
  $league = $this->League->find('first', [
      'contain' => [
          'LeaguesUser' => [
              'User' => [
                  'fields' => ['id', 'email', 'username', 'correct', 'predicitons']
              ]
          ]
      ],
      'conditions' => [
          'League.slug' => $slug
      ]
  ]);
  
  usort($league, [$this, 'sortByCorrect']);
  
  $this->set('league', $league);
}

private function sortByCorrect($a, $b) {
  if ($a['User']['correct'] < $b['User']['correct']) {
      return 1;
  } elseif ($a['User']['correct'] == $b['User']['correct']) {
      return 0;
  } else {
      return -1;
  }
}

// Rest of controller

Done

Go make a brew! Your work here is done.

Requiring CakePHP 2.x using Composer

Scenario

You have started your project using one of the 2.x downloads. Now you are using Composer to pull in your plugins, but you are still having to commit the framework to your repo.

So it’s time to remove your CakePHP lib and start requiring it as a dependancy instead.

I will assume you are fimiliar with Composer.

Solution

So it’s actually pretty straight forward. For the sake of this example, we will assume that you have a standard layout in your current project.

If you are not familiar with the current layout, take a quick look at the 2.5.3 tag to check the folders.

To see what we are aiming at, check out the Friends of Cake app-template.

If you don’t already have a composer.json file, then you’ll want to create that. composer init inside your project folder.

Adding the framework

Firstly we want to let Composer know that you want to require the framework, so let’s update the composer.json to add the following to your require section.

1
"cakephp/cakephp": "2.5.3"

You can pick your version here if you want to lock in the version, which I’d recommend. Or you could specify another version, such as 2.5.*. Check the Composer docs for more on versions.

When you composer update now it should download the framework for you into vendors/cakephp/cakephp.

Pointing to the correct core

The next task is to tell CakePHP that we have a new place for it to find the core. There are only really a few files you need to update.

  • app/webroot/index.php
  • app/Console/cake.php

app/webroot/index.php
You’ll want to update the ROOT, APP_DIR, TMP and the CAKE_CORE_INCLUDE_PATH constants.

1
2
3
4
define('ROOT', dirname(dirname(__FILE__)));
define('APP_DIR', 'app'); // I define this as a string because why not right?
define('TMP', ROOT . DS . 'tmp' . DS);
define('CAKE_CORE_INCLUDE_PATH', ROOT . DS . 'vendor' . DS . 'cakephp' . DS . 'cakephp' . DS . 'lib');

app/Console/cake.php
So that the console commands can still find the right lib, we need to update the include path here too.

1
ini_set('include_path', '..' . $ds . 'vendor' . $ds . 'cakephp' . $ds . 'cakephp' . $ds . 'lib' . PATH_SEPARATOR . ini_get('include_path'));

As you are running your shell tasks from within app we can use a relative path.

Testing

To check that the new setup is working you can run a Console/cake to check what the core value is.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ cd app
$ Console/cake

Welcome to CakePHP v2.5.3 Console
---------------------------------------------------------------
App : app
Path: /Users/david/Sites/example/app/
---------------------------------------------------------------
Current Paths:

 -app: app
 -working: /Users/david/Sites/example/app
 -root: /Users/david/Sites/example
 -core: /Users/david/Sites/example/vendor/cakephp/cakephp/lib

Done!

Feel free to delete your old lib folder as you now don’t need it. Have fun updating your applications version of CakePHP using Composer.

Creating a CakePHP project using Composer

Scenario

I want to start a new CakePHP project, but I don’t want to download anything. Ideally I want to create my application with the framework as a dependancy.

Solution

So firstly you’ll want to create yourself a project folder. For this example we’ll create a project to adopt a cat. We need to create ourself a project folder, make it into a Git repo so we can version our code, and then we can start adding our dependancies.

1
2
3
4
$ mkdir KittyMarket
$ cd KittyMarket
$ git init
$ composer init

So after the last command Composer will ask you about your project, so you can complete the details there. Be sure that you set your "minimum-stability": "dev" so that packages loading from dev-master will work.

Then you can start defining your dependancies. Firstly you’ll want cakephp/cakephp and set a version of the latest, currently 2.5.1.

After this I like to edit my composer.json to add some configuration, so that Composer will put things in the correct CakePHP place.

1
2
3
4
"config": {
    "vendor-dir": "vendors",
    "bin-dir": "vendors/bin"
}

Once you’ve added all your dependancies it’s time to make the app. Be aware that the ../vendors/bin might be different if you didn’t use the above amend to your composer.json.

1
2
3
$ mkdir app
$ cd app
$ ../vendors/bin/cake bake

This will copy in the framework skeleton.

Done!

Make a brew!

Filtering dates using CakeDC/Search

Scenario

I needed to do a search on a date field in my database which was stored as a DATE, but only filtering by month.

Solution

So I had already implemented the CakeDC/Search into my project, so all I needed to do was create a custom method to return the right conditions for the query.

My model already had the filterArgs setup.

1
2
3
4
5
6
7
8
9
/**
 * Setup default search filters
 *
 * @var array
 */
  public $filterArgs = [
      'day_type_id' => ['type' => 'value'],
        'month' => ['type' => 'query', 'method' => 'filterByMonth']
  ];

So I just added a type of query and passed in a method.

1
2
3
4
5
6
7
8
9
10
11
/**
 * Filter the pagination by month
 *
 * @param array $data
 * @return array
 **/
    public function filterByMonth(array $data = array()) {
        return [
            "DATE_FORMAT({$this->alias}.date, '%c')" => (int)$data['month']
        ];
    }

The $data array will contain all the fields setup in your model along with their values. So for me it looked like

1
2
3
4
[
    'day_type_id' => 3,
    'month' => '08' // Note the string type, hence why I cast to int
]

Done!

Make a brew!

Custom Pagination Helper links

Scenario

You are paginating a set of records but you want to use a custom url for that specific filter. For me this was pagination a set of news articles by category.

The url I wanted to use was /news/category/daves-awesome-category and then paginating results on /news/category/daves-awesome-category/page:3. However the Paginator helper didn’t want to play ball.

I had already created my route.

1
Router::connect('/news/category/:category/*', array('controller' => 'news_articles', 'action' => 'index'), array('category' => '[a-z0-9-]+', 'pass' => array('category')));

But instead of the expected links above, I was getting /news/daves-awesome-category/page:2 missing out my keyword from the url.

Solution

The Paginator helper options array to the rescue! You can actually configure the options of the helper right in the view. Such a simple fix.

Here is my pagination including the fix to adjust the url if a category is set.

1
2
3
4
5
6
if (isset($category)) {
  $this->Paginator->options['url'] = array('controller' => 'news_articles', 'action' => 'index', 'category' => $category['NewsCategory']['slug']);
}
echo $this->Paginator->prev('< ' . __('previous'), array(), null, array('class' => 'prev disabled'));
echo $this->Paginator->numbers(array('separator' => ''));
echo $this->Paginator->next(__('next') . ' >', array(), null, array('class' => 'next disabled'));

Done!

Make a brew, and probably have a biscuit too. Why not eh? You’ve earned it.

Dynamic validation in CakePHP

The scenario

You have two different forms which both submit data for the same model. Each of these forms has different fields, but both need to be validated before the data can be saved.

If you setup your model validation normally, both forms will fail to validate as they will be missing fields.

Solution

The method that I use to solve this is very simple. You can create two validation arrays and dynamically merge them together as and when you need them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
<?php
// Model/Post.php

/**
 * Setup the default rules here, these rules should be common to both forms 
 *
 * @var array $validate
 */
  public $validate = array(
      'title' => array(
          'one' => array(
              'rule' => 'notEmpty',
              'message' => 'Please enter a title',
              'required' => true
          ),
          'two' => array(
              'rule' => array('minLength', 10),
              'message' => 'Title must be more than 10 characters'
          )
      )
  );

/**
 * Validation rules for when editing a post
 * 
 * @var array $validatePost
 */
  public $validatePost = array(
      'content' => array(
          'one' => array(
              'rule' => 'notEmpty',
              'message' => 'Please enter some content',
              'required' => true
          )
      )
  );

/**
 * Validate Author
 * 
 * @var array $validateAuthor
 */
  public $validateAuthor = array(
      'author_id' => array(
          'one' => array(
              'rule' => 'notEmpty',
              'message' => 'Please select an Author',
              'required' => true
          )
      ),
  );

/**
 * Here we can check some conditions to see what validation we need
 * 
 * @return boolean
 */
  public function beforeValidate() {
      // We might want to check data
      if (isset($this->data['Post']['author_id'])) {
          $this->validate = array_merge($this->validate, $this->validateAuthor);
      }

      // Maybe only on an edit action?
      // We know it's edit because there is an id
      if (isset($this->data['Post']['id'])) {
          $this->validate = array_merge($this->validate, $this->validatePost);
      }

      // Perhaps we want to add a single new rule for add using the validator?
      // We know it's add because there is no id
      if (!isset($this->data['Post']['id'])) {
          $this->validator()->add('pubDate', array(
                  'one' => array(
                      'rule' => array('datetime', 'ymd'),
                      'message' => 'Publish date must be ymd'
                  )
              )
          )
      }

      return true;
  }

So now our validation array will contain a dynamic selection of rules based on conditions we choose. I prefer to use the array_merge() method because I find the validation rules easier to read and maintain.

You can find out more about the validator() in the book. Dynamically change validation rules

Success

Go and make a brew, you’ve just done some dynamic validation. Right in the model too, exactly where it should be. ggwp.

Merging Admin specific helpers

The scenario

You have a bunch of helpers which you are only using in the /admin section of your website. We don’t need to load these on the front-end part of the site.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
// app/AppController.php
  public $helpers = array(
      'Html',
      'Form',
  );

  public $adminHelpers = array(
        'NiceAdmin.Actions',
        'NiceAdmin.Boolean',
  );

public function beforeFilter() {
  if (isset($this->request->params['admin'])) {
      $this->layout = 'admin';
      $this->helpers = array_merge($this->helpers, $this->adminHelpers);
  }
}

Success!

Make a brew.

How to create hasManyThrough multi-selects

Updated 6th November 2013

The scenario

In your CMS you have a HABTM relationship between two models, something like Post and Tag. However you want to store some extra data about the Tag, such as who created it. This means that the relationship between the two models will in fact need to be a hasManyThrough and use a join model. You can read more about this type of relationship in the CakePHP CookBook.

You want to be able to select and save multiple tags when creating a Post. So the logical step here is to load a list of Tag items and display them in a multi-select element in our view.

The alternative scenario is using checkboxes to add a hasMany relation to a parent record.

The problem

When creating a multi-select there is no form field configuration which will allow the data to be formatted in a way which is compatible with any of the save() methods.

For example, Setting up your form using a multi-select such as $this->Form->input('tag_id', array('type' => 'select', 'multiple' => true)) will not allow you to numerically index your fields. This means that your data will look like the following.

1
2
3
4
5
6
array (size=1)
  'tag_id' => 
    array (size=3)
      0 => string '28' (length=2)
      1 => string '29' (length=2)
      2 => string '30' (length=2)

However this isn’t compatible, and must be transformed into a numerically indexed form such as this.

1
2
3
4
5
6
7
8
9
10
array (size=3)
  0 => 
    array (size=1)
      'tag_id' => string '28' (length=2)
  1 => 
    array (size=1)
      'tag_id' => string '29' (length=2)
  2 => 
    array (size=1)
      'tag_id' => string '30' (length=2)

This new data format can now be assigned to your join model and saved using saveAll(). However the dependency isn’t respected and as such, you need the hack for deleting the existing join model records as you can see below.

Solution

The models

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php

// Model/Post.php
$hasMany = array(
  'PostsTag' => array(
      'className' => 'PostsTag',
      'foreignKey' => 'post_id'
  )
);

// Model/Tag.php
$hasMany = array(
  'PostsTag' => array(
      'className' => 'PostsTag',
      'foreignKey' => 'tag_id'
  )
);

// Model/PostsTag.php
$belongsTo = array(
  'Post' => array(
      'className' => 'Post',
      'foreignKey' => 'post_id'
  ),
  'Tag' => array(
      'className' => 'Tag',
      'foreignKey' => 'tag_id'
  ),
);

The form in the view

If you want to use a multi-select field here you can use the following.

1
2
3
4
5
6
7
8
9
<?php
echo $this->Form->input(
  'PostsTag.tag_id',
  array(
      'type' => 'select',
      'multiple' => true,
      'selected' => Hash::extract($this->request->data['PostsTag'], '{n}.tag_id')
  )
);

If you would like to use checkboxes instead you can change the Form Helper to use checkboxes.

Important note
If you are using checkboxes and want to validate your data you will need to ensure that the hiddenField option is not false. Otherwise the data will not appear in the data array and you will not be able to validate it.

1
2
3
4
5
6
7
8
<?php
echo $this->Form->input(
  'Post.tag_id',
  array(
      'multiple' => 'checkbox',
      'options' => $tags // A list of tags fetched with $this->Post->Tag->find('list')
  )
);

The controller except

Controller method excerpt to show the usage of the function

1
2
3
4
5
<?php
// Here we are massaging the data in order to transform it
$this->request->data['PostsTag'] = $this->Post->PostsTag->massageHasManyForSaveAll($this->request->data['PostsTag'], 'tag_id', $this->request->data['Post']['id']);

$this->Post->saveAll($this->request->data;

The AppModel hack

In the AppModel we need to implement a hack to massage the data. You’ll notice that we call it on the join model, as above. This is important as the join model will have the correct relationships beteween the two models. We can use this to our advantage to find out the related keys for the delete.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php
/**
* Transform a set of hasMany multi-select data into a format which can be saved
* using saveAll in the controller
* 
* @param array $data
* @param str $fieldToSave
* @param int $deleteId
* @return array
*/
public function massageHasManyForSaveAll($data, $fieldToSave, $deleteId = null) {
  foreach ($this->belongsTo as $model => $relationship) {
      if ($relationship['foreignKey'] != $fieldToSave) {
          $relatedModel = $model;
          $relatedModelPrimaryKey = $this->{$model}->primaryKey;
          $relatedForeignKey = $relationship['foreignKey'];
      }
  }

  if ($deleteId !== null) {
      $this->deleteAll(array(
          $this->alias .'.'. $relatedForeignKey => $deleteId
      ));
  }

  if (is_array($data[$fieldToSave])) {
      foreach ($data[$fieldToSave] as $packageId) {
          $return[] = array($fieldToSave => $packageId);
      }
      
      return $return;
  }

  return $data;
}

Success

Make a brew!

How to use a Component to listen to Cake events

I wanted a sleek way to inject SEO meta tags into my project without a large overhead and lots of complicated finds and model joints. Thanks to jose_zap’s suggestion, I decided to learn about the Cake Events system.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<?php
/**
 * Component to find and load seo data and inject it into the view
 *
 * @author David Yell <neon1024@gmail.com>
 */

App::uses('CakeEventListener', 'Event');
App::uses('CakeEvent', 'Event');

class SeoComponent extends Component implements CakeEventListener {
  
/**
 * Setup the component
 * Called after the Controller::beforeFilter() and before the controller action
 * 
 * @param Controller $controller
 */
  public function startup(Controller $controller) {
      parent::startup($controller);
      $controller->getEventManager()->attach($this);
  }

/**
 * List of callable functions which are attached to system events
 * 
 * @return array
 */
  public function implementedEvents() {
      return array(
          'View.beforeLayout' => 'writeSeo'
      );
  }

/**
 * Inject the seo data into the view
 * 
 * @param CakeEvent $event
 * @return void
 */
  public function writeSeo(CakeEvent $event) {
      // Looking for vars set to the view isn't especially robust! This should probably call a behaviour method which goes and looks up data
      if (!empty($event->subject()->viewVars['content']['Content']['seo_title'])) {
          $event->subject()->viewVars['title_for_layout'] = $event->subject()->viewVars['content']['Content']['seo_title'];
      }
      
      if (!empty($event->subject()->viewVars['content']['Content']['seo_description'])) {
          $event->subject()->Html->meta('description', $event->subject()->viewVars['content']['Content']['seo_description'], array('block' => 'meta'));
      }
      
      if (!empty($event->subject()->viewVars['content']['Content']['seo_keywords'])) {
          $event->subject()->Html->meta('keywords', $event->subject()->viewVars['content']['Content']['seo_keywords'], array('block' => 'meta'));
      }
  }
}

Success

Make a brew!