Search code examples
phpsymfonydoctrinedql

Get key value list from Doctrine

I wrote this function inside my Repository class to receive a simple key value from Doctrine. Isn't there a build in Doctrine function to do this? (I couldn't find it). Or maybe the code can be improved.

Here's my function:

public function getListBy($criteria=null, $key, $value) {
    $dql = "SELECT i.".$key." as k,
                   i.".$value." as v
              FROM MbFooBundle:Input i";

    if (isset($criteria) && is_array($criteria)) {
        foreach($criteria as $cKey => $cValue) {
            if (!isset($where))
                $where = " WHERE ";
            else
                $where .= " AND ";

            $where .= "i.".$cKey." = ".(is_numeric($cValue) ? $cValue : "'".$cValue."'");
        }

        $dql .= $where;
    }

    $query = $this->getEntityManager()
        ->createQuery($dql);

    $result = $query->getArrayResult();
    $list = array();

    if (count($result)) {
        foreach($result as $data) {
            $list[$data['k']] = $data['v'];
        }
    }

     return $list;
}
Solution

  • I wouldn't do this. Yet this code is vulnerable to SQL Injection but it also breaks some standards.

    Here's my way of thinking.
    I would create a method which will manipulate the results of the standard doctrine's findBy

    /**
 * Data Manipulator
 */
class DataManipulator
{
    /**
     * Associates any traversable input into its key and value
     *
     * @param  mixed  $input A Traversable input
     * @param  string $key   Key to associate
     * @param  string $value Value to associate
     * @return array  Associated array
     *
     * @throws InvalidArgumentException When Input is not traversable
     */
    public function associate($input, $key, $value)
    {
        if (!is_array($input) && !($input instanceof Traversable)) {
            throw new InvalidArgumentException("Expected traversable");
        }

        $out = array();

        foreach ($input as $row) {
            $out[$this->getInput($row, $key)] = $this->getInput($row, $value);
        }

        return $out;
    }

    /**
     * Fetches the input of a given property
     *
     * @param  mixed  $row  An array or an object
     * @param  string $find Property to find
     * @return mixed  Property's value
     *
     * @throws UnexpectedValueException When no matching with $find where found
     */
    protected function getInput($row, $find)
    {
        if (is_array($row) && array_key_exists($find, $row)) {
            return $row[$find];
        }

        if (is_object($row)) {
            if (isset($row->$find)) {
                return $row->$find;
            }

            $method = sprintf("get%s", $find);

            if (method_exists($row, $method)) {
                return $row->$method();
            }
        }

        throw new UnexpectedValueException("Could not find any method to resolve");
    }
}

    Then you can use it

    $em      = $this->getDoctrine()->getManager();
$results = $em->getRepository('AcmeFooBundle:Input')
               ->findBy(array('category' => 'foo'));

$manipulator = new DataManipulator;
$filtered    = $manipulator->associate($results, 'key', 'value');

    You can see it working

    If you need to select only partial objects, you should create a method in your repository which will fetch your partial input.
    This function must only fetch the object, not associate its content.

    public function findPartialBy(array $values, array $criterias = array())
{
    $qb = $this->createQueryBuilder('i');
    $qb->select($values);

    foreach ($criterias as $key => $value) {
        $qb->andWhere(sprintf("i.%s", $key), sprintf(":%s", $key))
        $qb->setParameter(sprintf(":%s", $key), $value);
    }

    return $qb->getQuery()->getResult();
}

    Then you can use it

    $fetch   = array('key', 'value');
$em      = $this->getDoctrine()->getManager();
$results = $em->getRepository('AcmeFooBundle:Input')
               ->findPartialBy($fetch, array('category' => 'foo'));

$manipulator = new DataManipulator;
$filtered    = $manipulator->associate($results, 'key', 'value');