CODEWITHSUNDEEP
symfonydoctrine-orminner-join
Sergio Negri
Sergio Negri

Reputation: 2083

Symfony2/Doctrine2 double join

I have the following situation: a table named HRMgmt contains events happening to a user, caused by another user, so in my entity class I have:

/**
 * @ORM\ManyToOne(targetEntity="User")
 */
protected $user;

/**
 * @ORM\ManyToOne(targetEntity="User")
 */
protected $changed_by;

I want to do a double join to get both id, name and surname of the user this event is related to and id, name and surname of the user who caused the HRMgmt event (called changed_by)

My query is built like follows:

$events = $this->getEntityManager()
        ->createQuery(
        'SELECT u1.id currentuserid, u1.name currentusername, u1.surname currentusersurname, u2.id changedbyuserid, u2.name changedbyusername, u2.surname changedbyusersurname, h.is_active, h.timestamp, h.started_at, h.finished_at, r.name rolename FROM AppBundle:HRMgmtEvent h, AppBundle:Role r
         INNER JOIN h.user u1
         INNER JOIN h.changed_by u2
         WHERE h.project = :projectid
         AND h.role = r.id
         ORDER BY h.timestamp DESC, h.is_active DESC' 
        )
        ->setParameter('projectid', $project->getId())
        ->getResult();

But throws the following error Column not found: 1054 Unknown column 'h2_.user_id' in 'on clause'

Is the join properly performed? What am I missing here?

The question is similar to the following: JOIN DQL (symfony2) multiple joins between two tables

Actually my case is even simpler as I cannot have null values...

My DB is in sync with my entities having run:

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

EDIT:

here is the schema of HRMgmt:

CREATE TABLE `hrmgmt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`project_id` int(11) DEFAULT NULL,
`changed_by_id` int(11) DEFAULT NULL,
`started_at` datetime DEFAULT NULL,
`finished_at` datetime DEFAULT NULL,
`is_active` tinyint(1) NOT NULL,
`timestamp` datetime NOT NULL,
`role_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_B3C74883A76ED395` (`user_id`),
KEY `IDX_B3C74883828AD0A0` (`changed_by_id`),
KEY `IDX_B3C74883166D1F9C` (`project_id`),
KEY `IDX_B3C74883D60322AC` (`role_id`),
CONSTRAINT `FK_B3C74883166D1F9C` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
CONSTRAINT `FK_B3C74883828AD0A0` FOREIGN KEY (`changed_by_id`) REFERENCES `fos_user` (`id`),
CONSTRAINT `FK_B3C74883A76ED395` FOREIGN KEY (`user_id`) REFERENCES `fos_user` (`id`),
CONSTRAINT `FK_B3C74883D60322AC` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1042 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here is the User:

CREATE TABLE `fos_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...several other fields...
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`surname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_957A647992FC23A8` (`username_canonical`),
UNIQUE KEY `UNIQ_957A6479A0D96FBF` (`email_canonical`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Thank you!

Upvotes: 0

Views: 301

Answers (1)

lsouza
lsouza

Reputation: 2488

Both properties are mapped to the same field on the DB, user_id, which is the default mapping of Doctrine on ManyToOne associations (model_id). To solve that you should pick different column names for each property, like this:

/**
 * @ORM\ManyToOne(targetEntity="User")
 * @ORM\JoinColumn(name="user_id")
 */
protected $user;

/**
 * @ORM\ManyToOne(targetEntity="User")
 * @ORM\JoinColumn(name="changed_by_id")
 */
protected $changed_by;

After that you should be able to do your join.

Upvotes: 0

Related Questions