Symfony 2.7 - Column not found - many to many relation

Question

Problem: I'm trying to get all Companies related to a user by providing a user ID to my query.

The error I'm getting:

CRITICAL - Uncaught PHP Exception Doctrine\DBAL\Exception
\InvalidFieldNameException: "An exception occurred while executing 'SELECT
t0.id AS id_1, t0.name AS name_2, t0.phone AS phone_3, t0.fax AS fax_4, 
t0.country AS country_5, t0.address AS address_6, t0.zipcode AS zipcode_7, 
t0.state AS state_8, t0.city AS city_9, t0.notes AS notes_10 FROM company t0
 WHERE company_representatives.user_id = ?' with params [177]: 
SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'company_representatives.user_id' in 'where clause'" at C:\wamp64
\www\Portail\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver
\AbstractMySQLDriver.php line 71 

I have an entity "Company" which has a ManyToMany relation with my entity "User"

Company.php

 /**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="Dbm\UserBundle\Entity\User")
 * @ORM\JoinTable(name="company_representatives")
 */
private $representatives;

User.php has no relation with Company in his entity class.

Because of my ManyToMany relation, I do have a company_representatives table which contains "company_id" and "user_id"

The code below is what causes the "Column not found" error.

$companies = $em->getRepository('DbmPortalBundle:Company')->findBy(array('representatives' => $user->getId()));

-Cache has been cleared

-[Mapping] and [Database] = OK when using doctrine:schema:validate

-I instantiate representatives as an ArrayCollection in my constructor

EDIT

I used a workaround which will do the trick for now. I'll clearly want to fix this later. My workaround is to use raw SQL directly on my "company_representatives" table to find all companies' id linked to my user_id. With that I can then use a "FindBy" with their ID on my company repository to get all companies' objects.

Answer 1

You cannot use findBy(or any find methods) with a Many to Many relation(maybe it will be supported in the future). Your condition is correctly constructed "WHERE company_representatives.user_id = ?' with params [177]" by doctrine but no join with company_representatives is added.

What you want is to get all companies of one user, I would suggest to add the relation to the User entity also:

/**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="Company", inversedBy="representatives")
 * @ORM\JoinTable(name="company_representatives")
 */
private $companies;

public function __construct()
{
    $this->companies = new ArrayCollection();
}

public function getCompanies()
{
    return $this->companies;
}

Finally you can get the companies directly from the user entity:

$user->getCompanies();