using custom type in Criteria expression #5907

New Issue

admin · 2026-01-22T15:21:39+01:00

admin commented

2026-01-22 15:21:39 +01:00

Originally created by @tklaas on GitHub (Feb 28, 2018).

Originally assigned to: @ostrolucky on GitHub.

I created a custom type "unixtime" (see end of post) that let me use my old database with timestamps saved as simple integer instead of datetime fields.

in my doctine entity i have:

   /**
     * @var \DateTime
     * @ORM\Column(type="unixtime")
     */
    private $start;

    /**
     * @var \DateTime
     * @ORM\Column(type="unixtime")
     */
    private $end

I created a criteria in my PromotionRepository class that checks these fields to define my condition in only one place

   public static function createActiveCriteria($alias = null)
    {
        $now = new \DateTime();
        $alias = ($alias ? $alias.'.' : '');
        return Criteria::create()
            ->andWhere(Criteria::expr()->lt($alias.'start', $now))
            ->andWhere(Criteria::expr()->gt($alias.'end', $now))
            ;
    }

This method is used in 2 cases to check if a promotion is active on a product

in my product entity to filter the list of all promotions for this product:

    /**
     * @var Promotion[]|ArrayCollection
     * @ORM\OneToMany(targetEntity="App\Entity\Promotion", mappedBy="conditionProduct")
     */
    private $promotions;

    /**
     * @return Promotion|false
     */
    public function getActivePromotion()
    {
        return $this->promotions->matching(
            PromotionRepository::createActiveCriteria()
        )->first();
    }

in my PromotionRepository

    /**
     * @return Promotion[]
     */
    public function findAllActivePromotions()
    {
        $qb = $this->createQueryBuilder('promotion')
            ->addCriteria(PromotionRepository::createActiveCriteria())
        ;
        return $qb->getQuery()->execute();
    }

In Symfony Profiler, I can see, that SQL queries for the first case (using on ArrayCollection) contain the correct timestamp as integer, in the second case (using in query) it is converted to a DateTime String "2018-02-28 10:49:27"

A similar issues was already mentioned in https://github.com/doctrine/doctrine2/issues/5695 and the workaround was to set the Type explicitely on "setParameter" but I do not see a way to set the type on my Criteria

My custom type
registered in doctrine.yaml

doctrine:
    dbal:
        types:
            unixtime: App\Doctrine\Type\UnixtimeType

class UnixtimeType extends Type
{

    const UNIXTIME = 'unixtime';

    /**
     * Gets the SQL declaration snippet for a field of this type.
     *
     * @param array                                     $fieldDeclaration The field declaration.
     * @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform         The currently used database platform.
     *
     * @return string
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return $platform->getIntegerTypeDeclarationSQL($fieldDeclaration);
    }

    /**
     * @param \DateTime            $value
     * @param AbstractPlatform $platform
     *
     * @return int
     */
    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if($value instanceof \DateTime) {
            return $value->getTimestamp();
        }
        return 0;
    }

    /**
     * @param mixed            $value
     * @param AbstractPlatform $platform
     *
     * @return \DateTime
     */
    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return date_create()->setTimestamp($value);
    }

    public function getBindingType()
    {
        return \PDO::PARAM_INT;
    }

    /**
     * Gets the name of this type.
     *
     * @return string
     */
    public function getName()
    {
        return self::UNIXTIME;
    }
}

Originally created by @tklaas on GitHub (Feb 28, 2018). Originally assigned to: @ostrolucky on GitHub. I created a custom type "unixtime" (see end of post) that let me use my old database with timestamps saved as simple integer instead of datetime fields. in my doctine entity i have: ```php /** * @var \DateTime * @ORM\Column(type="unixtime") */ private $start; /** * @var \DateTime * @ORM\Column(type="unixtime") */ private $end ``` I created a criteria in my PromotionRepository class that checks these fields to define my condition in only one place ```php public static function createActiveCriteria($alias = null) { $now = new \DateTime(); $alias = ($alias ? $alias.'.' : ''); return Criteria::create() ->andWhere(Criteria::expr()->lt($alias.'start', $now)) ->andWhere(Criteria::expr()->gt($alias.'end', $now)) ; } ``` This method is used in 2 cases to check if a promotion is active on a product 1. in my product entity to filter the list of all promotions for this product: ```php /** * @var Promotion[]|ArrayCollection * @ORM\OneToMany(targetEntity="App\Entity\Promotion", mappedBy="conditionProduct") */ private $promotions; /** * @return Promotion|false */ public function getActivePromotion() { return $this->promotions->matching( PromotionRepository::createActiveCriteria() )->first(); } ``` 2. in my PromotionRepository ```php /** * @return Promotion[] */ public function findAllActivePromotions() { $qb = $this->createQueryBuilder('promotion') ->addCriteria(PromotionRepository::createActiveCriteria()) ; return $qb->getQuery()->execute(); } ``` In Symfony Profiler, I can see, that SQL queries for the first case (using on ArrayCollection) contain the correct timestamp as integer, in the second case (using in query) it is converted to a DateTime String "2018-02-28 10:49:27" A similar issues was already mentioned in https://github.com/doctrine/doctrine2/issues/5695 and the workaround was to set the Type explicitely on "setParameter" but I do not see a way to set the type on my Criteria My custom type registered in doctrine.yaml ``` doctrine: dbal: types: unixtime: App\Doctrine\Type\UnixtimeType ``` ```php class UnixtimeType extends Type { const UNIXTIME = 'unixtime'; /** * Gets the SQL declaration snippet for a field of this type. * * @param array $fieldDeclaration The field declaration. * @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform The currently used database platform. * * @return string */ public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform) { return $platform->getIntegerTypeDeclarationSQL($fieldDeclaration); } /** * @param \DateTime $value * @param AbstractPlatform $platform * * @return int */ public function convertToDatabaseValue($value, AbstractPlatform $platform) { if($value instanceof \DateTime) { return $value->getTimestamp(); } return 0; } /** * @param mixed $value * @param AbstractPlatform $platform * * @return \DateTime */ public function convertToPHPValue($value, AbstractPlatform $platform) { return date_create()->setTimestamp($value); } public function getBindingType() { return \PDO::PARAM_INT; } /** * Gets the name of this type. * * @return string */ public function getName() { return self::UNIXTIME; } } ```

admin closed this issue

2026-01-22 15:21:40 +01:00

admin commented

2026-01-22 15:21:41 +01:00

@ostrolucky commented on GitHub (Mar 14, 2018):

This behaviour seems expected to me. You specified custom DBAL type on a specific field so doctrine knows how to convert value when writing/reading from the field. But it doesn't know what to do with value you are comparing that field with. You'll need to explicitly cast datetime object when using this parameter but wanting to treat it as unix time.

@ostrolucky commented on GitHub (Mar 14, 2018): This behaviour seems expected to me. You specified custom DBAL type on a specific field so doctrine knows how to convert value when writing/reading from the field. But it doesn't know what to do with value you are comparing that field with. You'll need to explicitly cast datetime object when using this parameter but wanting to treat it as unix time.

admin commented

2026-01-22 15:21:41 +01:00

@ostrolucky commented on GitHub (Aug 6, 2018):

Closing as explained

@ostrolucky commented on GitHub (Aug 6, 2018): Closing as explained

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5907