symfony 2 doctrine 2

Question

I create method for select current premises foto, all works good

public function findByDistrictIds($criteria)
{
    $q = $this->getEntityManager()->
            createQuery('SELECT f.id, f.path FROM RealestateCoreBundle:Foto f WHERE f.premises in (:criteria)');
    $q->setParameter('criteria', $criteria);
    return $q->getResult();
}

but when I add to select command "f.premises", get error

[Semantical Error] line 0, col 23 near 'premises FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

How i can get foto with premises_id? And how use "WHERE IN" clause in queryBuilder?

Answer 1

Ah. This came up recently. Premises is a relation field so you need to do a fetch join. Note the Premises alias p in the field list:

SELECT f.id, f.path, p 
  FROM RealestateCoreBundle:Foto f 
  JOIN f.premises p 
 WHERE ...

Hope this helps :)

Answer 2

public function findByDistrictIds($criteria)
{
    $q = $this->getEntityManager()->
    createQuery('SELECT f.id, f.path FROM RealestateCoreBundle:Foto f WHERE f.premises =     :criteria');
    $q->setParameter('criteria', $criteria);
    return $q->getResult();
}

You can try to use f.premises = :criteria
instead of f.premises in (:criteria)

Answer 3

Regarding the WHERE IN in query builder, you can pass $criteria as an array to setParameter() method. Also make sure that array is in this format.

$criteria = array('1','2','3'); /* correct */

$criteria = array('1,2,3'); /* incorrect. Some people misunderstand this and set the array like this which is incorrect */

So your query would be something like this,

$q = $this->getEntityManager()->
        createQuery('
                    SELECT f.id, f.path FROM RealestateCoreBundle:Foto f 
                    WHERE f.premises in (:criteria)');
$q->setParameter('criteria', array('1','2','3'));

Hope this helps.