ORM stands for Object-relational mapping which in our case means that database data and it’s relationships will be represented in an object oriented abstraction. To put it more simply it will allow us to use tables as if they were classes and omit the writing of redundant tasks like updating and deleting.
Let’s assume we have a fairies table that keeps information about fairies in 3 columns: id, name and interests. Without ORM we would have to write a Fairy class that would manage adding, removing and updating this table. ORM does exactly this with just 2 lines of code:
1
2
3
4
5
6
7
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
}
After that we can add a fairy like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//Create a new fairy
$fairy=$pixie->orm->get('fairy'); //shorthand for 'new Fairy_Model'
$fairy->name='Tinkerbell';
$fairy->interests='Picking flowers';
//Save her to the database
$fairy->save();
//And remove her
$fairy->delete();
ORM relies heavily on a naming convention. It expect the name of the Model to be in a singular clause and the name of the table to be in plural. It will also take into account the namespace of the class, so \App\Model\Forest\Fairy class will map to the forest_fairies table by default. PHPixie also expects the name of the PRIMARY KEY to be id and it will use the ‘default‘ connection to the database. It is really easy to change this behaviour though:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
//Specify the PRIMARY KEY
public $id_field='fairy_id';
//Specify table name
public $table='pixies';
//Specify which connection to use
public $connection = 'test';
}
Retrieving fairies from the database is practically the same as in basic Database Queries. The only difference is that you need to end you query with find() (for single row searches) or find_all (for multiple results) instead of execute(). You can use count_all() to count the number of objects.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
//Getting a single fairy
$fairy=$pixie->orm->get('fairy')->where('name','Tinkerbell')->find();
echo $fairy->name.' likes: '.$fairy->interests;
//Getting 4 fairies ordered by name
$fairies=$pixie->orm->get('fairy')
->order_by('name','asc')
->limit(4)
->find_all();
foreach($fairies as $fairy){
echo $fairy->name.' likes: '.$fairy->interests.'\n';
}
//Get number of fairies
echo $pixie->orm->get('fairy')->count_all();
Find() will return an object if it doesn’t find a row you were searching for. To check if the row was found you need to call loaded() method. The object is considered loaded if it was fetched from the database, meaning that it already exists in the system. Calling save() on loaded objects will result in updating the row otherwise a new row will be inserted to the database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$fairy=$pixie->orm->get('fairy')->where('name','Trixie')->find();
if($fairy->loaded()){
//Update fairy if she already is in database
$fairy->interests='Picking flowers';
}else{
//Create her if there's no such fairy
$fairy->name='Trixie';
$fairy->interests='Picking flowers';
}
$fairy->save(); //Will add her to the table, or update if she exists;
If you have large amounts of data to fill (such as form input) in you can use the values() method and pass an associative array to it, like this:
1
2
3
4
5
6
7
8
9
10
11
$pixie->orm->get('fairy')
->values(array(
'name'=>'Tinkerbell',
'interests'=>'Singing songs'
))->save();
In very rare cases you will want to tell ORM that you filled all necessary data and that that row of data already exists in the database, then you can also pass true as the second parameter to values() and it will set the object to be considered loaded.
Removing works in a very similar way to find(). Calling delete() will remove the loaded row from the database, and if you wish to delete all rows that match a certain criteria use delete_all().
1
2
3
4
5
6
7
8
9
10
11
//Removing a single fairy
$fairy=$pixie->orm->get('fairy')->where('name','Trixie')->find();
$fairy->delete();
//Deleting all fairies with id > 3
$pixie->orm->get('fairy')->where('id','>',3)->delete_all();
Now all this seems really familiar to just using the Database module. Relationships is where ORM truly shines.
Relationships
Suppose our fairies live in trees and we have an additional trees table with columns like id and name. The Tree Model will look like this:
1
2
3
4
5
6
7
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
}
Now we need to add a tree_id column to our fairies table and specify the relationship. there are 3 types of relationships in ORM: belongs_to, has_one and has_many.
A Belongs_to relationship specifies that each row has a single counterpart in other table. In our case each fairy lives in a single tree. Here is how you specify this:
1
2
3
4
5
6
7
8
9
10
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
protected $belongs_to=array('tree');
}
That’s it! Now we can use it like this:
1
2
3
4
5
$fairy=$pixie->orm->get('fairy')->where('name','Trixie')->find();
echo $fairy->name.' lives in '.$fairy->tree->name;
Again it largely relies on a naming convention, it expects a column called
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
protected $belongs_to=array(
//Set the name of the relation, this defines the
//name of the property that you can access this relation with
'home'=>array(
//name of the model to link
'model'=>'tree',
//key in 'fairies' table
'key'=>'tree_id'
)
);
}
And then use like this:
1
2
3
4
5
$fairy=$pixie->orm->get('fairy')->where('name','Trixie')->find();
echo $fairy->name.' lives in '.$fairy->home->name;
If the current model that you get a relationship of is not a loaded result getting the relationship instead will not return a loaded model, instead it will return a general model (Tree in our case) and add appropriate conditions to it, so that when you use a find_all() you can get appropriate rows. It sounds complex, but it has an easy example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//Getting a single tree where either Tinkerbell or Trixie lives
$pixie->orm->get('fairy')
->where('name','Tinkerbell')
->where('or',array('name','Trixie'))
//We access trees without calling find_all() on fairies
->tree
//We can add any conditions to it again
->order_by('name','desc')
//And now get te first tree
->find();
The above will execute just a single query on the database. This is the main feature of the ORM. If you have complex relations you can access any related object with just one query, even if there are a lot of relationships in between. As with the find() if there is no corresponding object (tree) a relationship will still return a model, and you need to check if it’s loaded.
1
2
3
4
5
6
7
8
9
10
11
12
13
$fairy=$pixie->orm->get('fairy')->where('name','Trixie')->find();
if($fairy->tree->loaded()){
echo $fairy->name.' lives in '.$fairy->tree->name;
}else{
echo $fairy->name.' doesn't dwell in a tree.'
}
The has_many relationship falls on the other side of the belongs_to one. In our case each tree can have multiple fairies living inside of it. Configuration is very similar, but remember that it uses the same tree_id field we already specified, so there is no reason to add anything else. A simple version would be like this:
1
2
3
4
5
6
7
8
9
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
protected $has_many=array('fairies'); //Note the multiple clause
}
Or a more customized example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
protected $has_many=array(
'fairies'=>array(
'model'=>'fairy',
'key'=>'tree_id'
)
);
}
Has_many relation never returns a loaded object, it always just customizes you query for the relation, so you need to call find() or find_all() later on.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$tree=$pixie->orm->get('tree')->where('name','Oak')->find();
//Getting 3 fairies living in the Oak tree.
$tree->fairies
//again we can add some other conditions
->limit(3)
->find_all();
//Counting all fairies that live in this tree
echo 'Oak houses '.$tree->fairies->count_all().' fairies';
Let,s now try a complex example of both relationships:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//Find all fairies that live in the same tree as tinkerbell
$pixie->orm->get('fairy')->where('name','Tinkerbell')
->tree
->fairies
//We already know tinkerbell lives there, so let's omit her
->where('name','<>','Tinkerbell')
->find_all();
Again this will result in only a single query. I hope this convinced you enough that ORM is very helpful thing to use. You will be glad to know that not many ORM systems can offer same awesome performance as PHPixie does, a lot of them would build 2 or even 3 queries to do the same thing.
Has_one relationship behaves the same as the has_many one, with a single difference being that it will call find() automatically if the current model is loaded (just like belongs_to does). We would use it if each tree could only house a single fairy. The configuration is very similar to the has_many relationship.
1
2
3
4
5
6
7
8
9
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
protected $has_one=array('fairy'); //Note the singular clause
}
or the extended version:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
protected $has_one=array(
'fairy'=>array(
'model'=>'fairy',
'key'=>'tree_id'
)
);
}
And a usage example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//Find a fairy that lives in the Oak
$tree=$pixie->orm->get('tree')->where('name','Oak')->find();
echo $tree->fairy->name.' lives here';
//Or find fairies that live in Oak or Pine
$fairies=$pixie->orm->get('tree')
->where('name','Oak')
->where('or',array('name','Pine'))
->fairy
->find_all();
Up to this moment we were dealing only with a relations where there always was a singular object at least at one end of the relationship. E.g. each fairy could live only in one tree. Many times you will need to implement what’s called a many-to-many relationship. For example a fairy can protect many trees, and each tree can be protected by many fairies. There is no way to specify this relationship with just two tables, a third joining table must be added to the database. Let’s add a fairies_trees table with 2 columns: fairy_id and tree_id. This is a special case of the has_many relationship. The Fairy would look like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
protected $has_many=array(
'protects'=>array(
'model'=>'tree',
'through'=>'fairies_trees'
)
);
}
Again this relies on the naming convention and expects tree_id and fairy_id to exist in exist in fairies_trees table. You can customize these keys as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
protected $has_many=array(
'protects'=>array(
'model'=>'tree',
'through'=>'fairies_trees',
//the key for current table in joining table
'key'=>'fairy_id',
//the key for foreign (trees) table in joining table
'foreign_key'=>'tree_id'
)
);
}
The same relation for the Tree model would look very similar, but with foreign_key and key switching places.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
protected $has_many=array(
'protectors'=>array(
'model'=>'fairy',
'through'=>'fairies_trees',
//the key for current table in joining table
'key'=>'tree_id',
//the key for foreign (fairies) table in joining table
'foreign_key'=>'fairy_id'
)
);
}
These relations would be accessed in the same fashion as a generic has_many relation.
In total our models now look like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
// /classes/app/model/fairy.php
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
//We will call 'home' the tree that the fairy lives in
//There is no need to set the 'key' because its the default one
protected $belongs_to=array(
'home'=>array('model'=>'tree')
);
//Trees this fairy protects
protected $has_many=array(
'protects'=>array(
'model'=>'tree',
'through'=>'fairies_trees'
)
);
}
````php?start_inline=1
// /classes/app/model/tree.php
namespace App\Model;
class Tree extends \PHPixie\ORM\Model{
//The fairy that lives in the tree
protected $has_one=array('fairy');
//Fairies that protect this tree
protected $has_many=array(
'protectors'=>array(
'model'=>'fairy',
'through'=>'fairies_trees'
)
);
}
Adding and removing relationships is easy too. While it is possible to set the tree the fairy lives in by setting her tree_id property, this is not that easy to do for many-to-many relationships. This is why PHPixie abstracts how the relationships are added and removed into two methods add() and remove().
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$fairy=$pixie->orm->get('fairy');
$fairy->name='Tinkerbell';
$fairy->save();
$oak=$pixie->orm->get('tree')->where('name','Oak')->find();
//Tinkerbell will live in the Oak
$oak->add('fairy',$fairy);
//And protect it
$oak->add('protectors',$fairy);
//So now Trixie can stop protecting it
$trixie=$pixie->orm->get('fairy')->where('name','Trixie')->find();
$oak->remove('protectors',$trixie);
If the relationship has both sides (like belongs_to on one side and has_many on the other) then updating one side will also update the other to avoid redundancy. You can also use assigning to a relationship as a shorthand to the add() method.
1
2
3
4
5
6
7
$oak=$pixie->orm->get('tree')->where('name','Oak')->find();
$trixie=$pixie->orm->get('fairy')->where('name','Trixie')->find();
$trixie->home=$oak;
Working with results of find_all() is very similar to how Database Module handles it. The results are iterable so they can be placed in a foreach statement. But they are not arrays. To get result as an array of objects you will need to call the as_array() method.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$trees=$pixie->orm->get('tree')->find_all();
foreach($trees as $tree)
echo 'Tree name: '.$tree->name;
//Getting an array
$trees=$pixie->orm->get('tree')->find_all()->as_array();
//To get an array of rows presented as associative arrays
//and not as ORM objects pass True to this method
$trees=$pixie->orm->get('tree')->find_all()->as_array(true);
//this is usefull for raw data presentation, such as json_encode
echo json_encode($trees);
Preloading relationships is useful if you are going to be accessing same rlationship for multiple objects, for example printing the name of each fairy and the tree she lives in. If we don’t preload the trees beforehand a database query will be executed for each fairy to get the tree she lives in. To preload a relationship use the with() method like this:
1
2
3
4
5
6
7
8
9
//The trees will be automatically preloaded
$fairies=$pixie->orm->get('fairy')->with('tree')->find_all();
foreach($fairies as $fairy)
echo $fairy->name.' lives in '.$fairy->tree->name;
You can also preload nested relationships (for example preloading the protector fairy for the trees too) like this:
1
2
3
$fairies=$pixie->orm->get('fairy')->with('tree.protector')->find_all();
Adding your own properties and methods to a Model is done just by adding them to the Model class. But here is something to consider, always remember that you may cause redundant queries that will influence your performance. If you create a method that will return the count of the tries that a fairy protects like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
//...
function protected_trees(){
return $this->protects->count_all();
}
//...
}
it will fire a separate counting query every time you use it. This is why you need caching. PHPixies’ ORM provides a way to cache thing that you wouldn’t like to be called redundantly and it will also transform your methods into properties. It’s easy, just define a get() method like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
namespace App\Model;
class Fairy extends \PHPixie\ORM\Model{
//...
function get($property){
if($property=='protected_trees')
return $this->protects->count_all();
//You can add as many more properties like this as you like
}
//...
}
You can then access this value like this:
1
2
3
echo $fairy->protected_trees;
and no matter how often you will use it it will not cause any performance loss, because everything that gets defined in get() gets cached. The cache is cleared every time the object is updated, so it will never be out of date.
Extensions
Adding new functionality to your models can be done in various ways. The obvious solution would be extending the Model class, but thiscan lead to architectural problems since PHP doesn’t support multiple inheritance. That’s why a concept of extensions was introduced, think of them as of helper classes. Let’s take a look at the Nested extension that allows us to work with Nested Sets.
Nested Sets
Nested sets are an alternative way to store complex hierarchies in your database, e.g. blog categories. The most simple approach to storing hierarchal data is having a parent_id column in your table. This is very convenient for moving nodes inside the tree and updating it, but it makes selecting subtrees very hard. Sticking to our blog categories example, if you want to put a list of them in the sidebar you would have to first select first level categories and then recursively find the children of each one, which can amount into a huge amount of queries. Of course you could select all of them at once and use PHP to order them around, but this still isn’t very flexible.
Nested sets solve this problem by instead of the parent_id column having a lpos(left position) and rpos(right position) fields which describe the nesting. Child nodes have lpos and rpos values between the lpos and rpos of the parent node, like this:
Nested Sets Diagram
Optionally you can also have a depth field that describes the nesting level of the node, with 0 meaning that the node is at the root level, 1 that it’s a first-level child and so on. This approach makes selecting subtrees very simple, all you have to do is find all items that have the lpos and rpos between the values of the parent node and sort them by lpos ascending. Then you can use depth value to calculate label margins when displaying them, so that depth 0 would mean no indentation in the list, depth 1 would indent the item slightly, depth 2 would indent it some more thus resulting in a very nice tree-like representation of the categories.
Nested Sets are rather hard to modify, since you have to recalculate positions of nodes. ORM Nested extension takes care of that in a very efficient fashion. To take advantage of it let’s first create a categories table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`lpos` int(11) DEFAULT NULL,
`rpos` int(11) DEFAULT NULL,
`depth` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
Now here is our model:
1
2
3
4
5
6
7
8
9
10
11
12
13
namespace App\Model;
class Category extends \PHPixie\ORM\Model{
protected $extensions = array(
'nested'=>'\PHPixie\ORM\Extension\Nested'
);
}
Using it is even easier:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
$first= $pixie->orm->get('category');
$first->name = 'First';
//Appending to nothing makes a root node
//Notice how you can access an Extension as a property
$first->nested->prepare_append();
//Always call save() after prepare_append()
//We don't do it automatically because in some cases you may want
//to modify the model before saving it.
$first->save();
$second = $pixie->orm->get('category');
$second->name = 'Second';
//Make $second a child of $first
//Nodes are always moved together with their children
$second->nested->prepare_append($first);
$second->save();
$third = $pixie->orm->get('category');
$third->name = 'Third';
$third->nested->prepare_append($second);
$third->save();
//Refresh $second from database
//This is needed because it's record got updated during appending
//If you are going to be doing multiple tree modifications in a row
//it is best to refresh models from the database before doing each one.
$first = $pixie->orm->get('category',$first->id);
$second = $pixie->orm->get('category',$second->id);
//This will move all children from $second to $first,
//but will leave $second where it was
$second->nested->move_children($first);
$first = $pixie->orm->get('category',$first->id);
//Find all children of $first
$children = $first->nested->children()->find_all();
//This will prepare the node for removal,
//update the tree and delete it's children
$first->prepare_delete();
//You also have to call delete() manually after preparing
$first->delete();
Outputting categories as a tree could be done like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
foreach($categories as $cat){
for($i=0;$i<$cat->depth;$i++)
echo('-');
echo $cat->name;
}
//It would look like:
//first
//-second
//--third
Of course in real life it’s much better to define classes like ‘depth-0′, ‘depth-1′ etc with margins increasing along with depth.
A very usefull trick is to use both nested sets and parent_id in your models and use whichever is more appropriate for the given task.
I hope you finished this lengthy read and can now create your own ORM models. Soon you will learn that ORM is very simple to use and it will greatly speed up your website development.