Symfony 2.7 - Column not found - many to many relation

user_8901621 0
Viewed 933

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.

symfony-2.7 symfony doctrine entitymanager php
3 Answers

Are you initialising your ArrayCollection in the _construct() method in Company.php?

// Entity/Company.php

public function __construct() {
    $this->representatives = new \Doctrine\Common\Collections\ArrayCollection();
}

...

See the doctrine documentation here.

user_45471770

Try to php app/console doctrine:schema:update --force

user_74226410
Related