DQL Query Using Variable in Select Statement Returning Exception

Question

I am getting this Exception:

[Semantical Error] line 0, col 124 near 'column = :column': Error: 'column' is not defined.

When trying to run this code:

/**
 * @param User $user
 * @param String $column
 * @return array
 */
public function getStatsByDQL(User $user, String $column)
{
    $midNight = date_create('00:00:00')->format('Y-m-d H:i:s');

    $em2 = $this->getDoctrine()->getManager()->getRepository('AppBundle:ExerciseStats')
        ->createQueryBuilder('g')
        ->setParameters(array(
            'user' => $user, 'column' => $column, 'date' => $midNight,
        ))
        ->where('user = :user', 'g.timestamp < :date',  'column = 
:column')
        ->select('g.column')
        ->setMaxResults(1)
        ->join('g.user', 'user')
        ->orderBy('g.column','DESC')

        ->getQuery()->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
        return $em2;

Here's how it is executing:

$yy = $this->getStatsByDQL($usr, 'bench_press_1_weight');
    var_dump($yy);

This DQL Query runs fine when 'g.column' is replaced by 'bench_press_1_weight', but goes awry when g.column is placed there instead. I think 'bench_press_1_weight' should be passed since I set in parameters 'column' => $column. To me, this should work. I have not found the Doctrine docs to be of any use and have tried a myriad of combinations.

This function is in my default controller.

Anyone have any clues? Thanks in advance!

Answer 1

I have solved the issue, it was so simple that I almost shot myself. So what needed to occur was removing the variable introduced in the signature of the function from the params and WHERE statement, and it looks like this:

public function getStatsByDQL(User $user,  String $col)
{

    $midNight = date_create('00:00:00')->format('Y-m-d H:i:s');
    $parameters = (array('user' => $user, 'date' => $midNight, 

    ));
    $em2 = $this->getDoctrine()->getManager()->getRepository('AppBundle:ExerciseStats')
        ->createQueryBuilder('g')
        ->setParameters($parameters)
        ->where('g.user = :user', 'g.timestamp < :date')
        ->select('g.'. $col)
        ->setMaxResults(1)
        ->join('g.user', 'user')
        ->orderBy( 'g.'. $col,'DESC')
        ->getQuery()->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
    return $em2;
}

This returns the correct data I was after, i.e. the column requested and it's contents. Thanks for everyone's input. Much appreciated. :)