CODEWITHSUNDEEP
mysqlsymfonydoctrine-orm
Michael Emerson
Michael Emerson

Reputation: 1813

Implementing a join in doctrine query builder not working correctly

I am trying to get information from one table based on information in another table, which is linked by an ID.

The two tables are: property and unit.

I need to gather all the units within a property but ONLY if the property has a status of '1' and a hidden flag of '0'. In normal mySQL, I wrote:

SELECT u.* FROM unit u INNER JOIN property p ON p.id = u.property WHERE p.status = 1 AND p.hidden = 0

which produces the correct results, although when I try the same using querybuilder:

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('u')
    ->from('AppBundle:Unit', 'u')
    ->join('u', 'AppBundle:Property', 'p', 'u.property = p.id')
    ->where('p.status = :status')
    ->andWhere('p.hidden = :hidden')
    ->setParameter('status', 1)
    ->setParameter('hidden', 0);

return $qb->getQuery()->getResult();

Using information I gleaned from the Doctrine Query Builder documentation. However, when I load the page I get the following error:

[Semantical Error] line 0, col 42 near 'u AppBundle:Property': Error: Class 'u' is not defined.

The query being executed:

SELECT u FROM AppBundle:Unit u INNER JOIN u AppBundle:Property P u.property = p.id WHERE p.status = :status AND p.hidden = :hidden

Can anyone help figure out what I'm doing wrong in my query?

Upvotes: 2

Views: 2030

Answers (2)

Evgeny Melnikov
Evgeny Melnikov

Reputation: 1092

You should exchange your first and second arguments places, because join() method is:

/**
 * Creates and adds a join over an entity association to the query.
 *
 * The entities in the joined association will be fetched as part of the query
 * result if the alias used for the joined association is placed in the select
 * expressions.
 *
 * <code>
 *     $qb = $em->createQueryBuilder()
 *         ->select('u')
 *         ->from('User', 'u')
 *         ->join('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');
 * </code>
 *
 * @param string      $join          The relationship to join.
 * @param string      $alias         The alias of the join.
 * @param string|null $conditionType The condition type constant. Either ON or WITH.
 * @param string|null $condition     The condition for the join.
 * @param string|null $indexBy       The index for the join.
 *
 * @return QueryBuilder This QueryBuilder instance.
 */
public function join($join, $alias, $conditionType = null, $condition = null, $indexBy = null)

This is a doc from doctrine QueryBuilder class.

Upvotes: 0

Alessandro Minoccheri
Alessandro Minoccheri

Reputation: 35963

try to change this:

->join('u', 'AppBundle:Property', 'p', 'u.property = p.id')

to this:

->join('AppBundle:Property', 'p', 'WITH', 'u.property = p.id')

Upvotes: 2

Related Questions