I am trying to create a DQL query by testing on the property of a property. The objective is to create a DQL query that will indicate in what role a user is fulfilling a job, returning no rows if the user does not have the proper role.
My Entities are Role, Job, and User. User's have roles, Jobs require a role (to fulfill them), and Roles have 'alternates' that link to another role that can fill in for that role.
Stubbed versions of the entites look like:
class User {
//Annotation not needed for question
protected $id;
/**
* @var SystemBundle\Entity\Role
*
* @ORM\ManyToMany(targetEntity="SystemBundle\Entity\Role")
* @ORM\JoinTable(name="User_User__Role",
* joinColumns={
* @ORM\JoinColumn(name="User_ID", referencedColumnName="ID")
* },
* inverseJoinColumns={
* @ORM\JoinColumn(name="Role_ID", referencedColumnName="ID")
* }
* )
*/
protected $roles;
}
class Job {
//Annotation not needed for question
protected $id;
/**
* @var SystemBundle\Entity\Role $jobRole
*
* @ORM\ManyToOne(targetEntity="Role")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="Role_ID", referencedColumnName="ID")
* })
*/
protected $jobRole;
}
class Role {
//Annotation not needed for question
protected $id;
/**
* @var SystemBundle\Entity\Role $jobRole
*
* @ORM\OneToOne(targetEntity="Role")
* @ORM\JoinColumn(name="BackupRole_ID", referencedColumnName="ID")
*/
protected $backUpRole;
}
The conceptual logic is to join Roles with Jobs, testing if the current Role IS job.JobRole or job.JobRole.backUpRole. I've tried this, and doctrine really doesnt seem to like the job.jobRole.backUpRole and throws this error at the second period:
[Syntax Error] line 0, col 210: Error: Expected =, <, <=, <>, >, >=, !=, got '.'
My DQL attempt looks like this
SELECT r
FROM SystemBundle:Role r
INNER JOIN ProcessBundle:Job j
WITH j = :j AND j.jobRole = r OR j.jobRole.backUpRole = r
LEFT JOIN UserBundle:User u
WITH r MEMBER OF u.roles
WHERE u = :emp AND u IS NOT NULL
ORDER BY r.id
I can accomplish this task with pure SQL, as well as just using php to walk the associations, but I am looking to stay true to use DQL (because it's vexing me and I want to know if it can be done).
If it helps, here is my pure SQL:
#get all roles
select r.*
from Sys_Role as r
inner join
#get the role assigned to the job
(select r.*
FROM Sys_Role as r
INNER JOIN Sys_Job as j
ON j.JobRole_ID = r.ID
WHERE j.ID = ?) as jr
#join roles based on the job-role's id or backup id
# this should filter the list of roles down to the role and backup role for the job
on ar.ID = aar.ID OR ar.ID = aar.BackUpRole_ID
# and filter by the roles the user is assigned
INNER JOIN User_User__Role as uur
ON r.ID = uur.Role_ID
WHERE uur.User_ID = ?
EDIT: My apologies. While editing the question layout i accidently deleted the DQL. I've added that back the question!
UPDATE: I am exploring making the $backUpRole a bidirectional self-join and attacking from that direction. Doctrine does not like the naive attempt, so it looks like if this tact is used, fresh DQL is needed.
Changing the DQL to WITH j = :j AND j.jobRole = r OR j.jobRole = r.roleIBackUp yeilds a new error: A single-valued association path expression to an inverse side is not supported in DQL queries. Use an explicit join instead.
I figured it out. It IS possible... you need to join the sub-objects before you can use them. I had to switch up the order of my joins and I'm not terribly happy with my filters being in WITH clauses instead of WHERE, but it's hard to argue with results!
SELECT r
FROM SystemBundle:Role r
LEFT JOIN UserBundle:User u
WITH u = :emp
INNER JOIN ProcessBundle:Job j
WITH j = :job
JOIN j.jobRole jr
JOIN jr.backUpRole jrbr
WHERE (jr = ar OR jrbr = ar) AND r MEMBER OF u.role
The last 2 Joins let us alias the objects that belong to the linked entities so we can use them to compare.
The "30,000 foot view" of how this DQL works is such:
Kind of weird, but fairly simple once you get it.