Symfony custom repository query giving error

Question

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

I'm pretty sure createQuery() is for creating DQL queries - where it looks you are creating a raw SQL, or perhaps a hybrid raw/DQL query. For instance a DQL query might look like this: SELECT u FROM FooBundle:User u Join u.offer o etc. — Darragh Enright
– Darragh Enright, Commented Jun 29, 2015 at 11:01
@Darragh well Symfony documentaion seems to be using it symfony.com/doc/current/book/… — Saadia
– Saadia, Commented Jun 29, 2015 at 11:12
Not exactly. The main problem with your query is Doctrine DQL does not support the * glob. @DonCallisto includes this in his answer below. More information on DQL queries here: doctrine-orm.readthedocs.org/en/latest/reference/… — Darragh Enright
– Darragh Enright, Commented Jun 29, 2015 at 11:22
I've deleted my answer as, at the moment, I can't correct it and I need to take a deep look to this question because I'm afraid that I've didn't understood it. BTW you cannot use * in DQL, you must use BundleName:EntityName — DonCallisto
– DonCallisto, Commented Jun 29, 2015 at 12:34

Shairyar · Accepted Answer · 2015-06-30 10:39:06Z

1

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();

answered Jun 30, 2015 at 10:39

Shairyar

3,3569 gold badges51 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VaN · Accepted Answer · 2015-06-29 15:11:05Z

0

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.

edited Jun 29, 2015 at 15:11

answered Jun 29, 2015 at 15:04

VaN

2,2104 gold badges20 silver badges43 bronze badges

2 Comments

Saadia Over a year ago

This is pretty close to what I need and did work by the way. However the scenario is that even if the user data does not exist in AdminBundle:Offer I still need the user to be listed. So on the front end the data will be displayed where the user has provided it, where the user did not provide the user null will be displayed. ofcourse i will handle the front end, its just the query that i am struggling with to pull the records. So how can i change your query to achieve the above?

VaN Over a year ago

What are you trying to fetch ? A list of users with their (optional) related offer, or a list of offers with their (optional) related user ?

Collectives™ on Stack Overflow

Symfony custom repository query giving error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related