Doctrine 2 joining table, ManyToOne unidirectional, where tbl2.value = :value

Question

I'm trying to figure out how to join two tables, while querying the second table. I thought it was as simple as:

// Within ServerServiceRepository 
return $this->createQueryBuilder('ss')
    ->join(ServiceType::class, 't')
    ->where('t.serviceTypeName = :name')
    ->getQuery()
    ->execute(['name' => $name]);

But turns out, not so much...

The issue is the query is NOT joining the keys (service_type_id on both tables). What's going on here? I have all the OneToMany relationships setup correctly:

/**
 * ServerService
 *
 * @ORM\Table(name="server_services")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\ServerServiceRepository")
 */
class ServerService extends AbstractEntity
{
    /**
     * @var ServiceType
     *
     * @ORM\Column(name="service_type_id", type="integer", nullable=false)
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Supportal\ServiceType", fetch="LAZY", inversedBy="serviceTypeId")
     * @ORM\JoinColumn(name="service_type_id", referencedColumnName="service_type_id")
     */
    private $serviceType;
    // [...]
 }

/**
 * ServiceType
 *
 * @ORM\Table(name="service_types")
 * @ORM\Entity
 */
class ServiceType extends \AppBundle\Entity\AbstractEntity
{
    /**
     * @var integer
     *
     * @ORM\Column(name="service_type_id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\ServerService", fetch="EXTRA_LAZY", mappedBy="serviceType")
     */
    private $serviceTypeId;

    /**
     * @var string
     *
     * @ORM\Column(name="service_type_name", type="string", length=255, nullable=true)
     */
    private $serviceTypeName;
    // [...]

}

I've added / removed the OneToMany relationship from ServiceType to no change. This is really a unidirectional relationship. Per Doctrine's own docs (Chapter 5), ServerType does not require a relationship mapping.

The SQL query is generating a JOIN that's missing the actual keys:

INNER JOIN service_types s1_ ON (s1_.service_type_name = ?)

What am I missing here on Doctrine to get this working right? I've looked at the tutorials. Symofny's example is almost exact what I'm after, except I need to select by "category name" not Product id: https://symfony.com/doc/2.8/doctrine/associations.html

I've got to be missing something so super simple. But I can't for the life of me peg it...

Edit:

I've removed the OneToMany from ServiceType in my code. It's optional. Not needed for this anyway. This is a unidirectional relationship.

I've tried this:

    return $this->createQueryBuilder('ss')
        ->join('ss.serviceType', 't')
        ->where('t.serviceTypeName = :name')
        ->getQuery()
        ->execute(['name' => $name]);

Resulting in this error:

[Semantical Error] line 0, col 85 near 't WHERE t.serviceTypeName': Error: Class AppBundle\Entity\ServerService has no association named serviceType

Solution

The solution was removing the @ORM Column definition. Looks like it's a conflict in the relationship definitions.

Answer 1

First of all, change the ManyToOne docblock definition to:

 /**
 * @var ServiceType
 *
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Supportal\ServiceType", fetch="LAZY", inversedBy="serverService")
 * @ORM\JoinColumn(name="service_type_id", referencedColumnName="service_type_id")
 */
private $serviceType;

Also change the OneToMany, remove line @ORM\OneToMany(targetEntity="AppBundle\Entity\ServerService", fetch="EXTRA_LAZY", mappedBy="serviceType")

Create new field serverService for the OneToMany relation:

/**
 * 
 * @ORM\OneToMany(targetEntity="ServerService", mappedBy="serviceType")
 */
private $serverService;

You should join on the relation field, in this case serviceType. The way you defined the join is like selecting both tables.

Change to:

return $this->createQueryBuilder('ss')
    ->join('ss.serviceType', 't')
    ->where('t.serviceTypeName = :name')
    ->getQuery()
    ->execute(['name' => $name]);

Since you are applying a condition on the joined result here, using a LEFT JOIN or simply JOIN is the same.

References:

How to Work with Doctrine Associations / Relations

how to do left join in doctrine

Left join ON condition AND other condition syntax in Doctrine