Kohana 3 ORM – Query Building and Key Word Search

I've been working on a project over the last couple of months using the Kohana 3 framework. I've been knee deep in code. When I'm in this mode, and I run into a problem that takes me a couple of hours, I figure it's worth a write up to save someone else some time.

I notice with the Kohana release the codegen module is now included in the release, so you don't have to download it from github. To use these examples you should turn on the ORM, database, and codegen module in the bootstrap.php file.

Your ORM code will be generated into a /temp directory when you go to url: http://localhost/yoursite/codegen, a file will be created for each database table. Move the database table files generated to your application/model directory, and we're ready to go.

Here's the scene, we have a populated MySQL database. In this database, we have a table called reports with various columns including: a rpt_id, user_id, a create_date, save_date, pub_date, and of course, the report, rpt.

We also have a users table with a user_id , dept_id, div_id. and username.

We're building a search engine for reports. The user puts a query in the input form. The form data goes to the controller, where the query is run for output to the results view.

The user puts his report id in the form, and we pull that particular report from the database. Let's write the basic ORM query.

$rptid = $_POST['rptid'];

$queryobj = ORM::factory ('report')
    ->with ('user')
    ->where('reports.rpt_id', '=', $rptid )

The "->with('user')" joins the two tables. Please see my article on table joins, you need to add the relationship to both model files. The "->find_all()" returns all reports that match the conditional where clause. Notice, the operator between the elements of the query is "->" and there is only one semicolon at the end.

What comes back in $queryobj is an object of the database record. You then assign "reports->rpt" into your data output array for outputting to the next view.

Let's start building queries. We'll make a change, and bring back more results.

$queryobj = ORM::factory('report')
->where ('div_id','=', $groupid )->

The query builder for "where" in Kohana takes three arguments. The table column, a logical operator, and the query variable from the user's input form. They are separated with a comma and have single quotes around two of the fields and sometimes the third depending on your variable. The "div_id" is in your user table, which is picked up automatically, with the user table join.

Normally, you have more than one criteria. Lets add another where clause.

$queryobj = ORM::factory('report')
->where ('div_id','=', $groupid )
->and_where ('reports.pub_date','>',$lastweek )
->order_by(	'pub_date', 'DESC' )

You can build a series of where clauses between "where_open" and "where_close". Each where clause after the first, is added with an "and_where" or "or_where". If there is an ambiguity with the column name, you can put the table name in front, like "reports.pub_date". $lastweek is a calculated variable of a MySQL date seven days ago.


Let's go to what took me a couple of hours, the key word search. We want to search through all the reports for any occurrence of a key word, $keywd, which is returned from the form.

First, to do a full-text, key word search, your database table must be saved in the MyISAM format. The normal table format in the MySQL versions above 5.5 is InnoDB. It is possible to convert the database table to MyISAM without losing your data.
There are responses to this in the MySQL forum.

The Key Word Search ++++++++

There is a query in MySQL that goes "WHERE MATCH (reports.rpt) AGAINST ($keywd)" which will go through your reports looking for the $keywd and output only those reports where the $keywd is present.

Initially, I tried to use this in a query by using the "DB::expr()" method in Kohana. An DB expression is the only way to use SQL functions within query builders.

Here's an example from the Kohana documentation:

$expression = DB::expr('COUNT(users.id)');

A DB::expr using "MATCH ( ) AGAINST ( )" didn't work with the Kohana ORM.

Thanks to the Kohana IRC channel, and Jeremy Bush, aka Zomber, who is a Kohana developer that has worked at building the ORM among his many other Kohana projects, I found out that the Kohana ORM is based on SQL92, and "MATCH AGAINST" is not supported by the Kohana ORM. I did notice that "LIKE" was supported by SQL92.

As a result, I was able to craft a query that will match a key word or key words with any column in your table, like so:

$queryobj = ORM::factory ( 'report' )
->with ('user')
->and_where( 'reports.rpt', 'LIKE', "%$keywd%" )

"LIKE" will do simple pattern matching. The key to making this work was putting the % signs around the search word or phrase. The % signs allow you to do "wildcard" searching, which is supported in many databases. The % at the end matches anything that starts with the $keywd and the reverse for the one at the front of the $keywd. Without the % sign, the query will not work properly, and yes, you need both sides.

As you can see, this took a while to figure out. The clues provided by the Kohana IRC channel #kohana at freenode, and, especially Zomber, were invaluable is setting me on the right path, if your stuck in the future, the IRC channel is a good source. They certainly can help point you in the right direction.


Kohana 3 ORM – Query Building and Key Word Search — 1 Comment