Symfony2 Doctrine DataTables.js (Related Entity Enabled)

DataTable.php

<?php
/**
 * Recognizes mData sent from DataTables where dotted notations represent a related
 * entity. For example, defining the following in DataTables...
 *
 * "aoColumns": [
 *     { "mData": "id" },
 *     { "mData": "description" },
 *     { "mData": "customer.first_name" },
 *     { "mData": "customer.last_name" }
 * ]
 *
 * ...will result in a a related Entity called customer to be retrieved, and the
 * first and last name will be returned, respectively, from the customer entity.
 *
 * There are no entity depth limitations. You could just as well define nested
 * entity relations, such as...
 *
 *     { "mData": "customer.location.address" }
 *
 * Félix-Antoine Paradis is the author of the original implementation this is
 * built off of, see: https://gist.github.com/1638094 
 */

namespace Acme\DemoBundle\Helper;

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\EntityRepository;

use Symfony\Component\DependencyInjection\Container;
use Symfony\Component\Config\Definition\Exception\Exception;

class Datatable
{
    /**
     * Doctrine innerJoin type
     */
    const JOIN_INNER = 'inner';

    /**
     * Doctrine leftJoin type
     */
    const JOIN_LEFT = 'left';

    /**
     * @var string The default join type to use
     */
    protected $defaultJoinType;

    /**
     * @var object The Doctrine Entity Repository
     */
    protected $repository;

    /**
     * @var object The Doctrine Entity Manager
     */
    protected $em;

    /**
     * @var string  Used as the query builder identifier value
     */
    protected $tableName;

    /**
     * @var array All the request variables as an array
     */
    protected $request;

    /**
     * @var array The parsed request variables for the DataTable
     */
    protected $parameters;

    /**
     * @var array Information relating to the specific columns requested
     */
    protected $associations;

    /**
     * @var array SQL joins used to construct the QueryBuilder query
     */
    protected $assignedJoins = array();

    /**
     * @var array The SQL join type to use for a column
     */
    protected $joinTypes = array();

    /**
     * @var object The QueryBuilder instance
     */
    protected $qb;

    /**
     * @var array The results of the QueryBuilder instance
     */
    protected $fresults;

    /**
     * @var integer The number of records the DataTable can display in the current draw
     */
    protected $offset;

    /**
     * @var string Information for DataTables to use for rendering
     */
    protected $echo;

    /**
     * @var integer The display start point in the current DataTables data set
     */
    protected $amount;

    /**
     * @var string The DataTables global search string
     */
    protected $search;

    /**
     * @var array The primary/unique ID for an Entity. Needed to pull partial objects
     */
    protected $identifiers = array();

    /**
     * @var integer The total amount of results to get from the database
     */
    protected $limit;

    public function __construct(array $request, EntityRepository $repository, ClassMetadata $metadata, EntityManager $em)
    {
        $this->em = $em;
        $this->request = $request;
        $this->repository = $repository;
        $this->metadata = $metadata;
        $this->tableName = Container::camelize($metadata->getTableName());
        $this->defaultJoinType = self::JOIN_INNER;
        $this->setParameters();
        $this->qb = $em->createQueryBuilder();
        $this->echo = $this->request['sEcho'];
        $this->search = $this->request['sSearch'];
        $this->offset = $this->request['iDisplayStart'];
        $this->amount = $this->request['iDisplayLength'];
    }

    /**
     * @return array All the paramaters (columns) used for this request
     */
    public function getParameters()
    {
        $this->parameters;
    }

    /**
     * Parse and configure parameter/association information for this DataTable request
     */
    public function setParameters()
    {
        if (is_numeric($this->request['iColumns'])) {
            $params = array();
            $associations = array();
            for ($i=0; $i < intval($this->request['iColumns']); $i++) {
                $fields = explode('.', $this->request['mDataProp_' . $i]);
                $params[] = $this->request['mDataProp_' . $i];
                $associations[] = array('containsCollections' => false);

                if (count($fields) > 1)
                    $this->setRelatedEntityColumnInfo($associations[$i], $fields);
                else
                    $this->setSingleFieldColumnInfo($associations[$i], $fields[0]);
            }
            $this->parameters = $params;
            $this->associations = $associations;
        }
    }

