Doctrine Query Builder / DQL - How to write query with WHERE column = BINARY 'text'

Question

How would I write a query such as:

SELECT id FROM Records WHERE name = BINARY 'My Record';

Using Doctrine Query Builder? 'My Record' could be any arbitrary name to query for.

The following does not work... Honestly, I didn't expect this to work but I can't think of anything else to try.

// Assume $repo is the Records repository
// Assume $name is the name to query, such as "My Record"
$repo->createQueryBuilder('r')
  ->select('r.id')
  ->where('r.name = BINARY :name')
  ->setParameter('name', $name)
  ->getQuery()
  ->getResult();

As I understand it, if the field is properly mapped to that type then Doctrine should handle what ever data conversion is needed when you set the parameter. Does it work if you simply omit `BINARY` from your `where` clause? — Arleigh Hix, Oct 28 '21 at 20:57
It would be good to show the relevant parts of the entity (the field mapping, getter & setter) — Arleigh Hix, Oct 28 '21 at 21:00
@ArleighHix I think you're misunderstanding my question. This is not about data conversion or anything like that. My question is specifically how to execute this particular SQL query using the Doctrine query builder. — cheryllium, Oct 28 '21 at 21:36
The reason I want to do this doesn't matter. The question is more like, if it's possible to do this particular SQL clause `where columnName = BINARY 'some text here'` with DQL and, if so, what it would look like. — cheryllium, Oct 28 '21 at 21:38
Sorry, trying to explain this. I get where you're coming from, my question is more about whether DQL supports this form of query, not asking if there are other ways to write the raw SQL query / make changes on SQL end to accomplish the same goal. Basically, if I know this is the raw SQL that I want DQL to run, how would I make it do that (if possible) - I hope that makes sense. — cheryllium, Oct 28 '21 at 21:40
Does this answer your question? [is there a way to use mysql binary operator in doctrine2?](https://stackoverflow.com/questions/37263094/is-there-a-way-to-use-mysql-binary-operator-in-doctrine2) — Arleigh Hix, Oct 28 '21 at 21:50
I don't think you understood me, I'm talking about changing this line as so ` ->where('r.name = :name')`, and I never said anything about raw SQL. — Arleigh Hix, Oct 28 '21 at 21:54
Thanks so much @ArleighHix for your help - truly appreciate it - but that question is from 2016 and the links in the only answer are dead. And I understood you, but I specifically want to use `BINARY` the way I did in the raw query. — cheryllium, Oct 28 '21 at 21:56
I mean, changing it to remove BINARY will just perform `WHERE name = 'my text'`, not `WHERE name = BINARY 'my text'` you know? — cheryllium, Oct 28 '21 at 21:57
No, Doctrine should build the appropriate query for the configured database, based on the field mapping which you are not showing. — Arleigh Hix, Oct 28 '21 at 22:01
If that is a binary data type in the database then it will convert the php string parameter into a binary value in the query. — Arleigh Hix, Oct 28 '21 at 22:06
Okay, it's not. I guess there is no way to simply run the query I want to with DQL. Thanks. — cheryllium, Oct 28 '21 at 23:00
@cheryllium you have to create a [custom user function](https://www.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html) like the [BINARY type extension](https://github.com/beberlei/DoctrineExtensions/blob/v1.3.0/src/Query/Mysql/Binary.php) for it to be supported in your DQL, which should support `r.name = BINARY(:name)`. — Will B., Oct 29 '21 at 04:16
@WillB. Omg thank you! Could you please post that as an answer, I'd like to accept it. — cheryllium, Oct 29 '21 at 15:58

Will B. · Accepted Answer · 2021-10-29T21:50:59.687

In order to support any DQL function not provided by the Doctrine ORM a custom user function would need to be used.

Install the DoctrineExtensions library as suggested by the Doctrine documentation.

composer require beberlei/doctrineextensions

Enable the BINARY type extension in the Symfony doctrine configuration.

# config/packages/doctrine.yaml

doctrine:
    orm:
       # ...
       dql:
           string_functions:
               binary: DoctrineExtensions\Query\Mysql\Binary

Now your DQL statements should support r.name = BINARY(:name)

Full list of provided DQL type extensions for MySQL.

datetime_functions:
    addtime: DoctrineExtensions\Query\Mysql\AddTime
    convert_tz: DoctrineExtensions\Query\Mysql\ConvertTz
    date: DoctrineExtensions\Query\Mysql\Date
    date_format: DoctrineExtensions\Query\Mysql\DateFormat
    dateadd: DoctrineExtensions\Query\Mysql\DateAdd
    datesub: DoctrineExtensions\Query\Mysql\DateSub
    datediff: DoctrineExtensions\Query\Mysql\DateDiff
    day: DoctrineExtensions\Query\Mysql\Day
    dayname: DoctrineExtensions\Query\Mysql\DayName
    dayofweek: DoctrineExtensions\Query\Mysql\DayOfWeek
    dayofyear: DoctrineExtensions\Query\Mysql\DayOfYear
    div: DoctrineExtensions\Query\Mysql\Div
    from_unixtime: DoctrineExtensions\Query\Mysql\FromUnixtime
    hour: DoctrineExtensions\Query\Mysql\Hour
    last_day: DoctrineExtensions\Query\Mysql\LastDay
    makedate: DoctrineExtensions\Query\Mysql\MakeDate
    minute: DoctrineExtensions\Query\Mysql\Minute
    now: DoctrineExtensions\Query\Mysql\Now
    month: DoctrineExtensions\Query\Mysql\Month
    monthname: DoctrineExtensions\Query\Mysql\MonthName
    period_diff: DoctrineExtensions\Query\Mysql\PeriodDiff
    second: DoctrineExtensions\Query\Mysql\Second
    sectotime: DoctrineExtensions\Query\Mysql\SecToTime
    strtodate: DoctrineExtensions\Query\Mysql\StrToDate
    time: DoctrineExtensions\Query\Mysql\Time
    timediff: DoctrineExtensions\Query\Mysql\TimeDiff
    timestampadd: DoctrineExtensions\Query\Mysql\TimestampAdd
    timestampdiff: DoctrineExtensions\Query\Mysql\TimestampDiff
    timetosec: DoctrineExtensions\Query\Mysql\TimeToSec
    truncate: DoctrineExtensions\Query\Mysql\Truncate
    week: DoctrineExtensions\Query\Mysql\Week
    weekday: DoctrineExtensions\Query\Mysql\WeekDay
    weekofyear: DoctrineExtensions\Query\Mysql\WeekOfYear
    year: DoctrineExtensions\Query\Mysql\Year
    yearmonth: DoctrineExtensions\Query\Mysql\YearMonth
    yearweek: DoctrineExtensions\Query\Mysql\YearWeek
    unix_timestamp: DoctrineExtensions\Query\Mysql\UnixTimestamp
    utc_timestamp: DoctrineExtensions\Query\Mysql\UtcTimestamp
    extract: DoctrineExtensions\Query\Mysql\Extract

numeric_functions:
    acos: DoctrineExtensions\Query\Mysql\Acos
    asin: DoctrineExtensions\Query\Mysql\Asin
    atan2: DoctrineExtensions\Query\Mysql\Atan2
    atan: DoctrineExtensions\Query\Mysql\Atan
    bit_count: DoctrineExtensions\Query\Mysql\BitCount
    bit_xor: DoctrineExtensions\Query\Mysql\BitXor
    ceil: DoctrineExtensions\Query\Mysql\Ceil
    cos: DoctrineExtensions\Query\Mysql\Cos
    cot: DoctrineExtensions\Query\Mysql\Cot
    degrees: DoctrineExtensions\Query\Mysql\Degrees
    exp: DoctrineExtensions\Query\Mysql\Exp
    floor: DoctrineExtensions\Query\Mysql\Floor
    json_contains: DoctrineExtensions\Query\Mysql\JsonContains
    json_depth: DoctrineExtensions\Query\Mysql\JsonDepth
    json_length: DoctrineExtensions\Query\Mysql\JsonLength
    log: DoctrineExtensions\Query\Mysql\Log
    log10: DoctrineExtensions\Query\Mysql\Log10
    log2: DoctrineExtensions\Query\Mysql\Log2
    pi: DoctrineExtensions\Query\Mysql\Pi
    power: DoctrineExtensions\Query\Mysql\Power
    quarter: DoctrineExtensions\Query\Mysql\Quarter
    radians: DoctrineExtensions\Query\Mysql\Radians
    rand: DoctrineExtensions\Query\Mysql\Rand
    round: DoctrineExtensions\Query\Mysql\Round
    stddev: DoctrineExtensions\Query\Mysql\StdDev
    sin: DoctrineExtensions\Query\Mysql\Sin
    std: DoctrineExtensions\Query\Mysql\Std
    tan: DoctrineExtensions\Query\Mysql\Tan
    variance: DoctrineExtensions\Query\Mysql\Variance

string_functions:
    aes_decrypt: DoctrineExtensions\Query\Mysql\AesDecrypt
    aes_encrypt: DoctrineExtensions\Query\Mysql\AesEncrypt
    any_value: DoctrineExtensions\Query\Mysql\AnyValue
    ascii: DoctrineExtensions\Query\Mysql\Ascii
    binary: DoctrineExtensions\Query\Mysql\Binary
    cast: DoctrineExtensions\Query\Mysql\Cast
    char_length: DoctrineExtensions\Query\Mysql\CharLength
    collate: DoctrineExtensions\Query\Mysql\Collate
    concat_ws: DoctrineExtensions\Query\Mysql\ConcatWs
    countif: DoctrineExtensions\Query\Mysql\CountIf
    crc32: DoctrineExtensions\Query\Mysql\Crc32
    degrees: DoctrineExtensions\Query\Mysql\Degrees
    field: DoctrineExtensions\Query\Mysql\Field
    find_in_set: DoctrineExtensions\Query\Mysql\FindInSet
    format: DoctrineExtensions\Query\Mysql\Format
    from_base64: DoctrineExtensions\Query\Mysql\FromBase64
    greatest: DoctrineExtensions\Query\Mysql\Greatest
    group_concat: DoctrineExtensions\Query\Mysql\GroupConcat
    hex: DoctrineExtensions\Query\Mysql\Hex
    ifelse: DoctrineExtensions\Query\Mysql\IfElse
    ifnull: DoctrineExtensions\Query\Mysql\IfNull
    inet_aton: DoctrineExtensions\Query\Mysql\InetAton
    inet_ntoa: DoctrineExtensions\Query\Mysql\InetNtoa
    inet6_aton: DoctrineExtensions\Query\Mysql\Inet6Aton
    inet6_ntoa: DoctrineExtensions\Query\Mysql\Inet6Ntoa
    instr: DoctrineExtensions\Query\Mysql\Instr
    is_ipv4: DoctrineExtensions\Query\Mysql\IsIpv4
    is_ipv4_compat: DoctrineExtensions\Query\Mysql\IsIpv4Compat
    is_ipv4_mapped: DoctrineExtensions\Query\Mysql\IsIpv4Mapped
    is_ipv6: DoctrineExtensions\Query\Mysql\IsIpv6
    lag: DoctrineExtensions\Query\Mysql\Lag
    lead: DoctrineExtensions\Query\Mysql\Lead
    least: DoctrineExtensions\Query\Mysql\Least
    lpad: DoctrineExtensions\Query\Mysql\Lpad
    match: DoctrineExtensions\Query\Mysql\MatchAgainst
    md5: DoctrineExtensions\Query\Mysql\Md5
    nullif: DoctrineExtensions\Query\Mysql\NullIf
    over: DoctrineExtensions\Query\Mysql\Over
    radians: DoctrineExtensions\Query\Mysql\Radians
    regexp: DoctrineExtensions\Query\Mysql\Regexp
    replace: DoctrineExtensions\Query\Mysql\Replace
    rpad: DoctrineExtensions\Query\Mysql\Rpad
    sha1: DoctrineExtensions\Query\Mysql\Sha1
    sha2: DoctrineExtensions\Query\Mysql\Sha2
    soundex: DoctrineExtensions\Query\Mysql\Soundex
    str_to_date: DoctrineExtensions\Query\Mysql\StrToDate
    substring_index: DoctrineExtensions\Query\Mysql\SubstringIndex
    unhex: DoctrineExtensions\Query\Mysql\Unhex
    uuid_short: DoctrineExtensions\Query\Mysql\UuidShort

Doctrine Query Builder / DQL - How to write query with WHERE column = BINARY 'text'

1 Answers1