[DCOM-288] Doctrine uses = operator instead of <= Created: 29/Jun/15  Updated: 03/Jul/15

Status: Open
Project: Doctrine Common
Component/s: Collections
Affects Version/s: 2.5.0
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Mike Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None

Windows 7.0 for dev, MySQL5.6


I'm trying to force Doctrine to use LTE operator on activeFrom field (datetime field), but instead EQ operator is used while matching() the many-to-many relation:

     * @return SubjectInterface[]
    public function getSubjects()
        $expr = Criteria::expr();

        return $this->subjects->matching(
                ->where($expr->eq('deleted', 0))
                ->andWhere($expr->eq('active', 1))
                // used date() here instead of DateTime, because I get errors with it
                ->andWhere($expr->lte('activeFrom', date('Y-m-d H:i:s'))

creates following SQL query:

SELECT te.id AS id, te.name AS name, te.active AS active, te.activeFrom AS activeFrom, te.position AS position, te.shortDescription AS shortDescription, te.description AS description, te.deleted AS deleted, te.dateCreated AS dateCreated, te.hasChat AS hasChat, te.owner_id AS owner_id FROM subject te JOIN subject_users t ON t.subject_id = te.id WHERE t.user_id = '6' AND te.deleted = '0' AND te.active = '1' AND te.activeFrom = '2015-06-29 13:50:53'

Doctrine schema validator says my mappings are ok.

// user entity

     * @var Subject[]|ArrayCollection
     * @ORM\ManyToMany(targetEntity="Subject", inversedBy="users", indexBy="id")
     * @ORM\JoinTable(name="subject_users")
     * @ORM\OrderBy({"name"="asc"})
    protected $subjects;

// subject entity

     * @var \DateTime
     * @ORM\Column(type="datetime")
    protected $activeFrom;

     * @var User[]|ArrayCollection
     * @ORM\ManyToMany(targetEntity="User", mappedBy="subjects", fetch="EXTRA_LAZY", indexBy="id")
     * @ORM\OrderBy({"surname" = "ASC"})
     * @ORM\JoinTable(name="subject_users",
     *     joinColumns={@ORM\JoinColumn(name="subject_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}
     * )
    protected $users;

Comment by Mike [ 30/Jun/15 ]

I wonder, if you could please tell me where query generation takes place I could debug what's happening under the hood. Probably I am not doing something right.

Generated at Tue Jul 07 17:46:43 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.