Implementing SELECT query inside another SELECT query in DQL (Doctrine)

Question

I have a SQL query for MySQL which is giving me the correct result. However, I'm facing a problem in converting it to a Doctrine query with DQL.

SELECT Sum(s) AS s 
FROM   (SELECT DISTINCT oder.id   AS m, 
                        o_t.restaurants_id, 
                        o_t.value AS s 
        FROM   orders AS oder 
               INNER JOIN order_total AS o_t 
                       ON oder.id = o_t.orders_id 
               INNER JOIN order_status AS o_s 
                       ON oder.id = o_s.orders_id 
        WHERE  o_s.status = 1 
               AND o_t.code = 'total') a 

I am trying the following:

 $query = Doctrine_Query::create()
        ->select('sum(s) as s')
        ->addSelect('DISTINCT order.id,o_t.value')
        ->from('orders as order')
        ->leftJoin('order.order_total as o_t')
        ->leftJoin('order.order_status as o_s_i')
        ->where('o_s_i.status = '.order_status::ORDER_COMPLETE)
        ->andWhere('o_t.code = "total"')->getSqlQuery();

That returns:

SELECT DISTINCT o.id     AS o__id, 
                o2.id    AS o2__id, 
                o2.value AS o2__value, 
                Sum(s)   AS o__0 
FROM   orders o 
       LEFT JOIN order_total o2 
              ON o.id = o2.orders_id 
       LEFT JOIN order_status o3 
              ON o.id = o3.orders_id 
WHERE  ( o3.status = 1 
         AND o2.code = "total" ) 

Any idea?

Answer 1

Yes, Jean Valjean is right.

My friend, if your raw SQL is working well then why don't you use raw SQL Query.

Let me help you in writing this.

First you need PDO (PHP data object), which is will be database handler for your raw query.

$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();

then prepare the statement and execute it:

$query = "SELECT Sum(s) AS s 
FROM   (SELECT DISTINCT oder.id   AS m, 
                        o_t.restaurants_id, 
                        o_t.value AS s 
        FROM   orders AS oder 
               INNER JOIN order_total AS o_t 
                       ON oder.id = o_t.orders_id 
               INNER JOIN order_status AS o_s 
                       ON oder.id = o_s.orders_id 
        WHERE  o_s.status = 1 
               AND o_t.code = 'total') a";

$stmt = $pdo->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll();

You can also pass parameters like follows:

$query = "SELECT * FROM table WHERE param1 = :param1 AND param2 = :param2";
$stmt = $pdo->prepare($query);

$params = array(
  "param1"  => "value1",
  "param2"  => "value2"
);
$stmt->execute($params);

$results = $stmt->fetchAll();