CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-query-builder
Lafoune
Lafoune

Reputation: 476

Convert SQL to Laravel

Let's say I have this:

SELECT * FROM drivers where driver_number NOT IN (SELECT driver_number FROM buses) AND station_id = 2 OR driver_number = 'Dr_02'

and it displays this data enter image description here

Then i want to get this data as laravel query and here is what I did:

$drivers = Driver::whereRaw('driver_number not in (select driver_number from buses) AND station_id = ? ', [$station_id].' OR id = ?', [$bus->Driver_id])
                    ->get();

But I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'driver_number not in (select driver_number from buses) AND station_id = ?' at line 1 (SQL: select * from drivers where id = 2 driver_number not in (select driver_number from buses) AND station_id = ?)

I think there is error the way put it, that's it gets a type system error. So if anyone knows a better way to do it, you can help.

Upvotes: 1

Views: 94

Answers (3)

Binara Medawatta
Binara Medawatta

Reputation: 532

Give this a try,

$result = DB::table('drivers')
->whereNotIn('driver_number', function($q){
    $q->select('driver_number')->from('buses');
})
->where('station_id', '=', 2)
->where('driver_number', '=', 'Dr_02')
->get();

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

This is tricky to handle in Laravel. The logic in your WHERE clause is basically (A and B) or C, where condition A can be represented by a NOT EXISTS subquery in MySQL.

$drivers = DB::table('Driver d')
    ->where(function($query1) {
        return $query1
            ->whereNotExists(function ($query2) {
                $query2->select(DB::raw(1))
                    ->from('buses b')
                    ->whereRaw('d.driver_number = b.driver_number');
              })
            ->where('station_id', '=', '2');
    })
    ->orWhere('driver_number', '=', 'Dr_02');
->get();

Upvotes: 2

Bhavesh Tailor
Bhavesh Tailor

Reputation: 243

You can try using

$task_without_due_date = \DB::select(\DB::raw("SELECT * FROM drivers where driver_number NOT IN (SELECT driver_number FROM buses) AND station_id = '.$station_id.' OR driver_number = '.$bus->Driver_id.'"));

Upvotes: 0

Related Questions