CODEWITHSUNDEEP
phplaravellaravel-5eloquentlaravel-5.1
code-8
code-8

Reputation: 58810

Eloquent where clause for timestamp in Laravel 5.1

I have a table and search bar.

enter image description here

When user input in the search that when I grab that and query my database.

This is what I got

public function getLogsFromDb($q = null) {

    if (Input::get('q') != '') {
        $q = Input::get('q');
    }
    $perPage = 25;

    if ($q != '') {

        $logs = DB::table('fortinet_logs')
            ->orWhere('account_id', 'like', '%'.$q.'%')
            ->orWhere('cpe_mac', 'like', '%'.$q.'%')
            ->orWhere('p_hns_id', 'like', '%'.$q.'%')
            ->orWhere('g_hns_id', 'like', '%'.$q.'%')
            ->orWhere('data', 'like', '%'.$q.'%')
            ->orWhere('created_at', 'like', '%'.$q.'%') <----🐞
            ->orderBy('updated_at', 'desc')->paginate($perPage) 
            ->setPath('');


            //dd($logs);

        $logs->appends(['q' => $q]);

    } else {

        $logs = DB::table('fortinet_logs')
            ->orderBy('created_at', 'desc')->paginate($perPage)
            ->setPath('');
    }

    return view('telenet.views.wizard.logTable', get_defined_vars());

}

Result

In the network tab, I kept getting

Undefined function: 7 ERROR: operator does not exist: timestamp without time zone ~~ unknown

enter image description here

How would one go about debugging this further?

Upvotes: 0

Views: 726

Answers (3)

Szab&#243; Kevin
Szab&#243; Kevin

Reputation: 56

first step on debuggin to make an sql by tihs. And you look what is the problem for your query. Like this:

 dd($logs = DB::table('fortinet_logs')
            ->orWhere('account_id', 'like', '%'.$q.'%')
            ->orWhere('cpe_mac', 'like', '%'.$q.'%')
            ->orWhere('p_hns_id', 'like', '%'.$q.'%')
            ->orWhere('g_hns_id', 'like', '%'.$q.'%')
            ->orWhere('data', 'like', '%'.$q.'%')
            ->orWhere('created_at', 'like', '%'.$q.'%')//This is the cri
            ->orderBy('updated_at', 'desc')->toSql());

This is show for you whats the problem of this query. By the way, can you cast $q to daterange and can find in this range the logs.

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

PostgreSQL is strict about different data types. Convert the timestamp column to text:

->orWhereRaw('created_at::text like ?', ['%'.$q.'%'])

Upvotes: 2

Mahdi Younesi
Mahdi Younesi

Reputation: 7509

sanitize your inputs to avoid such problems

$remove = array("'","!",";","+",'|','||',"&&","}","{","[","]");
$replace = array("","","","","","","","","","","");

$q = str_replace($remove, $replace, $q);

Use the new defined $q in your queris

Upvotes: 1

Related Questions