CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Kevin
Kevin

Reputation: 5082

Symfony Doctrine take the most recent date in where close

I would like to make a query which call the most recent date in the same table in a where close.

$query = $this->createQueryBuilder('a')
    ->select('a.amount')
    ->where('a.product = :productId')
    ->andWhere('a.id = :id'),

$recentDate = null;

if($date === null){
    $recentDate = $this->createQueryBuilder('a')
        ->select($query->expr()->max('a.date'))->getQuery()->getResult();
    $query->andWhere('a.date = :recentDate');
}else{
    $query->andWhere('a.date = :date');
}

$query->setParameters(array(
        'productId' => $productId,
        'id' => $id,
        'date' => $date,
        'recentDate' => $recentDate,
    ));

return $query;

but I have this issue:

Invalid parameter number: number of bound variables does not match number of tokens

Upvotes: 1

Views: 1850

Answers (3)

Delphine
Delphine

Reputation: 888

Just name your "date" parameter and "recentDate" parameter with the same name, they couldn't be in your request together and pass only this unique parameter in your setParameters()

$query = $this->createQueryBuilder('a')
    ->select('a.amount')
    ->where('a.product = :productId')
    ->andWhere('a.id = :id')
    ->andWhere('a.date = :date');

if($date === null){
    $date = $this->createQueryBuilder('a')
        ->select($query->expr()->max('a.date'))->getQuery()->getResult();
}

$query->setParameters(array(
        'productId' => $productId,
        'id' => $id,
        'date' => $date
    ));

return $query;

Upvotes: 2

Muriano
Muriano

Reputation: 383

You're providing 4 parameters to a query that only has 3.

$query = $this->createQueryBuilder('a')
    ->select('a.amount')
    ->where('a.product = :productId')
    ->andWhere('a.id = :id'),

$recentDate = null;

if($date === null){
    $recentDate = $this->createQueryBuilder('a')
        ->select($query->expr()->max('a.date'))->getQuery()->getResult();
    $query->andWhere('a.date = :recentDate');
}else{
    $query->andWhere('a.date = :date');
}

$query->setParameters(array(
        'productId' => $productId,
        'id' => $id,
));

if($date === null){
    $query->setParameter('recentDate', $recentDate);
}else{
    $query->setParameter('date', $date);
}

Upvotes: 1

Joe
Joe

Reputation: 2436

Try this: 

$query->setParameters(array(
    'productId' => $productId,
    'id' => $id
));
if($date === null) {
  $query->setParameter(':recentDate', $recentDate);
} else {
  $query->setParameter(':date', $date);
}

The recentDate parameter isn't a part of the query if $date != null and vice versa so isn't the date parameter.

Maybe there's a more elegant way to build this query?

kind regards Joe

Upvotes: 1

Related Questions