Implementing MySql View in CakePHP For Typeahead Search

View works like a table, but it is not a table. It never exists; it is only a prepared SQL statement that is run when you reference the view name. A programmer can write VIEWs that limit the dataset both horizontally and vertically, ensuring that only the data that is required is queried for. Recently I Had to implement a search option for one of my developed site. The requirement was to show typeahed results from 5 tables. I used jQuery Ui – Autocomplete‘s Remote with caching to fullfil this requirement. My ajax call back result must fast enough as user writes on the textbox and result is showing. So for my first query return I used MySql View. Then start I firedup my Cake Bake. When I go to model creation i dont see my view’s name. I thought I have made some mistake – So I start looking into my view creation sql, my database configuration. But all seems ok. Then I clear cache by deleting app/tmp/cahe/models/. Yet no luck. Then I start poking around codes on cake folder. Then i found what I have been dreading.

On cake/libs/model/datasources/datasource.php line 613

function listSources() {
    $cache = parent::listSources();
    if ($cache != null) {
        return $cache;
    }
    $result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']) . ';');

    if (!$result) {
        return array();
    } else {
        $tables = array();

        while ($line = mysql_fetch_row($result)) {
            $tables[] = $line[0];
        }
        parent::listSources($tables);
        return $tables;
    }
}

