phpdoctrinedqldoctrine-1.2

Select One column Doctrine DQL


I need a simple column for a table.

By example a table "project", with column id, name and year.

If I do:

$q = Doctrine_Query::create()
        ->select('a.pro_id')
        ->from('fndr_proyecto a')
        ->where('a.pro_id =?',1);
    $pro = $q->execute();
    json_encode($pro->toArray());

The answer is all column like

{"id":1,"name":"Project name","year":2013}

but I need only one column. I expect:

{"id":1}

It is with DQL because with native SQL work fine.

The ORM is build automaticaly with a Visual Paradigm.


Solution

  • This is because Doctrine hydrate the response with all the object information, so all columns.

    You need to use a different hydration method, there are many one, but let's focus on 5 of them:

    You need the HYDRATE_ARRAY_SHALLOW hydration method. Here's why.

    1. HYDRATE_RECORD

      $q = Doctrine_Query::create()
          ->select('a.pro_id')
          ->from('fndr_proyecto a')
          ->where('a.pro_id = ?',1);
      $pro = $q->execute(array(), Doctrine_Core::HYDRATE_RECORD);
      var_dump(json_encode($pro->toArray()));
      

      This will hydrate the result using object, and also hydrate relations (if you use a leftJoin inside your query). Since it returns object, we need to call toArray() to be able to send a propre json:

      [{"id":1,"name":"Project name","year":2013}]"
      
    2. HYDRATE_ARRAY

      $q = Doctrine_Query::create()
          ->select('a.pro_id')
          ->from('fndr_proyecto a')
          ->where('a.pro_id = ?',1);
      $pro = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
      var_dump(json_encode($pro));
      

      This will hydrate result as an array an automatically add the primary key:

      [{"id":"1","pro_id":"1"}]"
      
    3. HYDRATE_NONE

      $q = Doctrine_Query::create()
          ->select('a.pro_id')
          ->from('fndr_proyecto a')
          ->where('a.pro_id = ?',1);
      $pro = $q->execute(array(), Doctrine_Core::HYDRATE_NONE);
      var_dump(json_encode($pro));
      

      This won't hydrate result, and return just values:

      [["1"]]"
      
    4. HYDRATE_SCALAR

      $q = Doctrine_Query::create()
          ->select('a.pro_id')
          ->from('fndr_proyecto a')
          ->where('a.pro_id = ?',1);
      $pro = $q->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
      var_dump(json_encode($pro));
      

      This will hydrate result from the select but with key index as the column name with the table alias:

      [{"a_pro_id":"1"}]"
      
    5. HYDRATE_ARRAY_SHALLOW

      $q = Doctrine_Query::create()
          ->select('a.pro_id')
          ->from('fndr_proyecto a')
          ->where('a.pro_id = ?',1);
      $pro = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY_SHALLOW);
      var_dump(json_encode($pro));
      

      This will hydrate result from the select but with key index as the column name without the table alias:

      "[{"pro_id":"1"}]"