    /**
     * Parse a dotted-notation column format from the mData, and sets association
     * information
     *
     * @param array Association information for a column (by reference)
     * @param array The column fields from dotted notation
     */
    protected function setRelatedEntityColumnInfo(array &$association, array $fields) {
        $mdataName = implode('.', $fields);
        $lastField = Container::camelize(array_pop($fields));
        $joinName = $this->tableName;
        $entityName = '';
        $columnName = '';

        // loop through the related entities, checking the associations as we go
        $metadata = $this->metadata;
        while ($field = array_shift($fields)) {
            $columnName .= empty($columnName) ? $field : ".$field";
            $entityName = lcfirst(Container::camelize($field));
            if ($metadata->hasAssociation($entityName)) {
                $joinOn = "$joinName.$entityName";
                if (!empty($fields) && $metadata->isCollectionValuedAssociation($entityName)) {
                    $association['containsCollections'] = true;
                }
                $metadata = $this->em->getClassMetadata(
                    $metadata->getAssociationTargetClass($entityName)
                );
                $joinName .= '_' . $this->getJoinName(
                    $metadata,
                    Container::camelize($metadata->getTableName()),
                    $entityName
                );
                // The join required to get to the entity in question
                if (!isset($this->assignedJoins[$joinName])) {
                    $this->assignedJoins[$joinName]['joinOn'] = $joinOn;
                    $this->assignedJoins[$joinName]['mdataColumn'] = $columnName;
                    $this->identifiers[$joinName] = $metadata->getIdentifierFieldNames();
                }
            }
            else {
                throw new Exception(
                    "Association  '$entityName' not found ($mdataName)",
                    '404'
                );
            }
        }

        // Check the last field on the last related entity of the dotted notation
        if (!$metadata->hasField(lcfirst($lastField))) {
            throw new Exception(
                "Field '$lastField' on association '$entityName' not found ($mdataName)",
                '404'
            );
        }
        $association['entityName'] = $entityName;
        $association['fieldName'] = $lastField;
        $association['joinName'] = $joinName;
        $association['fullName'] = $this->getFullName($association);
    }

    /**
     * Configures association information for a single field request from the main entity
     *
     * @param array  The association information as a reference
     * @param string The field name on the main entity
     */
    protected function setSingleFieldColumnInfo(array &$association, $fieldName) {
        $fieldName = Container::camelize($fieldName);

        if (!$this->metadata->hasField(lcfirst($fieldName))) {
            throw new Exception(
                "Field '$fieldName' not found.)",
                '404'
            );
        }

        $association['fieldName'] = $fieldName;
        $association['entityName'] = $this->tableName;
        $association['fullName'] = $this->tableName . '.' . lcfirst($fieldName);
    }

    /**
     * Based on association information and metadata, construct the join name
     *
     * @param ClassMetadata Doctrine metadata for an association
     * @param string The table name for the join
     * @param string The entity name of the table
     */
    protected function getJoinName(ClassMetadata $metadata, $tableName, $entityName)
    {
        $joinName = $tableName;

        // If it is self-referencing then we must avoid collisions
        if ($metadata->getName() == $this->metadata->getName()) {
            $joinName .= "_$entityName";   
        }

        return $joinName;
    }

    /**
     * Based on association information, construct the full name to refer to in queries
     *
     * @param array Association information for the column
     * @return string The full name to refer to this column as in QueryBuilder statements
     */
    protected function getFullName(array $associationInfo)
    {
        return $associationInfo['joinName'] . '.' . lcfirst($associationInfo['fieldName']);
    }

    /**
     * Set the default join type to use for associations. Defaults to JOIN_INNER
     *
     * @param string The join type to use, should be of either constant: JOIN_INNER, JOIN_LEFT
     */
    public function setDefaultJoinType($joinType)
    {
        if (defined('self::JOIN_' . strtoupper($joinType))) {
            $this->defaultJoinType = constant('self::JOIN_' . strtoupper($joinType));
        }
    }

    /**
     * Set the type of join for a specific column/parameter
     *
     * @param string The column/parameter name
     * @param string The join type to use, should be of either constant: JOIN_INNER, JOIN_LEFT
     */
    public function setJoinType($column, $joinType)
    {
        if (defined('self::JOIN_' . strtoupper($joinType))) {
            $this->joinTypes[$column] = constant('self::JOIN_' . strtoupper($joinType));
        }
    }

