Kohana 3 ORM – Table Joins, a Working Example

I have been writing an application using Kohana 3 which, if you have read my reviews, is an excellent framework with piss-pour documentation, and still is.  Nevertheless, I find it to be an excellent framework once you figure it out, so I can't help myself, I bit the bullet, and I'm using it.

One of the things I've been working with is the ORM, Object Relational Mapper, which turns database sql into php objects, and is supposed to make the PHP developers life a lot easier.   I'm amazed that there is a lot of forum articles and limited documentation on the ORM, but none puts it all together.  The documentation gives you pieces, and you have to figure out how to piece everything you need to do together to use it.

This article tries to put things together and give you what you need to do to get your Kohana 3 ORM up and running.  I'm going to move fast until we get to the good parts.

First off, we need to get set up.  Go into your bootstrap.php file and turn on the following modules: database, orm, and codegen.

You will not find the codegen module in the initial bootstrap file or in Kohana 3.  Download this module from Github, install it in your module directory, turn it on in the bootstrap file.  Then follow the codegen documentation, and generate all your ORM files into a temporary directory.  When codegen is done generating your ORM code you will find a file for each table in your database. Move all these files to your application model directory.   If you change your database, you can quickly regenerate your ORM code with the codegen module, nice.

To get your ORM up and running you will need to keep two things in mind.  You will acquire your database information out of the database in your controller, but to configure your queries, you have to edit your model ORM files, especially for table joins.  Table joins is the main reason I wrote this article,  there is currently no documentation that talks about doing table joins.  Let's get started.

I wanted to join four tables: reports, users, depts, and divs.  Reports have an author, or user, and a user is in a department and a division.  The tables are all related in the database with primary and foreign keys.

The data from the query that I wanted to output was to list all reports from the users in a large organization in the last seven days.  The reports would be ordered, or grouped by, dept, div, and publication date descending.

Follow along in your mind, as I set this up. Reports only have one user.  Open up the report.php file in an editor.  This is the file for the report table in your application > model directory.  Make sure the primary key matches your table at the top of the file. We want to add the following line to the file under the primary key:

protected $_belongs_to = array
  'user' => array ('foreign_key' => 'user_id' )

We are saying that the report belongs to a single user, who is identified by the foreign key, user_id;

Open the file for the User table in your model directory and add this under the primary key:

protected $_has_many = array
  'report' => array ('model' => 'report', 'foreign_key' => 'user_id' )

This says that a user has many reports, that these reports are found in the report table, and the foreign key is user_id.  We now have laid the foundation for the relationship between reports and users in the ORM.

What about department and division? These are simple look up tables.  As long as we have user.php open in the editor, add:

protected $_belongs_to = array
  'dept' => array ('model' => 'dept', 'foreign_key' => 'dept_id' ),
  'div' => array ('model' => 'div', 'foreign_key' => 'div_id' )

These lines say user belongs to a dept and div and where to find the files along with the foreign keys.

Now open dept.php and put this line in the file:

protected $_has_many = array
  'user' => array ('foreign_key' => 'dept_id' )

A department has many users. Do the same for division in div.php file changing the name to div_id.

Ok, we have the relationships between our tables described in each of the ORM files in the model directory and now we can go ahead and write our ORM in the controller.

Open up the controller for the page you want to display. Here's what is not in the documentation to date.  How to do a table join in Kohana 3 using ORM.

$reports = ORM::factory ( 'report' )
->with ( 'user' )->with ( 'dept' )->with ( 'div' )
->where('pub_date' , '>' , "$lastweek")
->order_by('dept_id')->order_by( 'div_id')->order_by('pub_date', 'desc')
->find_all ();

Note this is one long query I chopped into separate lines for legibility.

Pretty easy once we get our relations set in the ORM model files.  Let's go over it.

We make our $reports object from the reports table, joined with the users, dept, and div tables.  The where part says give me the publication who's date is within the last 7 days.

As a bonus, the algorithm for $lastweek is a little tricky, so let's look at that, too. The date needs to be formatted like it's stored in the database for comparison purposes:

$weekago = time() - (7 * 24 * 60 * 60);
$lastweek  =  date('Y-m-d', $weekago) ;

Order_by, or grouping, is first by dept, then div, and then date descending, like we wanted.

The final command, find_all, initiates the query and brings back the data.

One more thing, to get the data out of our now loaded $reports object, you need to do a foreach loop, here's the example:

$tomarr = array ();
foreach ( $reports as $report )
  $tom = "UserID: " . $report->user_id;
  $tom .= ", Week: " . $report->pub_wk;
  $tom .= ", Report ID: " . $report->rpt_id;
  $tom .= ",Username: " . $report->username;
  $tom .= ", FirstName: " . $report->user->fname;
  $tom .= "Dept_Name: " . $report->user->dept->dept_name;
  $tom .= "Div_Name: " . $report->user->div->div_name;
array_push ( $tomarr, $tom );

Looks simple now, but without the documentation it took me a good day to set all this up.  Now, that I've done it, the rest of the ORM in my application should be pretty simple.  Getting the initial syntax is the hard part.


