CODEWITHSUNDEEP
symfonydoctrine-orm
Vertex
Vertex

Reputation: 43

Correct syntax for Doctrine2's query builder substring helper method

Ok, this problem is probably due to my foggy understanding of the query builder helper methods but for the life of me I cannot find the correct way to use the substring method.

I am attempting to return all results that begin with a specified alphanumeric value. The below code does not throw any errors but it also does not return any results. I have scoured google but apparently there is almost nothing showing how to use substring. I would like to get this working in query builder but I may have to go DQL or raw sql.

    $qb->select('p', 't');
    $qb->from('ContentParent', 'p');
    $qb->join('p.titleCurrent', 't');
    $qb->where(
            $qb->expr()->eq($qb->expr()->substring('t.sortTitle', 0, 1), ':letter')
        );

Thanks!

Upvotes: 4

Views: 4930

Answers (1)

webbiedave
webbiedave

Reputation: 48887

SUBSTRING needs to be one-based.

The MySQL manual states:

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

With Oracle:

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

From the Transact-SQL manual:

SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person.Person
WHERE LastName like 'Barl%'
ORDER BY LastName

You can see from these examples that they, too, are using one-based (Oracle, however, will assume 1 when given 0).

Simply change your substring parameters to 't.sortTitle', 1, 1 and it will work correctly.

Upvotes: 7

Related Questions