Erik Baan
Erik Baan

Reputation: 436

Symfony 2 query builder join without relation (cross join)

I am trying to use the query builder to join 2 tables which have no relation.

Desired end result:

SELECT x, y
FROM x
JOIN y

Query builder code:

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('x');
$qb->from('Test1', 'x');
$qb->join('Test2', 'y');
$qb->orderBy('x.name', 'ASC');

Produces the following DQL:

SELECT x FROM Test1 x INNER JOIN Test2 y ORDER BY x.name ASC

Which results in a syntax error:

[Syntax Error] line 0, col 137: Error: Expected Literal, got 'BY'

The entities Test1 and Test2 don't have a relation (not in the code, nor in the database).

Is there any way to accomplish this? I would like to use the query builder, because I have a lot of other functionality for the query that depends on the query builder (for filtering and sorting etc.).

I know this is possible with plain SQL, or DQL queries (not produced by the query builder).

Upvotes: 1

Views: 3169

Answers (1)

Alexandru Olaru
Alexandru Olaru

Reputation: 7092

You can try the following possibility:

public function getYourData($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb
        ->select('x', 'y')
        ->from('Test1', 'x')
        ->leftJoin(
            'Test2',
            'y',
            \Doctrine\ORM\Query\Expr\Join::WITH,
            'x.id = y.reference_id'
        )
        ->orderBy('x.name', 'ASC');

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

Upvotes: 2

Related Questions