星期一, 2018-05-21 16:06 — adminshiping1

i am looking to produce the following query in laravel 5.1 with eloquent method.

The mysql query is a follows

SELECT * FROM orders WHERE 1 = 1 ORDER BY o_date DESC LIMIT 25

No matter what i cant get the

WHERE 1 = 1

part working.

am new to laravel and pretty sure this is easy. but can't figure it out.

I have tried the following variations

$orders = orders::where('1', 1)->orderBy('o_date', 'desc')->take(25)->get();
$orders = orders::where(1)->orderBy('o_date', 'desc')->take(25)->get();
$orders = orders::where('1', '=', '1')->orderBy('o_date', 'desc')->take(25)->get();

but its not working. the query results is as shown below

> select count(*) as aggregate from `orders`

2 Answers 正确答案

active oldest votes

up vote2down voteaccepted

Seems like (looking at 1=1) you need whereRaw

$orders = orders::whereRaw("any clause u wish")->orderBy('o_date', 'desc')->take(25)->get();

but if "any clause u wish" is not a VERY-VERY dinamical part u'd better look what else you can use

http://laravel.com/api/5.0/Illuminate/Database/Query/Builder.html

For the above example the below code works fine

$orders = orders::whereRaw("1 = 1")->orderBy('o_date', 'desc')->take(25)->get();

edited Jul 29 '15 at 7:28

answered Jul 29 '15 at 7:07

M0rtiis

3,0991720

add a comment

up vote1down vote

Why do you want to create a condition that will be always true? I think that Eloquent is smart enought to remove this useless part of the query.

answered Jul 29 '15 at 6:20

Romain Lanz

517211

add a comment

来自 https://stackoverflow.com/questions/31691687/empty-where-clause-in-laravel-5-1-preferring-eloquent-method?rq=1

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

0down vote favorite

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.

2 Answers 正确答案

3down voteaccepted

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.

edited Sep 16 '15 at 17:46

answered Sep 16 '15 at 16:43

Joseph Silber

142k35268230

add a comment

up vote4down vote

You haven't posted the error, but I'm guessing you're getting:

Column not found: 1054 Unknown column '1' in 'where clause'

That's because the first parameter of the where method is expected to be a column name and will get quoted. So in your case when you have:

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

the SQL generated would be:

WHERE `1` = 1

So you should be using whereRaw to avoid any quoting:

$query = $baseQuery->whereRaw('1 = 1');

来自 https://stackoverflow.com/questions/32613244/laravel-query-builder-where1-1-getting-error

普通分类:

laravel

You are here

Empty where clause in Laravel 5.1 preferring eloquent method where 1等于1 1=1 有大用

2 Answers 正确答案

来自 https://stackoverflow.com/questions/31691687/empty-where-clause-in-laravel-5-1-preferring-eloquent-method?rq=1

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

2 Answers 正确答案

友情链接

搜索表单

用户登录

You are here

Empty where clause in Laravel 5.1 preferring eloquent method where 1等于1 1=1 有大用

2 Answers 正确答案

来自 https://stackoverflow.com/questions/31691687/empty-where-clause-in-laravel-5-1-preferring-eloquent-method?rq=1

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

2 Answers 正确答案

友情链接