CODEWITHSUNDEEP
mysqlsymfonydoctrine-orminner-join
Marlyyy
Marlyyy

Reputation: 722

Symfony2 - subquery within a join error

I would like to use a subquery inside a join, however Symfony2 throws the following error: enter image description here

Here is my failed attempt:

$query = $em->createQuery(
            'SELECT
              sc.id AS id,
              u.id AS userId,
              u.username AS username,
              sc_count.upvotes
            FROM
              myBundle:SuggestedCar sc
              INNER JOIN myBundle:User u WITH sc.user_id = u.id
              INNER JOIN (  SELECT sc1.user_id, COUNT(sc1.id) AS upvotes
                            FROM myBundle:SuggestedCar sc1
                            GROUP BY sc1.user_id
                ) sc_count WITH u.id = sc_count.user_id'
        );

Basically I'm just joining 3 tables and the third one has a count. The query worked when executing it inside the database.

How would it be possible to use a SELECT statement inside a join? Is it a good idea to use raw SQL at this point?

Upvotes: 1

Views: 484

Answers (1)

Sehael
Sehael

Reputation: 3736

The $em->createQuery() function is expecting DQL as the parameter, not SQL. If you want to execute a raw SQL statement, the syntax is different. You can do it like this:

$sql = "SELECT * FROM my_table";
$em = $this->getDoctrine()->getManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

for more on DQL or querying for objects, see Querying for Object. The biggest difference is DQL will return an object (based on your entity classes in Symfony). The method I posted above will just give you a PDO result. So if you execute raw SQL, don't expect to be able to use the result as an object.

If you want to use raw SQL and still have the result mapped to an object, you can look at the doctrine docs about Result set mapping. In my opinion, this is more work than necessary.

Upvotes: 2

Related Questions