Kohana 3 ORM – Table Joins, a Working Example — 10 Comments

  1. If you have to do table joins you are using the wrong technology. You don’t need ORM, you need SQL.

    Stop using this stilted ORM syntax to mimic what SQl was capable of doing decades ago, stop spoon-feeding chopped up queries to an API that exists because developers are too lazy to learn how data is properly stored or how to manipulate it using the real interface the RDBMS was written for.

    Look at what happens after you send that hacked-up stuttered query — behind the scenes it re-assembles it back together again, into real SQL. Because it realizes you are too lazy or stupid or too much of a hack to actually put together a valid SQL statement involving more than 1 table that will return what you need.

    This ORM table join stuff is fine for all you blog post 2-table script kiddies out there. But for a real web site with complex business logic this is nuts. ORM table joins are for those who are willing to spend inordinate amounts of time to learn how to not learn real SQL.

    I won’t even get into the legibility of this ORM table join crap. ORM table joins are for SQL illiterates. If you can’t tell a statement from a fragment, let alone an elegant statement from a fool’s yawp, I suppose it must seem to be all the same to you.

    Even so, there is no more a legit place in this profession for people who don’t or can’t or won’t learn basics than there is for a doctor who deigns to learn germ theory. The modern practice of medicine is rooted in germ theory. The modern practice of web development is rooted in data.

    Your client can lose every last line of code in your app, including all backups — and they can survive as a business entity. They can rebuild, they can throw a ton of developers in a death march to get something out the door that will keep their business going. It’s possible. But if they lose every last record in their database, all data wiped out — they are out of business. Game over. Instantly. No amount of money or manpower will make it whole again. Because business is based on data.

    A dev who doesn’t ‘get’ SQL is like a doctor who doesn’t ‘get’ germs.

    A dev who can’t or won’t work with SQL is a quack. A dev who doesn’t understand this is both a fool and a quack.

      • Easy. In Kohana 3.2, use:

        $item = ORM::factory(‘item’,’other_db’)

        By leaving the second paramater empty, ORM will use the default db in the config file.

      • Forget my last post. I wasn’t thinking. You can set the database in the model using the $_db_group protected variable:

        protected $_db_group = ‘alternate’;That’s directly from the Kohana 3.2 documentation. Sorry about the confusion

    • While I agree that every developer should at the minimum
      have an understanding of SQL, it does not nullify helpers like an ORM.

      Not every project is an enterprise endeavour. ORM is great for small to medium
      sites with smaller budgets or quick turnaround times and you always have the
      option to write and execute SQL as well.

      I use ORM for quick and easy data validation and storage and SQL for reading as
      ORM is overkill and not the best for complex queries.

      • I agree with you. However, I would say a developer should have more then a minimal understanding of SQL. And Object Relational Mapping (aka ORM) allows the data in the database to be easily accessible as an object. This is extremely helpful when writing OOP. I add methods in my Model that has the entire SQL string written out and others using ORM depending on my need and the complexity of the query sting. 

        One thing I like about ORM is when I create the object, it will “know” if I need to insert or update a table and relationships “automagicly”. For example, I’ll create the object with:

        $items = ORM::factory(‘item’,$id);

        Later I can add values and save with:

        $items->stuff = $stuff;

        No big deal, right? Here to cool part that I like; if $id is null or empty, when I call $items->save() it will insert a new record in the database. However, if $id contains a value, $item->save() will update the record in the database with that id. Using SQL strings, I would need to surrond it with “if” statements to check to see if $id contains a value and then write two separate query stings, one for the insert and one for the update. 

        I also disagree that ORM is not for an enterprise environment. I’ve use ORM on many enterprise environments and was in fact introduced to ORM while working at an web advertisement distribution agency. 

        I think that what footrois is complain about is actually the query builder and I agree with him when developers solely rely on it to build all their queries. Too many times I’ve had to fix issues when they’ve used the query builder and lost control of the query they were trying to build. It make subselects or unions very complicated that are easier to build and read using a beautified query string.

  2. Thanks so much; this makes sense but isn’t easy without documentation. 

    Do you know if there’s an easy way to do many-to-many relationships?  Say you’re tracking the hobbies of your company’s users, represented by the tables “hobbies” (id, hobby_name) and “users_hobbies” (user_id, hobby_id).  I’d like to simplify that for the controller so it doesn’t have to know about both tables.  

    • The ORM has to know the relationships between your tables, so you can use it in the controller. In Kohana 3 it’s done in both directions in your model files. That is, both table files in your model directory need to be told about the other table. In the case of many-to-many relationships, you should have a third table in your database, called a junction table, to link one table’s id’s with the other table’s id’s. The codegen module should have generated a model file for the junction table. How to set the ORM up in Kohana was described fairly well in the ORM doc on Github in the last section located here: https://github.com/jheathco/kohana-orm/wiki

  3. Thanks for being the one place in the 923750123 google results for Kohana ORM info that actually explains real examples fully. Your posts are much appreciated!