Hallo,
ich benutze einen komplexen Query, um Datensätze abzurufen
Dieser query liefert auch das erwartete Ergebnis
Jetzt will ich einige whereIn() dynamisch setzen:
Das geht schief, statt das erwartete Ergebnis, bekomme ich
Warum funktioniert das dynamische setzen nicht?
ich benutze einen komplexen Query, um Datensätze abzurufen
PHP-Code:
...
$vehicles = DB::table('vehicles AS v')
->leftJoin('vehicle_manufactures as vmfct', 'v.id_manufacture', '=', 'vmfct.ID')
->leftJoin('vehicle_types as vt', 'v.id_type', '=', 'vt.ID')
->leftJoin('vehicle_models as vModel', 'v.id_model', '=', 'vModel.ID')
->leftJoin('vehicle_approvals as va', 'v.ID', '=', 'va.id_vehicle')
->leftJoin('register_districts as rd', 'va.id_register_district', '=', 'rd.ID')
->leftJoin('vehicle_cession_data as vcd', 'v.ID', '=', 'vcd.id_vehicle')
->leftJoin('cession_forms as cf', 'vcd.id_cession_form', '=', 'cf.ID')
->leftJoin('vehicle_internal_data as vid', 'v.ID', '=', 'vid.id_vehicle')
->leftJoin('statuses', 'vid.id_status', '=', 'statuses.ID')
->leftJoin('users', 'v.id_editor', '=', 'users.ID')
->orderByRaw('FIELD(vid.id_status, 5, 7, 6)')
->orderBy($orderByKey, 'desc')
->whereIn('vid.id_status', $whereInStatus)
->whereIn('va.id_register_district', $whereInRegisterDistrict)
->whereIn('va.register_ident_char', $whereInRegisterChar)
->select(
'v.ID',
'vmfct.name AS nameVehicleManufacture',
'vt.german AS germanVehicleType',
'vModel.name AS germanVehicleModel',
DB::raw("CONCAT(UPPER(rd.code), ' - ', va.register_ident_char, ' ', va.register_ident_number) AS patent"),
'cf.german as cessionType',
DB::raw("DATE_ADD(vcd.cession_begin_date, INTERVAL vcd.contract_duration_in_months MONTH) as cessionContractFinish"),
'vid.id_status',
'statuses.german as currentStatusGerman',
'statuses.name as currentStatusName',
DB::raw("CONCAT(users.first_name, ' ', users.last_name) AS editorName"),
DB::raw("DATE_FORMAT(v.__last_change, '%d.%m.%Y') AS lastChange")
)
->paginate($this->request->query('items') ?? 10);
...
var_dump($vehicles);
die();
Code:
... object(Illuminate\Pagination\LengthAwarePaginator)[645] protected 'total' => int 6 protected 'lastPage' => int 1 protected 'items' => object(Illuminate\Support\Collection)[566] protected 'items' => array (size=6) 0 => object(stdClass)[605] ...
PHP-Code:
...
$vehicles = DB::table('vehicles AS v')
->leftJoin('vehicle_manufactures as vmfct', 'v.id_manufacture', '=', 'vmfct.ID')
->leftJoin('vehicle_types as vt', 'v.id_type', '=', 'vt.ID')
->leftJoin('vehicle_models as vModel', 'v.id_model', '=', 'vModel.ID')
->leftJoin('vehicle_approvals as va', 'v.ID', '=', 'va.id_vehicle')
->leftJoin('register_districts as rd', 'va.id_register_district', '=', 'rd.ID')
->leftJoin('vehicle_cession_data as vcd', 'v.ID', '=', 'vcd.id_vehicle')
->leftJoin('cession_forms as cf', 'vcd.id_cession_form', '=', 'cf.ID')
->leftJoin('vehicle_internal_data as vid', 'v.ID', '=', 'vid.id_vehicle')
->leftJoin('statuses', 'vid.id_status', '=', 'statuses.ID')
->leftJoin('users', 'v.id_editor', '=', 'users.ID')
->orderByRaw('FIELD(vid.id_status, 5, 7, 6)')
->orderBy($orderByKey, 'desc')
->whereIn('vid.id_status', $whereInStatus);
$a = true;
if($a === true) {
$vehicles
->whereIn('va.id_register_district', $whereInRegisterDistrict)
->whereIn('va.register_ident_char', $whereInRegisterChar);
}
$vehicles
->select(
'v.ID',
'vmfct.name AS nameVehicleManufacture',
'vt.german AS germanVehicleType',
'vModel.name AS germanVehicleModel',
DB::raw("CONCAT(UPPER(rd.code), ' - ', va.register_ident_char, ' ', va.register_ident_number) AS patent"),
'cf.german as cessionType',
DB::raw("DATE_ADD(vcd.cession_begin_date, INTERVAL vcd.contract_duration_in_months MONTH) as cessionContractFinish"),
'vid.id_status',
'statuses.german as currentStatusGerman',
'statuses.name as currentStatusName',
DB::raw("CONCAT(users.first_name, ' ', users.last_name) AS editorName"),
DB::raw("DATE_FORMAT(v.__last_change, '%d.%m.%Y') AS lastChange")
)
->paginate($this->request->query('items') ?? 10);
...
var_dump($vehicles);
die();
Code:
object(Illuminate\Database\Query\Builder)[470] public 'connection' => object(Illuminate\Database\MySqlConnection)[198] protected 'pdo' => object(PDO)[435] ...
Kommentar