Doctrine2 - How can I order by a discriminator column?

Question

How should I go about ordering by a discriminator column in a doctrine repository query?

I have a pretty straight forward setup, I have different types of payment details, it can either be Credit Card (CC) or Debit Order (DO).

So I've implemented a single table inheritance mapping strategy to achieve this, but the problem now comes in when I try to order by the discriminator column, since the discriminator column isn't present in the base class.

The repository function:

public function getPaymentDetails (ClientContactInterface $clientContact)
{
    $dql = 'SELECT pd
            from
            AccountingBundle:PaymentDetail pd
            JOIN ClientProductBundle:ClientProduct cp
            WITH cp.payment_detail_id = pd.id
            WHERE
            cp.payment_detail_id = pd.id
            and cp.client_contact_id = :client_contact_id
            GROUP BY pd.id
            ORDER BY pd.method_type'; // Since pd.method_type is the discriminator column, I cannot order by it. And I need to be able to.

    $em = $this->getEntityManager();
    $query = $em->createQuery($dql)->setParameter('client_contact_id', $clientContact->getId());
    return $query->getResult();
}

Base PaymentDetail entity:

/**
 * @ORM\Entity(repositoryClass="AccountingBundle\Repository\PaymentDetailRepository")
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\Table(name="PaymentDetails")
 * @ORM\DiscriminatorColumn(name="PaymentMethodType", type="string")
 * @ORM\DiscriminatorMap({ "DO" = "DOPaymentDetail", "CC" = "CCPaymentDetail"})
 */

class PaymentDetail implements PaymentDetailInterface
{

    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected  $id;

    /* etc... */
}

Debit Order PaymentDetail entity:

/**
 * AccountingBundle\Entity\DOPaymentDetail
 *
 * @ORM\Table(name="PaymentDetails")
 * @ORM\Entity
 */
class DOPaymentDetail extends PaymentDetail implements DOPaymentDetailInterface
{

    /**
     * @var string $account_holder
     *
     * @ORM\Column(name="DOAccountHolder", type="string", length=255)
     */
    protected $account_holder;

    /* etc... */
}

Credit Card PaymentDetail entity:

/**
 * AccountingBundle\Entity\CCPaymentDetail
 *
 * @ORM\Table(name="PaymentDetails")
 * @ORM\Entity
 */
class CCPaymentDetail extends PaymentDetail implements CCPaymentDetailInterface
{

    /**
     *
     * @var string $card_holder
     *
     * @ORM\Column(name="CCCardHolder", type="string", length=255)
     */
    protected $card_holder;

    /* etc... */
}

When I try that, I get this error,

Error: Class AccountingBundle\Entity\PaymentDetail has no field or association named method_type") 

Gravimeter

look at your discriminator-column declaration * @DiscriminatorColumn(name="PaymentMethodType" and check what's the name of the generated column in your database ... I'm pretty sure the column-/field-name is PaymentMethodType and not method_type. Can you confirm this? – Chalcis

I can confirm this. But, the problem keeps coming in when I try to order by a column that technically doesn't exist in Doctrine's eyes, I tried changing method_type to PaymentMethodType in the repo function, and I got... ("[Semantical Error] line 0, col 388 near 'PaymentMethodType': Error: 'PaymentMethodType' is not defined.") – Gravimeter

possibly related: #32701772 – Hollister

I think it is answered at #5989136 – Couscous

@Couscous - the answer you reference is about using a discriminator in a WHERE clause. This question is about using it in an ORDER BY clause. – Trephine

Answer 1

Even though TYPE is not implemented yet into the core of doctrine it's still possible to implement it as a custom user function.

Someone already did an amazing job and implemented it for us. Just in case the resource will be removed in the future, here is a slightly adjusted version for php7+:

<?php
use Doctrine\ORM\Mapping\ClassMetadataInfo;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\QueryException;
use Doctrine\ORM\Query\SqlWalker;

class TypeFunction extends FunctionNode
{
    /**
     * @var string
     */
    public $dqlAlias;

    public function getSql(SqlWalker $sqlWalker): string
    {
        /** @var ClassMetadataInfo $class */
        $class = $sqlWalker->getQueryComponent($this->dqlAlias)['metadata'];
        $tableAlias = $sqlWalker->getSQLTableAlias($class->getTableName(), $this->dqlAlias);

        if (!isset($class->discriminatorColumn['name'])) {
            $message = 'TYPE() only supports entities with a discriminator column.';
            throw QueryException::semanticalError($message);
        }

        return $tableAlias . '.' . $class->discriminatorColumn['name'];
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->dqlAlias = $parser->IdentificationVariable();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Now you can order by the discriminator column by doing something like:

SELECT e, TYPE(e) AS HIDDEN my_type FROM Entity e ORDER BY my_type DESC;

Phototonus

Answer 2

It seems like the simplest solution (so far) is to add another field to the base class and copy the discriminator column value.

The aforementioned TYPE(q) works only in WHERE clause.

Yongyoni

Answer 3

Can your try to use TYPE() or INSTANCE OF?

related: https://groups.google.com/forum/#!topic/doctrine-user/JtCbwuN-37o

However, this topic doesn't say if it is implemented or not. At the time it was written someone said order by wouldn't work.

$dql = 'SELECT pd
        from
        AccountingBundle:PaymentDetail pd
        JOIN ClientProductBundle:ClientProduct cp
        WITH cp.payment_detail_id = pd.id
        WHERE
        cp.payment_detail_id = pd.id
        and cp.client_contact_id = :client_contact_id
        GROUP BY pd.id
        ORDER BY TYPE(pd)';

Rist

TYPE() is still not implemented AFAIK – Trephine