CODEWITHSUNDEEP
symfonydoctrine-orm
Saadia
Saadia

Reputation: 856

Symfony custom repository query giving error

I am really new at Symfony and I am trying to get used to query builder. At the moment I am trying to join three tables and the following MySQL query gives me the results I need when I run it in PhpMyAdmin

SELECT * FROM pe_users u
LEFT JOIN pe_apply a ON u.id = a.user
LEFT JOIN pe_offer o ON a.id = o.application

However when I move this inside a Symfony Repository method

namespace Ache\AdminBundle\Repository;

use Doctrine\ORM\EntityRepository;

class AdminRepository extends EntityRepository{
    public function findAllAppByStatus(){
        $query = $this->getEntityManager()->createQuery(
            'SELECT * FROM pe_users u
             LEFT JOIN pe_apply a ON u.id = a.user
             LEFT JOIN pe_offer o ON a.id = o.application
             ');
        try {
            return $query->getSingleResult();
        } catch (\Doctrine\ORM\NoResultException $e) {
            return null;
        }
    }
}

I get the error

[Syntax Error] line 0, col 7: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got '*'

What does this error mean? what am I doing wrong?

UPDATE

The three entities I have are as following

UserBundle:User
CoreBundle:Apply
AdminBundle:Offer

User.id links with Apply.user and Offer.application links with Apply.id

Upvotes: 0

Views: 1078

Answers (2)

Shairyar
Shairyar

Reputation: 3356

You can still use raw sql with Symfony if you are comfortable with that

$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT * FROM pe_users u LEFT JOIN pe_apply a ON u.id = a.user LEFT JOIN pe_offer o ON a.id = o.application WHERE u.id = a.user";
$stmt = $conn->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();

Upvotes: 1

VaN
VaN

Reputation: 2210

I would do :

public function findAllAppByStatus(){
    $qb = $this->createQueryBuilder('u')
    ->leftJoin('CoreBundle:Apply', 'a', 'WITH', 'a.user = u')
    ->leftJoin('AdminBundle:Offer', 'o', 'WITH', 'o.application = a')
    ->setMaxResults(1); // if you return only 1 result, you want to be sure only one (or none) result is fetched

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

if you want to return possibly many results, as the 'All' in the method name suggests, get rid of the ->setMaxResults() and use $qb->getQuery()->getResult();

see how the queryBuilder works with objects, not tables. Joins are built on entities and properties, not tables and field names.

Upvotes: 0

Related Questions