Laravel - Query Builder Where Syntax

Question

I have some difficulties in laravel query builder.

I have a syntax like this :

$data['tmasuk'] =TSediam::where('kd_lokasi', $kd_lokasi)->where('kd_brg', $kd_brg)->where('DATEPART(YYYY,tgldok)',$tgldok)->get();

it says error

An uncaught Exception was encountered Type: Illuminate\Database\QueryException Message: SQLSTATE[42S22]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'DATEPART(YYYY,tgldok)'. (SQL: select * from [t_sediam] where [kd_lokasi] = 023040500414964000KD and [kd_brg] = 1010302001000001 and [DATEPART(YYYY,tgldok)] = 2015) Filename: C:\wamp\www\sedia\vendor\illuminate\database\Connection.php Line Number: 651

**

But, if I check the query via sql server management studio it's work

select * from [t_sediam] where [kd_lokasi] = '023040500414964000KD' and [kd_brg] = '1010302001000001' and DATEPART(YYYY,tgldok) = '2015'

So, what is the solution? thanks

Moppo · Accepted Answer · 2015-12-07 09:50:47Z

1

Try to use DB::raw:

$data['tmasuk'] = TSediam::where('kd_lokasi', $kd_lokasi)
                          ->where('kd_brg', $kd_brg)
                          ->where( \DB::raw( "DATEPART(YYYY,tgldok) = '" . $tgldok . "'"  ) )
                          ->get();

edited Dec 7, 2015 at 9:50

answered Dec 7, 2015 at 9:12

Moppo

19.4k5 gold badges68 silver badges66 bronze badges

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

5 Comments

Yoga Bayu Aji Pranawa Over a year ago

It says eror Message: Class 'DB' not found

Moppo Over a year ago

try with \DB::raw instead of DB::raw

Moppo Over a year ago

Then try to specify this: use Illuminate\Support\Facades\DB; (or only use DB; in the top of your file, and use DB::raw

Yoga Bayu Aji Pranawa Over a year ago

it says error Message: A facade root has not been set.

Moppo Over a year ago

you have some problem accessing your Facades, does the other Facades work on your application ?

astroanu · Accepted Answer · 2015-12-07 09:35:51Z

0

You can't query a where like that;

->where('DATEPART(YYYY,tgldok)',$tgldok)

means you are searching for a field by the name DATEPART(YYYY,tgldok)

try something like this:

$data['tmasuk'] = TSediam::select('DATEPART(YYYY,tgldok) AS datepart')
  ->where('kd_lokasi', $kd_lokasi)
  ->where('kd_brg', $kd_brg)
  ->where('datepart',$tgldok)
  ->get();

answered Dec 7, 2015 at 9:35

astroanu

4,0392 gold badges39 silver badges51 bronze badges

2 Comments

Yoga Bayu Aji Pranawa Over a year ago

it says error Message: SQLSTATE[42S22]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'datepart'. (SQL: select [DATEPART(YYYY,tgldok)] as [datepart] from [t_sediam] where [kd_lokasi] = 023040500414964000KD and [kd_brg] = 1010302001000001 and [datepart] = 8)

astroanu Over a year ago

i haven't used ODBC driver, check the reference for how to write a SELECT ... AS .. query..

Collectives™ on Stack Overflow

Laravel - Query Builder Where Syntax

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related