Laravel Eloquent - Unable to fetch columns using array

Question

I am using laravel eloquent to fetch the data from multiple columns. I am using array to fetch the data from multiple columns. For simplicity, I am showing the data from two columns.

Here is my database table which has callee_number and caller_number:

Here is my code where I am using array for fetching the columns:

    $paramArray = $params->all();

    $Caller_number = "";
    $Callee_number = "";

    if (isset($paramArray['Caller_number']) && $paramArray['Caller_number'] != '') {
        $Caller_number = $params->input('Caller_number');
    }
    if (isset($paramArray['Callee_number']) && $paramArray['Callee_number'] != '') {
        $Callee_number = $params->input('Callee_number');
    }

    if($Caller_number!=null){
        $querypart[] = "caller_number=$Caller_number";
    }
    if($Callee_number!=null){
        $querypart[] = "callee_number=$Callee_number";
    }


    $FinalQueryPart = '';
    if (!empty($querypart)) {
        
        $Seprator = "";
        foreach ($querypart as $querypartValue) {
            $FinalQueryPart .= $Seprator . $querypartValue;
            $Seprator = " AND ";
        }
    }

    $FinalQueryPart = trim($FinalQueryPart); //Output: "caller_number=783 AND callee_number=77"

However I get the error after I run the following code after above code:

    $gets = Tbcdrnew::where(function ($query) use ($FinalQueryPart) {
        $query->MIN('uniqueid')->where($FinalQueryPart)
            ->from('cdrnew');
            
    })->groupBy('uniqueid')->orderBy('Start_Date','DESC')->get();

The error is as follows:

Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'caller_number=783 AND callee_number=77' in 'where clause' (SQL: select * from `cdrnew` where (`caller_number=783 AND callee_number=77` is null) group by `uniqueid`

Not getting what is wrong in my code. Can anybody correct me ?

Unknown column 'caller_number=783 AND callee_number=77'

jewishmoses
– jewishmoses

2020-08-05 06:58:28 +00:00
Commented Aug 5, 2020 at 6:58 — jewishmoses
– jewishmoses, Commented Aug 5, 2020 at 6:58
Change where() to whereRaw()

jewishmoses
– jewishmoses

2020-08-05 07:01:52 +00:00
Commented Aug 5, 2020 at 7:01 — jewishmoses
– jewishmoses, Commented Aug 5, 2020 at 7:01

JesusValera · Accepted Answer · 2020-08-05 07:23:19Z

1

I think you commited a mistake in the query. Replace the following lines by those ones:

if($Caller_number!=null){
    $querypart[] = "caller_number = '$Caller_number'";
}
if($Callee_number!=null){
    $querypart[] = "callee_number = '$Callee_number'";
}

(Note the single quotes on the $Caller_number & $Callee_number).

Also, using the whereRaw() method instead of where() as it is suggested in a response comment.

edited Aug 5, 2020 at 7:23

answered Aug 5, 2020 at 7:03

JesusValera

6576 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JesusValera Over a year ago

@Shreeniwas sorry, I placed the quotes on the other side. Try again

user14034082 Over a year ago

But requires whereRaw

JesusValera Over a year ago

Yes, you are right. Another possibility would be to check manually if the caller_number is not empty and adding the where() clause to the $query object (and the same with the other var), it's a bit tricky. I think the current implementation is good enough :)

user3532758 · Accepted Answer · 2020-08-05 07:53:12Z

I'm adding an answer suggesting a method to simplify your code by eliminating all the if conditions using Laravel collection methods.

$query = Tbcdrnew::query();                           //start a query string based on Tbcdrnew model OR you could do DB::table('cdrnew');
collect($paramArray)->filter(function($value, $key){  //convert paramArray into a collection
    return $value != '';                              //filter all null and empty values
})->each(function($value, $key) use ($query){         //iterate through all filtered values
    $query->where(strtolower($key), $value);          //assuming array key is always column key in lowercase
});
$result = $query->groupBy('uniqueid')->orderBy('start_date','desc')->get();

I'm not sure if $query->MIN you have in your original query is a valid query builder function.

Collectives™ on Stack Overflow

Laravel Eloquent - Unable to fetch columns using array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related