Abrar Jahin Abrar Jahin - 6 months ago 37
PHP Question

Laravel Query Builder - where(1=1) getting error

I am trying to build this PHP MySQL query to Laravel Query builder:

$sql = "SELECT employee_name, employee_salary, employee_position, employee_city, employee_extension, DATE_FORMAT(employee_joining_date, '%Y-%m-%d') as employee_joining_date, employee_age ";
$sql .= " FROM employee WHERE 1=1";
if (!empty($EmployeeNameSeach) ) {
$sql .=" AND employee_name LIKE '".$EmployeeNameSeach."%' ";
}
if (!empty($SalarySeach)) {
$sql .= " AND employee_salary LIKE '".$SalarySeach."%' ";
}
if (!empty($PositionNameSeach) ) {
$sql .= " AND employee_position LIKE '".$PositionNameSeach."%' ";
}
if (!empty($CitySeach) ) {
$sql .= " AND employee_city LIKE '".$CitySeach."%' ";
}
if (!empty($ExtensionSeach) ) {
$sql .= " AND employee_extension LIKE '".$ExtensionSeach."%' ";
}
if (!empty($JoiningDataSeach) ) {
$sql .= " AND employee_joining_date LIKE '".$JoiningDataSeach."%' ";
}
if (!empty($AgeSeach) ) {
$sql .= " AND employee_age LIKE '".$AgeSeach."%' ";
}


And what I am doing is -

$baseQuery = DB::table('employee')->select('employee_name', 'employee_salary', 'employee_position', 'employee_city', 'employee_extension',"employee_joining_date", 'employee_age', 'employee_id');
//->select('name as user_name', 'email')

//Applying Filters
$query = $baseQuery->where(1, '=', 1);
if(!empty($EmployeeNameSeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$EmployeeNameSeach.'%');
if(!empty($SalarySeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$SalarySeach.'%');
if(!empty($PositionNameSeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$PositionNameSeach.'%');
if(!empty($CitySeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$CitySeach.'%');
if(!empty($ExtensionSeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$ExtensionSeach.'%');
if(!empty($JoiningDataSeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$JoiningDataSeach.'%');
if(!empty($AgeSeach))
$query = $query->orWhere('employee_name', 'LIKE', '%'.$AgeSeach.'%');

//Ordering and Limiting Current data
$query = $query->orderBy($columns[$column_index_for_ordering], $column_ordering_direction)
->skip($start_data_index)->take($per_page_content_number);
$data = $query->get();


So I am getting error for-

$query = $baseQuery->where(1, '=', 1);


Can anyone help me please?
Thanks.

Answer

There's no need for the WHERE 1 = 1 part. No need at all.

Also, as pointed out in the comment below, you want to use where, not orWhere.


Here's your code, considerably cleaned up:

$query = DB::table('employee')->select('employee_name', 'employee_salary', 'employee_position', 'employee_city', 'employee_extension', "employee_joining_date", 'employee_age', 'employee_id');

$filters = ['EmployeeNameSeach', 'SalarySeach', 'PositionNameSeach', 'CitySeach', 'ExtensionSeach', 'JoiningDataSeach', 'AgeSeach'];

foreach ($filters as $filter) {
    if (empty($$filter)) continue;

    $query->where('employee_name', 'LIKE', '%'.$$filter.'%');
}

$query->orderBy($columns[$column_index_for_ordering], $column_ordering_direction)
      ->skip($start_data_index)
      ->take($per_page_content_number);

$data = $query->get();

The code above uses$$filter as a variable variables to loop through the filters. I don't know where those filters some from, but you should probably have them in an associative array instead. Would make life easier.

Comments