CODEWITHSUNDEEP
symfonydoctrine
K. Weber
K. Weber

Reputation: 2773

Doctrine query with LPAD (custom function)

I need to perform the next query in Doctrine:

SELECT MAX(LPAD(SUBSTRING(code, 1, instr(code, '/')-1), 6, '0')) as lastCode
FROM provider
WHERE entry_date >= '%s'", date("Y") . "-01-01"

(in short: I have providers with codes xx/yy where yy is 2 digit year and xx is a sequential number, as this code is varchar I perform a query to get last numeric code in this year)

The problem is the use of LPAD. If I use query builder I get an error for using custom functions, if I use native query I get empty results.

Here is my code:

With native query:

$sqlMaxCode = sprintf("SELECT MAX(LPAD(SUBSTRING(code, 1, instr(code, '/')-1), 6, '0')) as lastCode
    FROM provider
    WHERE entry_date >= '%s'", date("Y") . "-01-01");

$nQuery = $em->createNativeQuery($sqlMaxCode, new ResultSetMapping());
$rows = $nQuery->getSingleResult();
$this->get('logger')->info(print_r($rows, true));
//$rows is always empty

With query builder:

$qb = $em->getRepository('AppBundle:Provider')->createQueryBuilder('I');
$qb->select('MAX(LPAD(SUBSTRING(code, 1, instr(code, "/")-1), 6, "0")) as lastCode')
    ->where("I.entryDate >= ?1")
    ->setParameter('1', date("Y") . "-01-01");

$result = $qb->getQuery()->getSingleResult();

Query builder throws this error:

Error: Expected known function, got 'LPAD'

Do I have other choices? Why is native query returning empty result? (same query on mysql console shows last number)

Note: I tried CAST(MAX(SUBSTRING(...) as unsigned)... but have same problem with CAST function.

Upvotes: 1

Views: 1186

Answers (1)

K. Weber
K. Weber

Reputation: 2773

I had an error in native query, if I configure correctly the ResultSetMapping native query can deal with custom functions correctly:

$rsm = new ResultSetMapping();
$rsm->addScalarResult('lastCode', 'lastCode');

$sqlMaxCode = sprintf("SELECT MAX(LPAD(SUBSTRING(code, 1, instr(code, '/')-1), 6, '0')) as lastCode
    FROM provider
    WHERE entry_date >= '%s'", date("Y") . "-01-01");

$nQuery = $em->createNativeQuery($sqlMaxCode, $rsm);
$rows = $nQuery->getSingleResult();
$this->get('logger')->info(print_r($rows, true));

Upvotes: 1

Related Questions