So CakePHP does not support MySql view!? No it does. But views are not available for Bake :(. Anyway you have to create model, controller and view files old fashioned manually.

So my view sql is

CREATE VIEW searches AS 
select videos.id AS id,videos.video_title AS Name,'videos' AS TYPE from videos 
union 
select users.id AS id,concat(users.first_name,' ',users.last_name) AS Name, 'users' AS TYPE from users 
union 
select tag_sub_categories.id AS id,tag_sub_categories.name AS name,'tag_sub_categories' AS TYPE from tag_sub_categories 
union  
select shows.id AS id,shows.title AS title,'shows' AS TYPE from shows union select announcements.id AS id,announcements.title AS title,'announcements' AS TYPE from announcements;

Mysql UNION is used to combine the result from multiple SELECT statements into a single result set. UNION is quite handy as you can see I can have all the values from different table in one result set. I also used a virtual field TYPE for tracking were the values from.

Now create a file app/models/search.php and add the following code

class Search extends AppModel {
	var $name = 'Search';
	var $useTable = 'searches';
	var $primaryKey = 'id'; 
	var $useDbConfig = 'default';
}

You have to use $primaryKey otherwise when you to use find(‘list’) as described Here.

Now let’s create out controller file app/controllers/searches_controller.php

class SearchesController extends AppController {

	var $name = 'Searches';
	var $paginate = array(
		'limit' => 10,
	);	
			
	function search_json() {
		$search_keyword=$this->params['url']['term'];
		$results = $this->Search->find('all',array( 
					'conditions'=>array('Name LIKE '=> "{$search_keyword}%"),
					'limit'=>8
				));
		$return_array=array();
		foreach	($results as $result):
			$temp['id']=$result['Search']['id'];
			$temp['label']= (strlen($result['Search']['Name']) > 30)? substr($result['Search']['Name'], 0, 30). '...' : $result['Search']['Name'];
			$temp['value']=$result['Search']['Name'];
			$return_array[]=$temp;
		endforeach;	
		echo json_encode($return_array);
		exit;		
	}
}

For our search function

<script>
$(function(){
$.ui.autocomplete.prototype._renderItem = function( ul, item){
  var term = this.term.split(' ').join('|');
  var re = new RegExp("(" + term + ")", "gi") ;
  var t = item.label.replace(re,"<strong>$1</strong>");
  return $( "<li></li>" )
     .data( "item.autocomplete", item )
     .append( "<a >" + t + "</a>" )
     .appendTo( ul );
};	
	var cache = {},
		lastXhr;
	$( "#search_ahead" ).autocomplete({
		minLength: 2,
		source: function( request, response ) {
			var term = request.term;
			if ( term in cache ) {
				response( cache[ term ] );
				return;
			}

			lastXhr = $.getJSON( "<?php echo $html->url(array("controller" => "searches", "action" => "search_json"));?>", request, function( data, status, xhr ) {
				cache[ term ] = data;
				if ( xhr === lastXhr ) {
					response( data );
				}
			});
		}
	});
});
</script>
    	<?php echo $this->Form->create(null, array('url'=>"/searches/show_results.html", 'type'=>"file", 'id'=>'change_status', 'inputDefaults' => array('label' => false,'div' => false)));?>
        <table width="230" cellpadding="0" cellspacing="0" border="0">
            <tr>
                <td width="170"><input name="search_ahead" id="search_ahead" type="text" class="input-box2" style="width:160px; height:22px;" value="" /></td>
                <td width="60"><?php echo $this->Form->end('/css/images/frequency-search.gif');?></td>
            </tr>
        </table>
        <?php ?>

This function also highlight the search text on the result text.

About Zakir Hyder

This entry was written by .

23. March 2012 by Zakir Hyder
Categories: CakePHP, MySql, PHP, Web Development | Tags: , , , , , , , , | Comments

Comments

  1. […] Implementing MySql View in CakePHP For Typeahead Search View works like a table, but it is not a table. It never exists; it is only a prepared SQL statement that is run when you reference the view name. A programmer can write VIEWs that limit the dataset both horizontally and vertically, ensuring that only the data that is required is queried for. Recently […] […]

  2. patrick says:

    Hi Zakir, which version of Cake are you using? I am using 2.1.0 and I was able to bake views that had been created in MySQL: the issue was that some table were not named following the cake convention, that is pluralized. So what I did was to create views that had the proper naming convention and that were mere selct * statements. Worked fine as Bake created the appropriate model. Then I opened the newly created model php files and added the appropriate $useTable variables. That meant that now cake would go to the table from which the view was created and work properly.

  3. Zakir Hyder says:

    I am using CakePHP 1.3. For retrieving data CakePHP 1.3 does well with view, but it has no support for creating new records. Did you try to insert data on the view?

  4. patrick says:

    Thanks for the precision.
    Actually, I have not tried to insert data on the view. I just needed to bake models for table that where not named after the cakephp convention, so I used sql views to have a pluralized version of those tables. Now,let me try to insert data in a view and I’ll come back to you.

  5. patrick says:

    So I just tried and it works on CakePHP 2.1.0 . What I do is I create a view in mysql:
    CREATE ALGORITHM = UNDEFINED VIEW `userstoos` AS SELECT *
    FROM `users`

    Then I just enter at the command prompt
    “cake bake all” to create the appropriate model (Usertoo). After that I browse to the “add” action of UsertooController and jsut create a new user. And it actually adds a new record to the table users. That is the view “userstoos” now is just a kind of proxy to “users”.
    Now if you check on Oracle’s MySQL reference (http://dev.mysql.com/doc/refman/5.5/en/view-updatability.html) you’ll realize that they may be many reasons why CakePHP could not use your view to update or insert new records in the underlying tables: 1) because your view is built from the combination of columns from different tables, which makes it so that there isn’t a one-to-one relationship between your view columns and the columns of the underlying table. Therefore, since a view update can only update one table, your update will most likely fail.
    2) A view can be used to update an underlying table if only the view does not contain derived columns. Your sql code contains “concat(users.first_name,’ ‘,users.last_name) AS Name” .
    These are two reasons why it failed. There are some more.
    After looking at the lib\Cake\Model\Datasource\Database\MySQL.php file (in version 2.1.0) from line 184, we can see that cake/libs/model/datasources/datasource.php itself does not prevent CakePHP to access views.
    Thanks for your post Kazir. That made me realize that some kind of views are not updatable nor insertable and that the issue comes from MySQL workings and not from CakePHP.

  6. patrick says:

    Actually to be more clear, the problem with inserting in the view happened because:
    1) your view is built from a combination of columns from different tables and
    2) contain derived columns as in “concat(users.first_name,’ ‘,users.last_name) AS Name” then there isn’t a one-to-one relationship between your view columns and the columns of the underlying table, the update or insert will fail.

  7. Zakir Hyder says:

    Thanks patrick for detail and in-depth explanation on view.