    /**
     * Set the scope of the result set
     */
    public function setLimit()
    {
        if (isset($this->offset) && $this->amount != '-1') {
            $this->qb->setFirstResult($this->offset)->setMaxResults($this->amount);
        }
    }

    /**
     * Set any column ordering that has been requested
     */
    public function setOrderBy()
    {
        if (isset($this->request['iSortCol_0'])) {
            for ($i = 0; $i < intval($this->request['iSortingCols']); $i++) {
                if ($this->request['bSortable_'.intval($this->request['iSortCol_'. $i])] == "true") {
                    $this->qb->addOrderBy(
                        $this->associations[$this->request['iSortCol_'.$i]]['fullName'],
                        $this->request['sSortDir_'.$i]
                    );
                }
            }
        }
    }

    /**
     * Configure the WHERE clause for the Doctrine QueryBuilder if any searches are specified
     *
     * @param QueryBuilder The Doctrine QueryBuilder object
     */
    public function setWhere(QueryBuilder $qb)
    {
        // Global filtering
        if (isset($this->search) && !empty($this->search)) {
            $orExpr = $qb->expr()->orX();
            for ($i=0 ; $i < count($this->parameters); $i++) {
                if (isset($this->request['bSearchable_'.$i]) && $this->request['bSearchable_'.$i] == "true") {
                    $qbParam = "sSearch_global_{$this->associations[$i]['entityName']}_{$this->associations[$i]['fieldName']}";
                    $orExpr->add($qb->expr()->like(
                        $this->associations[$i]['fullName'],
                        ":$qbParam"
                    ));
                    $qb->setParameter($qbParam, "%" . $this->request['sSearch'] . "%");
                }
            }
            $qb->where($orExpr);
        }

        // Individual column filtering
        $andExpr = $qb->expr()->andX();
        for ($i=0 ; $i < count($this->parameters); $i++) {
            if (isset($this->request['bSearchable_'.$i]) && $this->request['bSearchable_'.$i] == "true" && !empty($this->request['sSearch_'.$i])) {
                $qbParam = "sSearch_single_{$this->associations[$i]['entityName']}_{$this->associations[$i]['fieldName']}";
                $andExpr->add($qb->expr()->like(
                    $this->associations[$i]['fullName'],
                    ":$qbParam"
                ));
                $qb->setParameter($qbParam, "%" . $this->request['sSearch_'.$i] . "%");
            }
        }
        if ($andExpr->count() > 0) {
            $qb->andWhere($andExpr);
        }
    }

    /**
     * Configure joins for entity associations
     *
     * @param QueryBuilder The Doctrine QueryBuilder object
     */
    public function setAssociations(QueryBuilder $qb)
    {
        foreach ($this->assignedJoins as $joinName => $joinInfo) {
            $joinType = isset($this->joinTypes[$joinInfo['mdataColumn']]) ?
                $this->joinTypes[$joinInfo['mdataColumn']] :  $this->defaultJoinType;
            call_user_func_array(array($qb, $joinType . 'Join'), array(
                $joinInfo['joinOn'],
                $joinName
            ));
        }
    }

    /**
     * Configure the specific columns to select for the query
     *
     * @param QueryBuilder The Doctrine QueryBuilder object
     */
    public function setSelect(QueryBuilder $qb)
    {
        $columns = array();
        $partials = array();

        // Combine all columns to pull
        foreach ($this->associations as $column) {
            $parts = explode('.', $column['fullName']);
            $columns[$parts[0]][] = $parts[1];
        }

        // Partial column results on entities require that we include the identifier as part of the selection
        foreach ($this->identifiers as $joinName => $identifiers) {
            if (!in_array($identifiers[0], $columns[$joinName])) {
                array_unshift($columns[$joinName], $identifiers[0]);
            }
        }

        // Make sure to include the identifier for the main entity
        $identifiers = $this->metadata->getIdentifierFieldNames();
        if (!in_array($identifiers[0], $columns[$this->tableName])) {
            array_unshift($columns[$this->tableName], $identifiers[0]);
        }

        foreach ($columns as $columnName => $fields) {
            $partials[] = 'partial ' . $columnName . '.{' . implode(',', $fields) . '}';
        }

        $qb->select(implode(',', $partials));
        $qb->from($this->metadata->getName(), $this->tableName);
    }

