Kohana 3 ORM tutorial and samples

June 25th, 2011 | Kohana | 6 Comments »

ORM allows pushing and pulling of data from the database without the need of typing long sql statements. It turns database queries into objects.

How do you implement it in Kohana?

To get started, enable the module in bootstrap. Open bootstap.php and uncomment database and orm.

Kohana::modules(array(
	'database' => MODPATH.'database',
	'orm' => MODPATH.'orm',
));

Creating the database table
For our example, lets create students, subjects, students_subjects(pivot), phones and projects table.

Table structure for table `phones`

CREATE TABLE `phones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(15) NOT NULL,
  `student_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Table for phone number' AUTO_INCREMENT=7 ;

INSERT INTO `phones` VALUES(1, '123456', 1);
INSERT INTO `phones` VALUES(2, '654321', 1);
INSERT INTO `phones` VALUES(3, '78901', 2);
INSERT INTO `phones` VALUES(4, '10987', 3);
INSERT INTO `phones` VALUES(5, '12567', 3);
INSERT INTO `phones` VALUES(6, '76521', 3);

Table structure for table `projects`

CREATE TABLE `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `student_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `projects` VALUES(1, 'Word Digs', 'Like archaeologists, students can unearth words, discovering the history of words, finding synonyms, antonyms and spin-off words.', 1);
INSERT INTO `projects` VALUES(2, 'Happy Endings', 'Language arts projects for gifted students which include researching the history of words and discovering ways to create fractured fairytales while looking through the lens of critical literacy is an exercise in creativity that can scaffold on previous knowledge, other subject matter as well as lead to critical inquiry.', 2);
INSERT INTO `projects` VALUES(3, 'Literary Techniques and Free verse', 'Gifted students enjoy the challenges of creative use of language; therefore including language arts projects in critical literacy lessons is a wise decision.\r\n', 3);

Table structure for table `students`

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Students information' AUTO_INCREMENT=8 ;

INSERT INTO `students` VALUES(1, 'Ana', 'Smith');
INSERT INTO `students` VALUES(2, 'Marie', 'Browne');
INSERT INTO `students` VALUES(3, 'Mark', 'Thompson');

Table structure for table `student_subjects`

CREATE TABLE `student_subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL
  PRIMARY KEY (`id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Students subjects';

INSERT INTO `student_subjects` VALUES(1, 1);
INSERT INTO `student_subjects` VALUES(1, 2);
INSERT INTO `student_subjects` VALUES(1, 3);
INSERT INTO `student_subjects` VALUES(2, 2);
INSERT INTO `student_subjects` VALUES(2, 4);
INSERT INTO `student_subjects` VALUES(3, 1);
INSERT INTO `student_subjects` VALUES(3, 4);

Table structure for table `subjects`

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Suject information' AUTO_INCREMENT=5 ;

INSERT INTO `subjects` VALUES(1, 'Math', 'Mathematics is the study of quantity, structure, space, and change.');
INSERT INTO `subjects` VALUES(2, 'History', 'History is the discovery, collection, organization, and presentation of information about past events. ');
INSERT INTO `subjects` VALUES(3, 'Science', 'Science is a systematic enterprise that builds and organizes knowledge in the form of testable explanations and predictions about the world.');
INSERT INTO `subjects` VALUES(4, 'Logic', 'Logic is the formal systematic study of the principles of valid inference and correct reasoning. Logic is used in most intellectual activities, but is studied primarily in the disciplines of philosophy, mathematics, semantics, and computer science.');

Things you need to know
1. Model and Table(plural of model) name should be the same. Example, my model name is student so the table name must be students. If your table name doesn’t match the model name, add _table_name property inside the model.

protected $_table_name = 'strange_table_name';

2. Remember that an underscore means new directory. So Model_Firstname_Secondname means you have to put your model script inside model>firstname directory.

Setup Models

First, set up models for student, subject, student subject, project, and phone.

Create a file inside application>classes>model. Since our table is students, name it student.php. Do the same to subjects, projects and phones.

For the student_subject model, it is a bit different since the table name has two underscores. Create a student directory and inside it, create a subject.php file.

class Model_Student_Subject extends ORM {}

Basics ORM samples
To create an instance of the the student model,

$student = ORM::factory('student');

To load a student’s record,

$student = ORM::factory('student', 1);
echo $student->firstname

The above code means that it selects a student record with a primary id of 1. The result is Ana. If you pass 2 as the 2nd argument, the result is Marie.

To find a student’s record with where()(condition) and find() method,

$student = ORM::factory('student')
	->where('firstname', '=', 'Marie')
	->find();

To check if the model has been loaded,

echo ($students->loaded()) ? 'loaded' : 'not loaded';

To insert a record,

$student = ORM::factory('student');
$student->firstname = 'Jane';
$student->lastname = 'Adams';
$student->save();

To update a record, pass the student id (primary key) to the second argument.

$student = ORM::factory('student', 2);
$student->firstname = 'Mariella';
$student->save();

To delete a record, first check if the record exists by using loaded() method,

$student = ORM::factory('student', 4);
if($student->loaded()) {
	$student->delete();
	echo 'deleted';
} else {
	echo 'record does not exists';
}

Finding records and iterating over the result,

$students = ORM::factory('student')->find_all();
foreach($students as $student)
{
	echo $student->firstname;
}

Relationships
1. One-to-one
In one to one relationship, an example is the students and projects table. A student can only have one project and a project belongs only to one student.

Add the following line in the user model.

protected $_has_one = array('project' => array());

Add the following line in the project model.

protected $_belongs_to = array('student' => array ());

To access the student data, simply use $student->fieldname. To access the project data, use the $student->project->fieldname.

$students = ORM::factory('student')
	->find_all();

foreach($students as $student)
{
	echo $student->firstname.": ".$student->project->title;
}

2. One-to-many
In one to many relationship, an example is the students and phones table. A student can have many phone numbers and phone number/s belong only to one student.

Add the following in the phone model.

protected $_belongs_to = array('student' => array());

Add the following in the phone student model.

protected $_has_many = array('phone' => array ());

In our controller,

$phones = ORM::factory('phone')->find_all();
foreach($phones as $phone)
{
	echo $phone->student->firstname.': '.$phone->number;
}

3. Many-to-many
In many to many relationship, our example is the students and subjects table. I assume that a subject an be assigned to many students and many students can have many subjects.

Add the following in the student_subject model:

protected $_belongs_to = array
(
	'student'	=>	array(),
	'subject'	=>	array()
);

Add the following in the student model.

protected $_has_many = array
(
	'subject'	=>	array('through'	=>	'student_subjects')
);

Add the following in the subject model.

protected $_has_many = array
(
	'student'	=>	array('through'	=>	'student_subjects')
 );

In our controller,

$student_subjects = ORM::factory('student_subject')
			->find_all();
foreach($student_subjects as $student_subject)
{
	echo $student_subject->student->firstname.': '.$student_subject->subject->title;
}