Why to use doctrine relationships vs native query

Question

I have a simple question. I saw a Symfony2-Tutorial where the blogger used everywhere simple entities without relations and was also using doctrine native-query to do joins and query the different entities.

What is better, to use doctrine native query or build every time relationships ?

Greetings Michael

Answer 1

I think that it depends on how the resultset will be handle. For example, suppose the following relationship:

a company has a lot of employees (1:N)

Just figure out the impact if this company has more than 10K employees when it does a select query in every request loading all employees data to be showed. In this scenario could be a good practice to create a lazy association in models:

<?php

/**
 * @Entity
 */
class Company
{
    /**
     * @OneToMany(targetEntity="Employee", fetch="EXTRA_LAZY")
     */
    public $employees;
}

At this case, doctrine only will triggered the required data from database, because it knows that all the data will be accessed gradually as you request it. You can read more about lazy associations in doctrine's docs.

How about the native queries?

Native queries can map arbitrary SQL code to objects, such as highly vendor-optimized SQL or stored-procedures. Fast scalar results and less ram usage. Take note that a complex model relationship could be too heavy for server to be manipulated. For example, look this structure based on Class Table Inheritance:

There is a super class called Product and there are more than 200 different sub-products that extends from Product. Each sub-product is storaged in its respective table.

<?php 

abstract class Product

    protected $name;

Some sub-products as example:

<?php

class Candy extends Product

    /** specific property for this product */
    private $sugarLevel;

Another one:

<?php

class IceCream extends Product

    /** specific property for this product */
    private $temperature;

Now, you need to assess every product in you depot. Normally, the first idea to get this resulst is doing:

$assess = array();
$products = $em->getRepository('models\Product');
foreach ($products as $p)
{
    //summarize each product by type
    $assets[$p->getType()] = $assets[$p->getType()] + 1;
}


echo "There are " . $assets['candy'] " candies in stock";

This is really heavy process because we are quering 200 tables just to determinate the existence of each product. This could be easy mitigated with a simple native query:

$query = $em->createNativeQuery('SELECT p.type, count(p.type) as total FROM Product p group by p.type', $rsm);
$result = $query->getArrayResult();

print_r($result);
// [0] => array('type' => 'candy', 'total' => 545),
// [1] => array('type' => 'icecream', 'total' => 344),
//...
// [199] => array('type' => 'foo', 'total' => 878),