I have set up a many to one relationship between a schools entity and students entity, I've linked the two together using a manyToOne relationship;
Students entity:
/**
* @var \Doctrine\Common\Collections\Collection
* @ManyToOne(targetEntity="Schools", inversedBy="Students")
* @JoinColumn(name="school_id", referencedColumnName="school_id")
*/
private $Schools;
Schools entity:
/**
* @var \Doctrine\Common\Collections\Collection
* @OneToMany(targetEntity="Students", mappedBy="Schools")
*/
private $Students;
This works as expected.
I am trying to write a query for the Students table which specifies the school_id but I don't know how to specify this within the query;
$qb->select('t.position')
->from('\Entities\Students', 't')
->where($qb->expr()->eq('t.studentId', ':student_id'))
->andWhere($qb->expr()->eq('t.Schools.schoolId', ':school_id')) // doesn't work
->setParameters(array('student_id' => $this->studentId, 'school_id' => $school_id));
I've attempted to access the school id through $Schools property I set up in the student identity but this throws an error.
As a temporary fix I've created a $schoolId property in the Students entity and am using this instead but surely this is not the correct way to do this.
Appreciate the help.
UPDATE
I got it to work by changing the line;
->andWhere($qb->expr()->eq('t.Schools.schoolId', ':school_id')) // doesn't work
To:
->andWhere($qb->expr()->eq('t.Schools', ':school_id'))
However the same approach doesn't work with the following query:
$qb->select('DISTINCT t.age, s.Schools') // Doesn't work
->from('\Entities\Schools', 't')
->innerJoin('t.Students', 's')
The following error is displayed:
[Semantical Error] line 0, col 28 near 'Schools FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.