How to specify several join conditions for 1:1 rel

Question

Documentation states:

class Cart
{
    // ...

    /**
     * @OneToOne(targetEntity="Customer", inversedBy="cart")
     * @JoinColumn(name="customer_id", referencedColumnName="id")
     */
    private $customer;

    // ...
}

This annotation represents such sql:

JOIN Customer c ON c.id = cart.customer_id

And the issue is that I need to add additional comparison there, like:

JOIN Customer c ON c.id = cart.customer_id AND c.anotherField = <constant>

Any solutions for that?

UPD:

the real additional condition I need for now is <const> BETWEEN c.f1 AND c.f2

Answer 1

you can use the WITH keyword to specify additional join conditions, as you can see in some of the examples.

i think this should get you going:

SELECT l, c FROM location
INNER JOIN Customer c
WITH CURRENT_TIMESTAMP() BETWEEN c.f1 AND c.f2
WHERE CURRENT_TIMESTAMP() BETWEEN l.f1 AND l.f2

i removed the ON clause because i think there's no need to explicitly specify the join's ON fields unless they are not the "standard" ones (id of each entity)

also notice the call to CURRENT_TIMESTAMP() which translates into MySQL's NOW(). check out a list of other pretty useful aggregate functions and expresions here

Answer 2

There doesn't seem to be any solution to your problem that Doctrine could do auto-magically.

Since @ficuscr already gave you a solution for queries, there's only one more thing to handle - check your additional criteria and return the Customer instance in your getter on success and NULL on failure to meet additional criteria.

class Cart
{
    const VALUE = '<some_constant_value>';
    /**
     * @OneToOne(targetEntity="Customer", inversedBy="cart")
     * @JoinColumn(name="customer_id", referencedColumnName="id")
     */
    private $customer;

    // ...

    /**
     * @return Customer|null
     */
    public function getCustomer()
    {
        if ($this->customer->getField1() <= self::VALUE
            && $this->customer->getField2() >= self::VALUE
        ) {
            return $this->customer;
        }

        return null;
    }
}

If this was a One-To-Many relation, Collection Filtering API (a.k.a. Criteria) could be used to filter the collection created by the mappings:

use Doctrine\Common\Collections\Criteria;

class Cart
{
    const VALUE = '<some_constant_value>';
    /**
     * @OneToMany(targetEntity="Customer", mappedBy="cart")
     */
    private $customers;

    // ...

    /**
     * @return Customer[]|ArrayCollection
     */
    public function getCustomers()
    {
        $expr = Criteria::expr();
        $criteria = Criteria::create()
            ->where($expr->andX(
                $expr->lte('field1', self::VALUE),
                $expr->gte('field2', self::VALUE)
            ));

        return $this->patientProblems->matching($criteria);
    }
}