In this article we are going to look at 3 main subjects: Hooks (with CodeIgniter), Doctrine Profiling and DQL. And we are going to see how we can use these to optimize some of our database interactions.
"CodeIgniter and Doctrine from Scratch" Series:
Before we start going into Hooks, Profiling and DQL, let’s make a small addition to our Message Board home page.
User Controls
In Message Boards, there is usually a section in the top right, with user specific info and links. I am not sure if there is a better name for it, but I’m going to call it “User Controls”.
On the CodeIgniter Forums it looks like this:
And if you are not logged in, it looks like this:
Now we are going add something similar, but simpler, to our Message Board.
Home View
- Edit: system/application/views/home.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Home</title>
<link rel="stylesheet" href="<?php echo base_url(); ?>css/style.css"
type="text/css" media="all">
</head>
<body>
<div class="forums">
<div class="user_controls">
<?php if ($user = Current_User::user()): ?>
Hello, <em><?php echo $user->username; ?></em> <br/>
<?php echo anchor('logout', 'Logout'); ?>
<?php else: ?>
<?php echo anchor('login','Login'); ?> |
<?php echo anchor('signup', 'Register'); ?>
<?php endif; ?>
</div>
<h1>CI+Doctrine Message Board</h1>
<?php foreach($categories as $category): ?>
<div class="category">
<h2><?php echo $category->title; ?></h2>
<?php foreach($category->Forums as $forum): ?>
<div class="forum">
<h3>
<?php echo anchor('forums/'.$forum->id, $forum->title) ?>
(<?php echo $forum->Threads->count(); ?> threads)
</h3>
<div class="description">
<?php echo $forum->description; ?>
</div>
</div>
<?php endforeach; ?>
</div>
<?php endforeach; ?>
</div>
</body>
</html>
We just added the highlighted lines.
Line 13: We are using the Current_User class, to see if the user is logged in. If they are logged in, we will get an instance of the User Model. If not, it will return false.
Lines 15-16: We display the username, and link to the Logout Controller.
Lines 18-19: This section is for people who are not logged in. We link to the Login Controller and Signup Controller.
So we pretty much have just utilized Models and Controllers we have built before.
We need to make a small addition to the css.
- Edit: css/style.css
- Add this to the end of the file.
/* USER CONTROL BOX -------------------------------*/
.user_controls {
float: right;
text-align: right;
}
Testing
The links are showing up at the top right corner.
- Click Login.
- Login as Testuser:mypass .
Now we can see the username and the Logout link.
We will add more features to this later in other tutorials.
Profiling with Doctrine
Using Doctrine, our code is cleaner, smaller and easier to read and maintain. But it is sometimes a good idea to look at a few things that are going on behind the scenes.
For example, Doctrine executes several SQL queries for us, while we use our Models. It would be nice to know what these queries are, so we can see if there is more room for optimization. This can be accomplished by Profiling.
For this, we are going to be using a Doctrine component called Doctrine_Connection_Profiler.
Profiling the Home Page
Let’s look at what queries are executed on the home page. We are going to make a quick and dirty test for now. Later in the article we’ll make a more re-usable version.
There is going to be lots of new code here, but I will explain them line by line:
- Edit: system/application/controllers/test.php
<?php
class Test extends Controller {
//...
function home_profiler() {
// set up the profiler
$profiler = new Doctrine_Connection_Profiler();
foreach (Doctrine_Manager::getInstance()->getConnections() as $conn) {
$conn->setListener($profiler);
}
// copied from home controller
$vars['categories'] = Doctrine::getTable('Category')->findAll();
$this->load->view('home', $vars);
// analyze the profiler data
$time = 0;
$events = array();
foreach ($profiler as $event) {
$time += $event->getElapsedSecs();
if ($event->getName() == 'query' || $event->getName() == 'execute') {
$event_details = array(
"type" => $event->getName(),
"query" => $event->getQuery(),
"time" => sprintf("%f", $event->getElapsedSecs())
);
if (count($event->getParams())) {
$event_details["params"] = $event->getParams();
}
$events []= $event_details;
}
}
print_r($events);
echo "\nTotal Doctrine time: " . $time . "\n";
echo "Peak Memory: " . memory_get_peak_usage() . "\n";
}
}
Lines 15-17 are just copied from the home Controller. Before that, we set up the Profiler, and after that we look at the data gathered by the Profiler.
Lines 9-12: First we create a Profiler object. Then we attach it to every Doctrine connection. In our case, it will be just one connection. Now it’s ready to listen and record all Doctrine events.
Line 22: The Profiler object let’s us loop through it and get each Event object. Events can be executed queries and also other things like fetching row data etc.. But we are only going to look at query events.
Lines 20,23 and 37: Each Event has information on how much time it took. So we will add them up in $time variable and display it at the end.
Line 24: We are only interested in SQL queries. These can be types ‘query’ or ‘execute’. If a query has assigned parameters (for example ‘WHERE id = ?’), then it is of type ‘execute’, otherwise it is ‘query’.
Lines 25-32: We create an array ($event_details) with information about the query. The type, the query SQL, and the time it took to run. Also if there were any parameters, we add that too.
Lines 33 and 36: We add it all into the $events array, and just dump it at the end with print_r().
Line 38: We use the memory_get_peak_usage() PHP function to find out what the maximum memory usage was during the script execution.
- Go to: http://localhost/ci_doctrine/test/home_profiler
- View Source:
Array
(
[0] => Array
(
[type] => query
[query] => SELECT c.id AS c__id, c.title AS c__title FROM category c
[time] => 0.000220
)
[1] => Array
(
[type] => execute
[query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
[time] => 0.000310
[params] => Array
(
[0] => 2
)
)
[2] => Array
(
[type] => execute
[query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
[time] => 0.000322
[params] => Array
(
[0] => 1
)
)
[3] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000208
[params] => Array
(
[0] => 1
)
)
[4] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000169
[params] => Array
(
[0] => 2
)
)
[5] => Array
(
[type] => execute
[query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
[time] => 0.000322
[params] => Array
(
[0] => 2
)
)
[6] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000238
[params] => Array
(
[0] => 3
)
)
[7] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000171
[params] => Array
(
[0] => 4
)
)
[8] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000160
[params] => Array
(
[0] => 5
)
)
)
Total Doctrine time: 0.013517618179321
Peak Memory: 5849232
As you can see, there were 9 queries executed by Doctrine. That seems too many. This happens because of the way Doctrine does ‘lazy loading’. It fetches data from the database as we access the objects and their parameters.
Later in the article, we are going to see how we can optimize this with DQL.
Let’s see how we can use the Profiler for the entire application, in a re-usable way. For that, we’re going to look at CodeIgniter Hooks.
CodeIgniter Hooks
With Hooks, we can tell CodeIgniter to perform an operation, at a given point in the application flow.
For example, we are going to initialize the Profiler, before any Controller function is called.
Enabling Hooks
Hooks are disabled by default, so we must first enable them.
- Edit: system/application/config/config.php
// ... /* |-------------------------------------------------------------------------- | Enable/Disable System Hooks |-------------------------------------------------------------------------- | | If you would like to use the "hooks" feature you must enable it by | setting this variable to TRUE (boolean). See the user guide for details. | */ $config['enable_hooks'] = TRUE; // ...
Just changed it from FALSE to TRUE. Now we can use Hooks.
Creating a Profiler Hook
Let’s create the functions that will be called for this hook.
- Create: system/application/hooks/doctrine_profiler_hooks.php
<?php
class Doctrine_Profiler_Hooks {
public static $profiler;
public function profiler_start() {
self::$profiler = new Doctrine_Connection_Profiler();
foreach (Doctrine_Manager::getInstance()->getConnections() as $conn) {
$conn->setListener(self::$profiler);
}
}
public function profiler_end() {
// analyze the profiler data
$time = 0;
$events = array();
foreach (self::$profiler as $event) {
$time += $event->getElapsedSecs();
if ($event->getName() == 'query' || $event->getName() == 'execute') {
$event_details = array(
"type" => $event->getName(),
"query" => $event->getQuery(),
"time" => sprintf("%f", $event->getElapsedSecs())
);
if (count($event->getParams())) {
$event_details["params"] = $event->getParams();
}
$events []= $event_details;
}
}
$output = "<"."?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?".">\n\n";
$output .= print_r($events,1);
$output .= "\nTotal Doctrine time: " . $time . "\n";
$output .= "Peak Memory: " . memory_get_peak_usage() . "";
file_put_contents(BASEPATH."/logs/doctrine_profiler.php", $output);
}
}
The code is mostly the same as before. This time we are storing the $profiler as a static variable in this class. Also, we are writing the output to a log file instead of dumping it into the browser.
Adding the Hooks
Now we need to add the hooks to the config so they get executed.
- Edit: system/application/config/hooks.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------------
| Hooks
| -------------------------------------------------------------------------
| This file lets you define "hooks" to extend CI without hacking the core
| files. Please see the user guide for info:
|
| http://codeigniter.com/user_guide/general/hooks.html
|
*/
$hook['post_controller_constructor'][] = array(
'class' => 'Doctrine_Profiler_Hooks',
'function' => 'profiler_start',
'filename' => 'doctrine_profiler_hooks.php',
'filepath' => 'hooks',
);
$hook['post_controller'][] = array(
'class' => 'Doctrine_Profiler_Hooks',
'function' => 'profiler_end',
'filename' => 'doctrine_profiler_hooks.php',
'filepath' => 'hooks',
);
/* End of file hooks.php */
/* Location: ./system/application/config/hooks.php */
So we store the hooks inside the $hook array. The index of the array is name of the hook. In this case we used the ‘post_controller_constructor’ and ‘post_controller’ Hooks.
The array structure should be easy to understand. We point to the class name, function, file path, file name, so that CodeIgniter can find our code for execution.
(Note: I would have preferred to use the “pre_controller” Hook, because it happens before even the Controller constructor is called. However, the plug-ins are not initialized at that point, including Doctrine, so we need to use “post_controller_constructor” instead.)
Testing
You should just see the homepage with no errors. If you are getting file access errors, make sure the system/logs folder is writable.
- Open: system/logs/doctrine_profiler.php
You should see something like this:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?>
Array
(
[0] => Array
(
[type] => query
[query] => SELECT c.id AS c__id, c.title AS c__title FROM category c
[time] => 0.000220
)
[1] => Array
(
[type] => execute
[query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
[time] => 0.000310
[params] => Array
(
[0] => 2
)
)
[2] => Array
(
[type] => execute
[query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
[time] => 0.000322
[params] => Array
(
[0] => 1
)
)
[3] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000208
[params] => Array
(
[0] => 1
)
)
[4] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000169
[params] => Array
(
[0] => 2
)
)
[5] => Array
(
[type] => execute
[query] => SELECT f.id AS f__id, f.title AS f__title, f.description AS f__description, f.category_id AS f__category_id FROM forum f WHERE f.category_id IN (?)
[time] => 0.000322
[params] => Array
(
[0] => 2
)
)
[6] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000238
[params] => Array
(
[0] => 3
)
)
[7] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000171
[params] => Array
(
[0] => 4
)
)
[8] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000160
[params] => Array
(
[0] => 5
)
)
)
Total Doctrine time: 0.013517618179321
Peak Memory: 5849232
Let’s see how we can optimize this and reduce the number of queries executed.
Optimizing with DQL
In the Day 5 – CRUD article, we had a first look at DQL. Now we are going to use it in our application.
If you are familiar with the CodeIgniter Active Records class, there are some similarities. However DQL is much more powerful.
Reducing the Number of Queries
- Edit: system/application/controllers/home.php
<?php
class Home extends Controller {
public function index() {
// $vars['categories'] = Doctrine::getTable('Category')->findAll();
$vars['categories'] = Doctrine_Query::create()
->select('c.title, f.title, f.description')
->from('Category c, c.Forums f')
->execute();
$this->load->view('home', $vars);
}
}
We just commented out the findAll() line, and added a DQL call instead.
In the select() call we specify which fields we want to fetch.
In the from() call, we put the Models and use short aliases for them (c and f). Note that, we used c.Forums instead c.Forum, even though the Model was named Forum. This is because we are joining two models here, and in the Category Model definition, we referred to the Forum model as Forums:
<?php
// models/category.php
class Category extends Doctrine_Record {
public function setTableDefinition() {
$this->hasColumn('title', 'string', 255);
}
public function setUp() {
$this->hasMany('Forum as Forums', array(
'local' => 'id',
'foreign' => 'category_id'
));
}
}
It was just more suitable to name in plural since it’s a one-to-many relationship.
Another thing to note is that we did not specify an ON clause in DQL. Normally when you join tables in SQL, you would specify the columns you are joining on. But since our model definitions know how these relationships are set up, ON clause is not necessary in this case.
Finally, we run execute() to fetch the Doctrine_Collection object.
Let’s load the home page again, and then look at the profiler output.
- First go to: http://localhost/ci_doctrine/
- Then open: system/logs/doctrine_profiler.php
You should see something like this:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?>
Array
(
[0] => Array
(
[type] => query
[query] => SELECT c.id AS c__id, c.title AS c__title, f.id AS f__id, f.title AS f__title, f.description AS f__description FROM category c LEFT JOIN forum f ON c.id = f.category_id
[time] => 0.000311
)
[1] => Array
(
[type] => execute
[query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
[time] => 0.000307
[params] => Array
(
[0] => 2
)
)
[2] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000274
[params] => Array
(
[0] => 1
)
)
[3] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000172
[params] => Array
(
[0] => 2
)
)
[4] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000214
[params] => Array
(
[0] => 3
)
)
[5] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000154
[params] => Array
(
[0] => 4
)
)
[6] => Array
(
[type] => execute
[query] => SELECT t.id AS t__id, t.title AS t__title, t.forum_id AS t__forum_id FROM thread t WHERE t.forum_id IN (?)
[time] => 0.000311
[params] => Array
(
[0] => 5
)
)
)
Total Doctrine time: 0.012369155883789
Peak Memory: 5856440
Now we went down to 7 queries instead of 9. However, there is still room for improvement.
Getting Count
Currently, most of these queries are caused by the count() call in our Home View.
(<?php echo $forum->Threads->count(); ?> threads)
Now we are going to fetch the thread count with DQL so that these extra queries do not need to run.
- Edit: system/application/controllers/home.php
<?php
class Home extends Controller {
public function index() {
// $vars['categories'] = Doctrine::getTable('Category')->findAll();
$vars['categories'] = Doctrine_Query::create()
->select('c.title, f.title, f.description')
->addSelect('t.id, COUNT(t.id) as num_threads')
->from('Category c, c.Forums f')
->leftJoin('f.Threads t')
->groupBy('f.id')
->execute();
$this->load->view('home', $vars);
}
}
Highlighted lines are where the changes are.
Line 11: Now we are selecting from the Threads Model too, so we can count them. I could have added the ‘Threads’ model into the from() call in Line 10. But I wanted to demonstrate another way you can do joins in DQL. There is also innerjoin() if you need it.
Line 9: COUNT(t.id) will give us the number of threads. I could have added this into the select() in Line 8, but for demonstration purposes again, I used the addSelect() function.
Line 12: Since we want individual Forums returned, and also are counting the Threads per Forum, we are grouping the results by the Forum id field.
If you wanted to do the same thing in raw SQL, the query would look like this:
SELECT c.id, c.title, f.id, f.title, f.description, COUNT(t.id) AS num_threads FROM category c LEFT JOIN forum f ON c.id = f.category_id LEFT JOIN thread t ON f.id = t.forum_id GROUP BY f.id
One More Change
Now we need to do one more change. Since we are going to use the returned ‘num_threads’ value instead of calling $forum->Threads->count(), we need to change a line in the Home View.
- Edit: system/application/views/home.php around line #37
- Just change the highlighted line.
<!-- -->
<div class="forum">
<h3>
<?php echo anchor('forums/'.$forum->id, $forum->title) ?>
(<?php echo $forum->Threads[0]->num_threads; ?> threads)
</h3>
<div class="description">
<?php echo $forum->description; ?>
</div>
</div>
<!-- -->
At the first glance, the structure might seem a bit odd. Why not ‘$forum->Threads->num_threads’? or even ‘$forum->num_threads’?
This has to do with the way the query is structured. We called COUNT() on the Threads.id field. Therefore the returned data belongs to the Threads relationship, and not to the $forum object directly.
Also, the relationship is one-to-many. Therefore $forum->Threads is a Doctrine_Collection by default, instead of a Doctrine_Record. So we treat it like an array, and add the [0] index first before we can get the data in num_threads.
The result is: $forum->Threads[0]->num_threads
If you are ever unsure about the structure of the returned Doctrine_Collection object, you can convert it to an array using toArray(true) and dump it on the screen. Passing ‘true’ makes it ‘deep’, otherwise you only get the outermost object.
For example, if you do this:
$vars['categories'] = Doctrine_Query::create()
->select('c.title, f.title, f.description')
->addSelect('COUNT(t.id) as num_threads')
->from('Category c, c.Forums f')
->leftJoin('f.Threads t')
->groupBy('f.id')
->execute();
print_r($vars['categories']->toArray(true));
You can get an output like this:
Array
(
[0] => Array
(
[id] => 1
[title] => The CodeIgniter Lounge
[Forums] => Array
(
[0] => Array
(
[id] => 1
[title] => Introduce Yourself!
[description] => Use this forum to introduce yourself to the CodeIgniter community, or to announce your new CI powered site.
[category_id] => 1
[Category] =>
[Threads] => Array
(
[0] => Array
(
[id] =>
[title] =>
[forum_id] => 1
[Forum] =>
[num_threads] => 2
)
)
)
[1] => Array
(
[id] => 2
[title] => The Lounge
[description] => CodeIgniter's social forum where you can discuss anything not related to development. No topics off limits... but be civil.
[category_id] => 1
[Category] =>
[Threads] => Array
(
[0] => Array
(
[id] =>
[title] =>
[forum_id] => 2
[Forum] =>
[num_threads] => 0
)
)
)
)
[num_threads] => 2
)
[1] => Array
(
[id] => 2
[title] => CodeIgniter Development Forums
[Forums] => Array
(
[0] => Array
(
[id] => 3
[title] => CodeIgniter Discussion
[description] => This forum is for general topics related to CodeIgniter.
[category_id] => 2
[Category] =>
[Threads] => Array
(
[0] => Array
(
[id] =>
[title] =>
[forum_id] => 3
[Forum] =>
[num_threads] => 0
)
)
)
[1] => Array
(
[id] => 4
[title] => Code and Application Development
[description] => Use the forum to discuss anything related to programming and code development.
[category_id] => 2
[Category] =>
[Threads] => Array
(
[0] => Array
(
[id] =>
[title] =>
[forum_id] => 4
[Forum] =>
[num_threads] => 0
)
)
)
[2] => Array
(
[id] => 5
[title] => Ignited Code
[description] => Use this forum to post plugins, libraries, or other code contributions, or to ask questions about any of them.
[category_id] => 2
[Category] =>
[Threads] => Array
(
[0] => Array
(
[id] =>
[title] =>
[forum_id] => 5
[Forum] =>
[num_threads] => 0
)
)
)
)
[num_threads] => 0
)
)
Profile Again
Let’s look at the profiling results again to see how the new DQL performed.
- First go to: http://localhost/ci_doctrine/
- Then open: system/logs/doctrine_profiler.php
You should see something like this:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); ?>
Array
(
[0] => Array
(
[type] => query
[query] => SELECT c.id AS c__id, c.title AS c__title, f.id AS f__id, f.title AS f__title, f.description AS f__description, COUNT(t.id) AS t__0 FROM category c LEFT JOIN forum f ON c.id = f.category_id LEFT JOIN thread t ON f.id = t.forum_id GROUP BY f.id
[time] => 0.000493
)
[1] => Array
(
[type] => execute
[query] => SELECT u.id AS u__id, u.username AS u__username, u.password AS u__password, u.email AS u__email, u.created_at AS u__created_at, u.updated_at AS u__updated_at FROM user u WHERE u.id = ? LIMIT 1
[time] => 0.000354
[params] => Array
(
[0] => 2
)
)
)
Total Doctrine time: 0.011896848678589
Peak Memory: 6010440
Nice, we are all the way down to 2 queries!
The first one is for fetching all Categories, their Forums, and Thread counts all at once. And the second query was done by the Current_User class for getting user details. You can’t go much lower than this.
Conclusions
I admit that our code became a little more complicated when we switched from using findAll() to DQL. But if your web application is expecting a significant amount of traffic, you might need to utilize such optimizations. Also, with DQL you can achieve more complicated queries that would not be practical with the magic functions like findAll() or findBy*().
Optimization is a debatable subject. Some prefer to do it early, in small steps. Some prefer to do it all the way at the end of development. But as you gain experience, your initial code tends to be more optimized in the first place, and may not need many changes later on.
I guess the point is, using an ORM like Doctrine does not magically optimize your database interactions. You still need to put an effort into it.
Stay Tuned
Thank you for reading. Learning more about DQL was an important step, as it is an integral part of Doctrine. Also, Hooks and Profiling gave us an opportunity to look at what is going on behind the scenes.
And thank you for all the encouraging comments. It makes writing these articles even more enjoyable.
#1 by 3m masr on December 4th, 2009
| Quote
Really good man .. keep on .. i thinks this is my next step .. i am now writing some tutorials about JQuery validation with its plugin and with PHP and AJAX in my blog ISA it will be very good idea to try CI with Doctrine .. thanks
#2 by Zack on December 4th, 2009
| Quote
Thanks again for a great tutorial. I hope this series keeps going strong.
#3 by Kezza on December 6th, 2009
| Quote
Again, brilliant tutorial…. keep up the good work and I look forward to the next episode.
Kez
#4 by Carlos on December 6th, 2009
| Quote
Thank you!
I was visiting your blog every day waiting for the 8º part.
#5 by Marcel Marnix on December 6th, 2009
| Quote
Yes it was worth waiting thanks again
#6 by Martin Ledoux on December 8th, 2009
| Quote
Hey there, I’ve been enjoying your website since I found it last week. I actually found Doctrine a year ago before I found CI, and thought it was an excellent replacement for the Model section of CI when I later found it. Have you considered replacing View component of CI with Smarty? That would make a great addition to this series, especially when Smarty is done with blocks to work like Django’s templating engine (if you’re at all familiar with Python and the Django framework, which CI is very similar to)
#7 by Burak on December 8th, 2009
| Quote
I like the idea of using Smarty that way, but it might be too much to include in this particular series. I don’t wanna end up writing 50 episodes for a single series
But we’ll see
#8 by Enrique on December 8th, 2009
| Quote
Hi
What an awesome series of Doctrine with CodeIgniter…
I guess you’re going to use the pagination library with Doctrine when showing forum threads, right?
Thanks!
#9 by Burak on December 8th, 2009
| Quote
Yeah, I might do it in the Day 10 article.
#10 by Marcel Marnix on December 9th, 2009
| Quote
Just a little tip for the php starters who see the test.php array as one big string. And want to see it like this example.
Put the Print_r in tags:
In test.php replace print_r($events); with:
echo “”;
print_r($events);
echo “”;
#11 by Burak on December 9th, 2009
| Quote
I think your comment got filtered. Did you mean this?
echo “<pre>”;
print_r($events);
echo “</pre>”;
#12 by Marcel Marnix on December 9th, 2009
| Quote
Wow that was fast, couldn’t even correct myself,
and yes I meant that!!
#13 by lenzjo on December 14th, 2009
| Quote
Maybe this is a newbie error on my part (1 week in CI)… I have completed everything correctley up to now, until I hit the “One more Change” on line 40 of home.php. With the changed line I get the following error msg straight after “Introduce yourself! (”
*——-start of error msg
Fatal error: Uncaught exception ‘Doctrine_Record_UnknownPropertyException’ with message ‘Unknown record property / related component “num_threads” on “Thread”‘ in H:\XWebDev\doctrineforum\system\plugins\doctrine\lib\Doctrine\Record\Filter\Standard.php:55 Stack trace: #0 H:\XWebDev\doctrineforum\system\plugins\doctrine\lib\Doctrine\Record.php(1374): Doctrine_Record_Filter_Standard->filterGet(Object(Thread), ‘num_threads’) #1 H:\XWebDev\doctrineforum\system\plugins\doctrine\lib\Doctrine\Record.php(1333): Doctrine_Record->_get(’num_threads’, true) #2 H:\XWebDev\doctrineforum\system\plugins\doctrine\lib\Doctrine\Access.php(72): Doctrine_Record->get(’num_threads’) #3 H:\XWebDev\doctrineforum\system\application\views\home.php(34): Doctrine_Access->__get(’num_threads’) #4 H:\XWebDev\doctrineforum\system\libraries\Loader.php(677): include(’H:\XWebDev\doct…’) #5 H:\XWebDev\doctrineforum\system\libraries\Loader.php(307): CI_Loader->_ci_load(Array) #6 H:\XWebDev\doctrineforum\system\application\controllers\home.php(14): CI_Loader- in H:\XWebDev\doctrineforum\system\plugins\doctrine\lib\Doctrine\Record\Filter\Standard.php on line 55
*—— end of error msg
If I change back to the original line 40 no problem. If it’s any help I’m running the latest xampp that has php5.3 (I know this is the cause of some problems with other stuff).
thx
#14 by Burak on December 14th, 2009
| Quote
I had made a change to the article, which shifted the lines a little bit. I just edited the “One More Change” section of the article so it should be correct now. But I’m not sure if that caused your problem.
You can download the files by clicking the “Download the Code” image at the beginning of the article, and compare the home.php from that to yours.
#15 by lenzjo on December 14th, 2009
| Quote
It’s taken me a while, but I finally got it fixed. Back in day 1, there was a problem with putting doctrine in the app plugin folder, so I put it in the sys plugin folder.
After going thru every file, in the download, and comparing mine to them the only diff was doctrine’s location. Having nothing to lose I moved it and it’s all working fine now.
I only started using CI just over a week ago, so I’m not totally familar yet with CI, but I have been using the Template library from the start with this project and was getting to think that it might be the problem.
#16 by Denes on December 17th, 2009
| Quote
I had the same error message reported by Lenzjo, but unfortunately I could not figure out what triggers it. Some help?
Thanks
#17 by Burak on December 17th, 2009
| Quote
Ok, I finally figured it out. It has to do with the Doctrine version.
When I first started writing this series, Doctrine 1.1 was the stable release. Now Doctrine 1.2 is out, so I assume that is what you installed. I just upgraded my install from 1.1 to 1.2 and got that same error as you guys. I am really not sure what kind of internal change they made that caused this, because 1.2 was supposed to be backwards compatible with 1.1.
Anyway, I fixed the problem. In controllers/home.php, you need to change this line:
->addSelect('COUNT(t.id) as num_threads')
to this:
->addSelect('t.id, COUNT(t.id) as num_threads')
That should fix it.
Now I will go ahead update the articles.
#18 by Denes on December 18th, 2009
| Quote
Ok! That’s it! Actually I’m using the Doctrine 1.2 version.
Thanks a lot!
#19 by auke on December 22nd, 2009
| Quote
it is always a goog thing to use DQL.
and with using DQL… what is the advantage in using Doctrine?
#20 by Burak on December 22nd, 2009
| Quote
Well, DQL itself has nice features. I prefer it over Active Records.
But also we utilized other features of Doctrine too, not just DQL.
#21 by Ansh on December 22nd, 2009
| Quote
i got a question in DQL how to pass session User id
$vars['mood'] = Doctrine_Query::create()
->select(’m.mood_name, a.id, m.account_id’)
->from(’mood m, m.account a’)
->where(’m.account_id=”7″‘)
i want to show data according to current logged user.
m.account_id=Current_User::user()->id;
this is not working
#22 by Ansh on December 22nd, 2009
| Quote
Solved
->where(’m.account_id=?’,$variable)
Pingback: CodeIgniter and Doctrine from Scratch | CodeIgniter-jQuery
Pingback: CodeIgniter and Doctrine from scratch. Day 11 – Record Hooks | PHP and Stuff
#23 by Jonathan on February 27th, 2010
| Quote
I had an odd problem…
[Sat Feb 27 21:02:16 2010] [error] [client 10.5.5.197] PHP Fatal error: Call to undefined function memory_get_peak_usage() in /home/web/sites/citest.com/html/system/application/hooks/doctrine_profiler_hooks.php on line 38
…then I realized – I’m dealing with RHEL… sure enough, the base php version = 5.1.6. Yea, remember to update php to 5.2 or better.
Just a reminder.
Pingback: CodeIgniter- Doctrine ORM Tutorial: A way of enhancing CI | TheUnical Technologies Blog
#24 by Daniel Antonini on March 23rd, 2010
| Quote
My english is very bad, i have problem, i working in symfony framework and need at model use tow tables.
as i do..?
#25 by Haqqi on March 24th, 2010
| Quote
I have an idea.
How about integrating doctrine profiling with CI profiling. So that we can just call the CI’s profiler with parameter true. How about this?
#26 by Elbi on March 27th, 2010
| Quote
Q:
When we print_r the categories after we use DQL for the query, eg:
print_r($vars['categories']->toArray(true));
Why is there another
[num_threads] => 0
)
)
at the end?
#27 by Elbi on March 27th, 2010
| Quote
I guess it’s due to the nature of Doctrine_Collection Object. But I still don’t understand how exatly or why are the values of num_threads for the categories are 2 and 0.
#28 by Peter on April 23rd, 2010
| Quote
I had problems when PHP tried to write the ‘doctrine_profiler.php’ to my /system/logs directory. It turned out that I needed to set permissions on the /system/logs directory to 777. After that it all worked fine.
#29 by Ben on May 8th, 2010
| Quote
Thanks for more tutorial-ly goodness. I had a question: what are the single letters in the Doctrine_Query? Like this:
“->select(’c.title, f.title, f.description’)”
Where do the letters c and f come from; what do they represent. Does this have something to do with MYSQL programming?
#30 by Burak on May 8th, 2010
| Quote
from(’Category c, c.Forums f’)
that makes c and f aliases for the tables
#31 by Vladimir on May 21st, 2010
| Quote
Hi,
I have a strange problem.
If I output the output variable from profiler hook, it looks like this:
——————————————————
Array
(
[0] => Array
(
[type] => query
[query] => SELECT ..(something)…….
[time] => 0.000397
)
[1] => Array
(
[type] => query
[query] => SELECT ..(something)…….
[time] => 0.000994
)
[2] => Array
(
[type] => query
[query] => SELECT ..(something)…….
[time] => 0.000710
)
)
Total Doctrine time: 0.0027720928192139
Peak Memory: 9326416
——————————————————
But in my doctrine_profiler file I got this:
———————————————
php if…..
Array
(
)
Total Doctrine time: 0
Peak Memory: 9326416
—————————————
Empty array? Why?