symfony2 createQueryBuilder

Question

I am trying to make a really simple sql query from repository class, just select * from Adjudicacion where cursoAcademico_id=$cursoAcademicoActual;:

This is my entity:

/**
* Adjudicacion
*
* @ORM\Table(name="Adjudicacion")
* @ORM\Entity(repositoryClass="Administrador\AdjudicacionBundle\Entity\AdjudicacionRepository")
*/
class Adjudicacion {
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="fechaInicio", type="date")
     */
    private $fechaInicio;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="fechaFinal", type="date")
     */
    private $fechaFinal;


    /**
     * @ORM\ManyToOne(targetEntity="Administrador\CursoAcademicoBundle\Entity\CursoAcademico")
     */
    private $cursoAcademico;


    /**
     * @ORM\ManyToOne(targetEntity="Administrador\AdjudicacionClaseBundle\Entity\AdjudicacionClase")
     */
    private $adjudicacionClase;


    /**
     * @ORM\ManyToOne(targetEntity="Administrador\AdjudicacionNumeroBundle\Entity\AdjudicacionNumero")
     */
    private $adjudicacionNumero;


    /**
     * @ORM\ManyToOne(targetEntity="Administrador\AdjudicacionTipoBundle\Entity\AdjudicacionTipo")
     */
    private $adjudicacionTipo;

...getters and setters...

This is my repository class:

class AdjudicacionRepository extends EntityRepository {

public function findAdjudicacionesActuales($cursoAcademicoActual) {

    $q=$this->createQueryBuilder('c')
    ->where('c.cursoAcademico_id = :cursoAcademico_id')
    ->setParameter('cursoAcademico_id', $cursoAcademicoActual)
    ->getQuery()->getResult();

    return $q;


}

}

But it doesnt work, the screen is just blank and I dont get any result. I tried with criteria too, like this:

public function findAdjudicacionesActuales2($cursoAcademicoActual) {

    $expr = Criteria::expr();
    $criteria = Criteria::create();
    $criteria->where($expr->eq("cursoAcademico_id", $cursoAcademicoActual));
    return $this->matching($criteria);      

}

and i get: Unrecognized field: cursoAcademico_id

This is in the database:

mysql> select * from Adjudicacion;
+----+-------------+------------+-------------------+----------------------+-----------------------+---------------------+
| id | fechaInicio | fechaFinal | cursoAcademico_id | adjudicacionClase_id | adjudicacionNumero_id | adjudicacionTipo_id |
+----+-------------+------------+-------------------+----------------------+-----------------------+---------------------+
|  2 | 2009-01-01  | 2009-01-01 |                 7 |                    3 |                     4 |                   3 |
|  6 | 2009-01-01  | 2009-01-01 |                 7 |                    3 |                     4 |                   4 |
|  7 | 2009-01-01  | 2009-01-01 |                 7 |                    3 |                     5 |                   3 |
|  8 | 2009-01-01  | 2009-01-01 |                 7 |                    3 |                     5 |                   4 |
+----+-------------+------------+-------------------+----------------------+-----------------------+---------------------+

what is wrong?

Answer 1

Technically in the eyes of Doctrine ORM the cursoAcademico_id field doesn't exist. It is used to create a link between the 2 tables to create the object but you can't use it in anything.

To do a search for an object with the given id you should use a join and match the id of the joined object like..

$q=$this->createQueryBuilder('a')
    // Create builder in 'Adjudicacion' repository so 'a' rather than 'c'
    ->join('a.cursoAcademico', 'c')
    // Join 'Adjudicacion' to 'CursoAcademico'
    ->where('c.id = :cursoAcademico_id')
    // match id of joined `CursoAcademico`
    ->setParameter('cursoAcademico_id', $cursoAcademicoActual)
    ->getQuery()->getResult();

return $q;