Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

_camilo_'s avatar

Query runs raw but not with Query Builder `->get()`

Hi there! I'm struggling for a while already with the following problem:

I generate this instruction with the Query Builder (code below is the result of a DB::getQueryLog()), but it doesn't retrieve the data, with no errors, only an empty array.

array:1 [
  0 => array:3 [
    "query" => "select `id`, `content` from `mediator_responses` where (`private` = ? or `private` = ?) and exists (select `mediator_response_situations`.`id` from `mediator_response_situations` where `mediator_response_situations`.`mediator_response_id` = ? and exists (select `id` from `forum_threads` where `mediator_response_situations`.`model_id` = ? and (`title` LIKE ? or `content` LIKE ?)))"
    "bindings" => array:6 [
      0 => "1"
      1 => "0"
      2 => "mediator_responses.id"
      3 => "forum_threads.id"
      4 => "%a%"
      5 => "%a%"
    ]
    "time" => 0.64
  ]
]

The mysterious thing is, when I copy the generated query, manually replace the bindings and run it with DB::raw(), everything works perfectly...

Any guesses why?

0 likes
7 replies
_camilo_'s avatar

Here's the full code

public function search()
{
    DB::enableQueryLog();
    $params = request()->all();
    $query = MediatorResponse::select(['id', 'content']);

    $query->where(function($query) use ($params) {
        foreach ($params['private'] ?? [] as $val) {
            $query->orWhere('private', $val);
        }
    });
    
    if ($params['where'] === 'tags') {
        $query->where('tags', 'LIKE', "%{$params['q']}%");
    }
    elseif ($params['where'] === 'situation')
    {
        $response = app(MediatorResponse::class)->getTable();
        $pivot = app(Situation::class)->getTable();
        $situation = $this->morphModel->getTable();
        $searchable = $this->morphModel->searchable;

        // https://www.w3schools.com/sql/sql_exists.asp
        $query->whereExists(function($query) use ($pivot, $situation, $response, $searchable, $params) {
            $query->select("$pivot.id")
            ->from($pivot)
            ->where("$pivot.mediator_response_id", "$response.id")
            ->whereExists(function($query) use ($pivot, $situation, $response, $searchable, $params) {
                $query->select('id')
                    ->from($situation)
                    ->where("$pivot.model_id", "$situation.id");
                $query->where(function($query) use ($searchable, $params) {
                    foreach ($searchable as $column) {
                        $query->orWhere($column, 'LIKE', "%{$params['q']}%");
                    }
                });
            });
        });
    }
    $results = $query->get();
    dd(DB::getQueryLog());
    return response()->json(['success' => true, 'results' => $results]);
}
_camilo_'s avatar

Using this workaround to force the binding... This solves the problem but the reason why the Query Builder doesn't do it himself persists.

$sql_with_bindings = \Str::replaceArray('?', $query->getBindings(), $query->toSql());
$results = DB::select($sql_with_bindings);
Sinnbeck's avatar

Try using debugbar to see what query it creates and test that

_camilo_'s avatar

@Sinnbeck The query it creates is in the first code block I let (on top), which was got with DB::getQueryLog(). I think it would be the same in DebugBar's, right?

Sinnbeck's avatar

@camilo generally I would say yes, but in a case like this I would double check it. Also it might be worth seeing if mysql strict mode makes a difference. You can try turning it off in the connection

'strict' =>false, 
_camilo_'s avatar

@Sinnbeck Well, thanks for the attention. But since I'm running against time here I won't be able to dig deeper. If I bump into this in the future again I let you know ;)

Sinnbeck's avatar

@camilo totally fine. Might be worth add a todo reminder for yourself for later on :)

1 like

Please or to participate in this conversation.