    /**
     * Method to execute after constructing this object. Configures the object before
     * executing getSearchResults()
     */
    public function makeSearch() 
    {
        $this->setSelect($this->qb);
        $this->setAssociations($this->qb);
        $this->setWhere($this->qb);
        $this->setOrderBy();
        $this->setLimit();
    }

    /**
     * Check if an array is associative or not.
     *
     * @link http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-numeric
     * @param array An arrray to check
     * @return bool true if associative
     */
    protected function isAssocArray(array $array) {
        return (bool)count(array_filter(array_keys($array), 'is_string'));
    }

    /**
     * Execute the QueryBuilder object, parse the results, and send back the
     * DataTable data
     *
     * @return array Data results for DataTables.js
     */
    public function getSearchResults()
    {
        $this->fresults = $this->qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
        $output = array("aaData" => array());

        foreach ($this->fresults as $item) {
            // Go through each requested column, transforming the array as needed for DataTables
            for ($i = 0 ; $i < count($this->parameters); $i++) {
                // Results are already correctly formatted if this is the case...
                if (!$this->associations[$i]['containsCollections']) {
                    continue;
                }

                $rowRef = &$item;
                $fields = explode('.', $this->parameters[$i]);

                // Check for collection based entities and format the array as needed
                while ($field = array_shift($fields)) {
                    $rowRef = &$rowRef[$field];
                    // We ran into a collection based entity. Combine, merge, and continue on...
                    if (!empty($fields) && !$this->isAssocArray($rowRef)) {
                        $children = array();
                        while ($childItem = array_shift($rowRef)) {
                            $children = array_merge_recursive($children, $childItem);
                        }
                        $rowRef = $children;
                    }
                }
            }
            $output['aaData'][] = $item;
        }

        $outputHeader = array(
            "sEcho" => (int) $this->echo,
            "iTotalRecords" => $this->getCountAllResults(),
            "iTotalDisplayRecords" => (string) $this->getCountFilteredResults()
        );

        return array_merge($outputHeader, $output);
    }

    /**
     * @return int Total query results before searches/filtering
     */
    public function getCountAllResults()
    {
        return (int) $this->repository->createQueryBuilder($this->tableName)
            ->select('count(' . $this->tableName . '.id)')->getQuery()->getSingleScalarResult();
    }
    
    /**
     * @return int Total query results after searches/filtering
     */
    public function getCountFilteredResults()
    {
        $qb = $this->repository->createQueryBuilder($this->tableName);
        $qb->select('count(' . $this->tableName . '.id)');
        $this->setAssociations($qb);
        $this->setWhere($qb);
        return (int) $qb->getQuery()->getSingleScalarResult();
    }

    public function getOffset()
    {
        return $this->offset;
    }

    public function getEcho()
    {
        return $this->echo;
    }

    public function getAmount()
    {
        return $this->amount;
    }

    public function getSearch()
    {
        return  "%" . $this->search . "%";
    }

    public function getQueryBuilder()
    {
        return  $this->qb;
    }
}

Use like so within a Symfony2 controller (assumes FOSRestBundle)...

public function getAcmeDatatableAction(Request $request)
{
     $repository = $this->getDoctrine()->getRepository('AcmeDemoBundle:SomeEntity');
     $metadata = $this->getDoctrine()->getEntityManager()->getClassMetadata('AcmeDemoBundle:SomeEntity');
     $dataTable = new Datatable(
         $request->query->all(),
         $repository,
         $metadata,
         $this->getDoctrine()->getEntityManager()
     );
     $dataTable->makeSearch();

     $view = View::create()
         ->setStatusCode(200)
         ->setData($dataTable->getSearchResults());

     return $this->get('fos_rest.view_handler')->handle($view);
}

This is great work, Chad. Thanks for the update. However, my problem still persists. I am trying to get information from an entity (Branch) that has a One-to-Many relationship with itself and that association is mapped by a field called "parent_id". Like in,
/**
* @Orm\ManyToOne(targetEntity="Branch", inversedBy="children")
* @Orm\JoinColumn(name="parent_id", referencedColumnName="id")
*/
I have getter/setter in place for this. When I try to get columns { "mData": "id" }, { "mData": "branch_name" } and { "mData": "parent.id" }, I get two errors as follows:

