Level 56
Are you sure the error is triggered by this query?
I created a sample project with your code, and it runs fine.
<?php // routes/console.php
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
Artisan::command('app:run', function () {
$clientId = 1;
$cipCodes = array_values([1, 2, 3]);
// Format the CIP codes for the FIELD function
$formattedCipCodes = implode(',', array_map(function ($code) {
return "'" . $code . "'";
}, $cipCodes));
// Retrieve the occupations from the database
$occupations = DB::table('client_cipcodes')
->distinct()
->select('id', 'cip_code', 'cip_name', 'web_url')
->where('client_id', '=', $clientId)
->whereIn('cip_code', $cipCodes)
->orderByRaw("FIELD(cip_code, $formattedCipCodes)")
->get()
->toArray();
\dd($occupations);
});
<?php // database/migrations/0002_01_01_000002_create_client_cipcodes_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
public function up(): void
{
Schema::create('client_cipcodes', function (Blueprint $table) {
$table->id();
$table->foreignId('client_id');
$table->unsignedInteger('cip_code');
$table->string('cip_name')->nullable();
$table->string('web_url')->nullable();
$table->timestamps();
});
DB::table('client_cipcodes')->insert([
[
'client_id' => 1,
'cip_code' => 1,
'cip_name' => 'A',
'web_url' => 'https://example.com/',
],
[
'client_id' => 1,
'cip_code' => 2,
'cip_name' => 'B',
'web_url' => 'https://example.com/',
],
[
'client_id' => 1,
'cip_code' => 3,
'cip_name' => 'C',
'web_url' => 'https://example.com/',
],
[
'client_id' => 2,
'cip_code' => 1,
'cip_name' => 'A',
'web_url' => 'https://example.com/',
],
[
'client_id' => 2,
'cip_code' => 2,
'cip_name' => 'B',
'web_url' => 'https://example.com/',
],
]);
}
};
Results:
$ php artisan app:run
array:3 [
0 => {#514
+"id": 1
+"cip_code": 1
+"cip_name": "A"
+"web_url": "https://example.com/"
}
1 => {#517
+"id": 2
+"cip_code": 2
+"cip_name": "B"
+"web_url": "https://example.com/"
}
2 => {#518
+"id": 3
+"cip_code": 3
+"cip_name": "C"
+"web_url": "https://example.com/"
}
] // routes/console.php:24
The only thing I can think of if is any of the codes on the $cipCodes array is an Expression object.
In that case, you can change your array_map call to:
use Illuminate\Database\Query\Expression;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
// ...
// Format the CIP codes for the FIELD function
$formattedCipCodes = implode(',', array_map(function ($code) {
return match ($code instanceof Expression) {
true => "'" . $code->getValue(DB::connection()->getQueryGrammar()) . "'",
default => "'" . $code . "'",
};
}, $cipCodes));