Codeigniter Doctrine 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:

download_code

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:

ci_doctrine_day8_1

And if you are not logged in, it looks like this:

ci_doctrine_day8_2

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

ci_doctrine_day8_3

The links are showing up at the top right corner.

  • Click Login.
  • Login as Testuser:mypass .

ci_doctrine_day8_4

ci_doctrine_day8_5

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.

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.

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.

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.

"CodeIgniter and Doctrine from Scratch" Series: