Doctrine: How to join on a specific row?

Question

Lets take a look at my classes. As you can see, Model has Batteries, which is a ManyToMany relationship. A Model also has 1 Battery which is one of the Batteries with the highest count.

I want to select all the models for which Battery(not one of the Batteries) is larger than 1500mAh for example. I was thinking to join the Battery on Model, but this means all the Batteries will join. This seems to me as a performance waste because I already know which Battery I want to join. Is there any way to join on a specific row?

Model:

    /**
 * @Entity
 **/

class Model{
    /**
     * @Id @Column(type="integer")
     * @GeneratedValue
     */
    protected $id;

    /**
     * @Column(type="string")
     */
    protected $name;

    /**
     * @Column(type="string", nullable=true)
     */
    protected $alias;


    /**
     * @ManyToMany(targetEntity="Battery", cascade={"persist"})
     * @JoinTable(joinColumns={@JoinColumn(referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(referencedColumnName="id", unique=true)}
     *      )
     */
    protected $batteries;

    /**
     * @OneToOne(targetEntity="Battery")
     * @JoinColumn(referencedColumnName="id", nullable=true)
     */
    protected $battery;
}

Battery:

/**
 * @Entity
 **/

class Battery{
    /**
     * @Id @Column(type="integer")
     * @GeneratedValue
     */
    protected $id;

    /**
     * @Column(type="integer")
     */
    protected $mah;

    /**
     * @Column(type="integer")
     */
    protected $count;
}

Answer 1

You can specify a condition in your join:

$queryBuilder = $this->createQueryBuilder('model')
                     ->select('model')
                     ->from('Model', 'model')
                     ->join('model.battery', 'battery', 'WITH', 'battery.mah > 1500');

DQL version:

$query = $entityManager->createQuery('SELECT model FROM Model model JOIN model.battery battery WITH battery.mah > 1500');