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!):
Is this a bug, or am I doing something wrong?
via Leonard