Friday, March 17, 2017

Query Builder: where-like doesn't add single quotes?

I am trying to implement a rather complex SQL query using Laravel query builder, and I believe I might have run into a bug. I would like to confirm so is the case with you first.

This is the faulty code:

public function getWordTranslations($word) {
    if (strpos($word, '*') === false) {
        $word .= '%';
    } else {
        $word = str_replace('*', '%', $word);
    }

    return $this->createTranslationQuery()
        ->whereIn('t.NamespaceID', function ($query) use($word) {

            $tertiaryMatches = DB::table('keywords as k')
                ->join('translation as t', 'k.TranslationID', '=', 't.TranslationID')
                ->where('k.NormalizedKeyword', 'like', $word)
                ->where('t.Deleted', '=', 0)
                ->whereNotNull('k.TranslationID')
                ->select('t.NamespaceID');

            $query->select('NamespaceID')
                ->from('keywords')
                ->where('NormalizedKeyword', 'like', $word)
                ->where('Deleted', '=', 0)
                ->whereNotNull('NamespaceID')
                ->union($tertiaryMatches);

        })->get();
}

The createTranslationQuery uses DB to create a query builder object. I want to filter the original query with an IN subquery.

Unfortunately, the subquery generated by the whereIn builder does not wrap the value held by $word with single quotes.

Expected result when $word = "sample":

select ... where t.NamespaceID in(
   select NamespaceID 
   from ...
   where NormalizedKeyword like 'sample%'
   ...)

Actual result:

select ... where t.NamespaceID in(
   select NamespaceID 
   from ...
   where NormalizedKeyword like sample%
   ...)

= Major breakage.

This is the exception (apologies for the lack of formatting!):

Exception

Is this a bug, or am I doing something wrong?



via Leonard

Advertisement