Full-Text Search

CakePHP’s database abstraction is just one of the reasons to use this powerful framework for agile web development. However, it is not always obvious how to build complex queries. I recently had to implement a MySQL full text search against a music database that searched on titles, descriptions and tags. Full text search is a quick, and not too dirty way to make a pretty decent search, although it does come with a caveat, as described in the MySQL documentation:

    Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXTcolumns.

It did take a little bit of experimentation to get the syntax for the query correct in CakePHP as the query is a little different from the regular “SELECT field FROM table WHERE somevalue = somevalue“.

So, for this example I will use a MyISAM table called pages and search against the title, content and tags fields for the term” jquery web development”. You should substitute that term with your dynamically passed value in the code below.

Firstly I built the conditions for my query into a “params” array.
$params['conditions'] = array(
‘MATCH(Track.title,Track.description,Track.tags)
AGAINST(“‘ . $kw_list . ‘” IN BOOLEAN MODE)’
);

    $params= array(‘conditions’ => array(
    ‘MATCH(Page.title,Page.description,Page.tags)
    AGAINST(“jquery web development” )’ ));

Finally a call to CakePHP’s native find method will perform the search:

    $this->Page->find(‘all’,$params);

One last note: MySQL full text search can be powerful when used correctly and a potential bottleneck when poorly implemented. I suggest reading the documentation for further information and more advanced usage.

CakePHP find conditions

CakePHP find conditions (plus find all, find list, and find count)

CakePHP find FAQ: Can you share some "CakePHP find conditions" examples?

One thing you need to get used to when working with CakePHP is the CakePHP find method. To that end, I thought I'd put together this page of CakePHP find conditions and find query examples.
CakePHP find conditions

To get started, here's a list of the possible CakePHP find conditions you can use:

type      

• 'all'
• 'first' (default)
• 'list'

conditions

• array containing the find (select) conditions as key/value pairs
• default (null)

fields    

• array specifying which fields should be retrieved in the resulting select query
• default (null)

order

• sql 'order by conditions. field name must be followed by ASC or DESC
• default (null)

page      

• page number, used for paged data
• default (null)

limit

•  a limit on the number of results returned, like 'select * from orders limit 20'.
• default (1)

offset

• sql offset value (i haven't used this query much myself, but i think it refers to skipping X number of rows returned in a query)

recursive

• the cakephp recursive value, relating to associated model data

CakePHP find condition examples

Given that reference information, let's take a look at some CakePHP find conditions examples.

First, a very simple CakePHP find query that retrieves all records from the Post model (probably not something you'll want to do in a production application):

$this->Post->find('all');

Next, a "CakePHP find all" query with one find condition:

$this->Post->find('all', array('conditions'=>array('User.id'=>5)));

A CakePHP find query with one "not equal to" find condition:

$this->Post->find('all', array('conditions'=>array('User.id'=>'<> 5')));

A find query with multiple CakePHP find conditions:

# this is a little lame, but i'm trying to avoid dates
$this->Post->find('all', array('conditions'=>array('User.id'=>1, 'Post.id'=>'> 50')));

A CakePHP find query that uses all the find function parameters:

# TODO - i'm not sure that this is right; i think 'fields' is supposed to be an array
$this->Post->find('all',
                  array('conditions'=>array('User.id'=>5),
                        'fields'=>'Post.name',
                        'order'=>'Post.id ASC',
                        'limit'=>20,
                        'recursive'=>0));

A CakePHP find conditions query using a date:

# note: you can search for date or datetime fields by enclosing the table's field name
#       in the SQL DATE() function.
$this->Post->find('all',
                   array('conditions'=>array('User.id'=>5,
                   'DATE(Post.date)'=>'CURDATE()')));

# TODO demonstrate "date >" and "date <" conditions

CakePHP find conditions arrays with ORDER BY examples:

array('order'=>'date ASC')
array('order'=>'date DESC')
array('order'=>'User.id DESC')

A collection of other CakePHP find conditions/examples:

These CakePHP find examples are lines of code that would be used in an OrderController class:

$this->Order->find('all');
$this->Order->find(null, null, 'date DESC');
$this->Order->find('all', array('conditions'=>array('User.id'=>1)));
$this->Order->find('all', array('conditions'=>array('User.id'=>array(1,2,3,4))));
$this->Order->find('all', array('conditions'=>array('User.id'=>'<> 1')));
$this->Order->find('all', array('conditions'=>array('User.id'=>1, 'DATE(Post.date)'=>'CURDATE()')));
$this->Order->find('all', array('order'=>'date ASC', 'limit'=>20, 'recursive'=>0);

Here are some CakePHP find examples from the CakePHP retrieving your data book page:

$params can contain all these:

array(
  'conditions' => array('Model.field' => $thisValue), //array of conditions
  'recursive' => 1, //int
  'fields' => array('Model.field1', 'DISTINCT Model.field2'), //array of field names
  'order' => array('Model.created', 'Model.field3 DESC'), //string or array defining order
  'group' => array('Model.field'), //fields to GROUP BY
  'limit' => n, //int
  'page' => n, //int
  'offset'=>n, //int  
  'callbacks' => true //other possible values are false, 'before', 'after'
)

Here's a CakePHP function showing several different CakePHP find examples:

# find('first', $params) syntax

function some_function() {
  ...
  $this->Article->order = null; // resetting if it's set
  $semiRandomArticle = $this->Article->find();
  $this->Article->order = 'Article.created DESC'; // simulating the model having a default order
  $lastCreated = $this->Article->find();
  $alsoLastCreated = $this->Article->find('first', array('order' => array('Article.created DESC')));
  $specificallyThisOne = $this->Article->find('first', array('conditions' => array('Article.id' => 1)));
  ...
}

CakePHP find count examples

Here's a CakePHP find count example:

# find('count', $params)

# more cakephp find count examples
function some_function() {
   ...
   $total = $this->Article->find('count');

   $pending = $this->Article->find('count',
                                    array('conditions' => array('Article.status' => 'pending')));
   $authors = $this->Article->User->find('count');
   $publishedAuthors = $this->Article->find('count',
                                             array('fields' => 'DISTINCT Article.user_id',
                                                   'conditions' => array('Article.status !=' => 'pending')
   ));
   ...
}

CakePHP find all examples

Here is a list of some CakePHP find all examples:

# find('all', $params) syntax

# a cakephp find all example
function some_function() {
   ...
   $allArticles = $this->Article->find('all');
   $pending = $this->Article->find('all',
                                    array('conditions' => array('Article.status' => 'pending')));

   $allAuthors = $this->Article->User->find('all');

   $allPublishedAuthors =
       $this->Article->User->find('all',
                                   array('conditions' => array('Article.status !=' => 'pending')));
   ...
}

CakePHP find list examples

Here are some CakePHP find list examples, useful for creating select boxes:

# find('list', $params) syntax

function some_function() {
   ...
    $allArticles = $this->Article->find('list');
    $pending = $this->Article->find('list', array(
        'conditions' => array('Article.status' => 'pending')
    ));
    $allAuthors = $this->Article->User->find('list');
    $allPublishedAuthors = $this->Article->find('list', array(
        'fields' => array('User.id', 'User.name'),
        'conditions' => array('Article.status !=' => 'pending'),
        'recursive' => 0
    ));
   ...
}

See the CakePHP retrieving your data book page for many more CakePHP find examples, including:

    find threaded
    find neighbors
    findAllBy
    findBy
    query
    field
    read
    More complex CakePHP find examples