[1] Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 129 near 'Branches ORDER': Error: 'Branches' is already defined.

[2] Doctrine\ORM\Query\QueryException: SELECT Branches FROM AALCO\CompanyBundle\Entity\Branch Branches INNER JOIN Branches.parent Branches ORDER BY Branches.id asc

Am I doing something wrong? Is this supposed to work? Thanks again for your effort. Ciao.

Made a temporary fix by changing the alias in the setAssociations function with a hard-coded if block.

if(lcfirst($this->associations[$i]['entityName'])=='parent') {
$alias = "Parents";
} else {
$alias = $this->associations[$i]['tableName'];
}

As you can see, I'm not that good :)
Also, I needed a leftJoin, but got an error as I have null values in the parent_id column. I have hard-coded, once again :), a return value for "$targetItem" to fix that. Thanks for the help mate. Take care.

@gkalyan Ah, OK. I think I understand the issue now. Please try again. I changed how the join names are constructed. I never took self-referencing relationships into account. Also, you should be able to change the join type now by doing... (Umm..implemented but not tested that is :p)

     $repository = $this->getDoctrine()->getRepository('AcmeDemoBundle:SomeEntity');
     $metadata = $this->getDoctrine()->getEntityManager()->getClassMetadata('AcmeDemoBundle:SomeEntity');
     $dataTable = new Datatable(
         $request->query->all(),
         $repository,
         $metadata,
         $this->getDoctrine()->getEntityManager()
     );
     // The default type for all joins is inner. Change it to left if desired.
     // $dataTable->setDefaultJoinType(Datatable::JOIN_LEFT);

     // Can set JOIN_LEFT or JOIN_INNER on a per-column basis
     $dataTable->setJoinType('parent.id', Datatable::JOIN_LEFT);
     $dataTable->makeSearch();

@gkalyan And apparently my brain isn't functioning too well at the moment...but can you explain what you had to hard code for the null values for the parent.id column? You're detecting that line 400 returns a null value and instead return some other value? I guess I should really setup some test entities.

@ChadSikorra Looking great. Also, I'm just doing a if ($targetItem==null) return "--"; in the getDataSingle() function before $targetReflection is initialized. Thanks for all the help.

@gkalyan Ah ok, that makes sense. I think you could return null instead of '--'. Then in your mData for DataTables.js you would define sDefaultContent for when the value is null (see http://www.datatables.net/ref#sDefaultContent). That way if you have something else returning null you can more easily display what you want. I'll have to update that later.

@ChadSikorra Hi Chad, hope I'm not being a bother. I'm just wondering if you could help me understand how to filter results on the server side with a custom doctrine QueryBuilder. I've been trying this for some time but finding it hard to get that to work. Everything is peachy on the client-side, I just need to filter results before rendering in the table. Thanks for any help with this. Take care.

@gkalyan Nah, don't worry about it. I've actually wanted to build in the ability to do that as well. I have some code I'm working on so a user can construct a filter dynamically based off a given entity (Currently in a private git repository I have not yet made public) but have not yet built a way turn that filter into a usable Doctrine2 based expr objects to be applied to this object. I was a little tied up with some personal stuff the past 2 months. Hopefully I'll have a little more time to get something figured out in the coming few weeks or month. I'd like to move this class to its own bundle that uses a service. I've now updated the code so that it uses Doctrine 2 partial objects to be more efficient with queries and eliminates the need for reflection objects altogether when constructing data to return.

To filter the query builder, a few additional methods would need to be added. Something like an 'addFilter' method that should accept additional doctrine based expr objects, so it can store the needed filters and reapply them as needed when it constructs/returns DataTable results.

@ChadSikorra Hi Chad, actually I got data filtering to work by constructing a QB for a specific entity and passing it to the Datatable class as an argument. I guess that's not a well-rounded solution, but nevertheless it is something that works for me now. However, doing the same with the new code above, gives me an error like "request: Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 173 near 'Companies ORDER': Error: 'Companies' is already defined. (uncaught exception) at /media/sf_sandbox/aalcodev/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 49". I have added my action and the constructor for Datatable at https://gist.github.com/4116866. Please take a look at it when you are free. Thanks.

In aoColumnDefs you must define sDefaultContent="" to avoid a "Requested unknown parameter from the data source" error, when loading the table on client side...