CODEWITHSUNDEEP
laraveleloquent
Leo
Leo

Reputation: 63

Raw SQL to Eloquent

Could you help me convert this SQL query to Eloquent:

SELECT item.id, hired.item_id, hired.quantity - item.quantity as quanity 
FROM items item 
join hireds hired on hired.item_id = item.id 
WHERE item.quantity > hired.quantity 
order by item.id;

Upvotes: 1

Views: 296

Answers (5)

Leo
Leo

Reputation: 63

Thanks everyone for your help. I have achieved it in the following way:

$hireds = Hired::query()->select('item.id', 'hired.item_id')
            ->selectRaw('hired.quantity - item.quantity as quanity')
            ->from('items as item')
            ->rightJoin('hireds as hired', 'hired.item_id', '=', 'item.id')
            ->whereRaw('item.quantity < hired.quantity')
            ->groupBy('bill_id')->select('bill_id')
            ->selectRaw('count(*) as items, sum(hired.price) as price')
            ->get();

Upvotes: 0

Amy
Amy

Reputation: 131

You can use whereHas(). 

   Item::with('hireds')->whereHas('hireds',function($q){
        $q->whereRaw('items.quantity>hireds.quantity');
    })->get();

OR

Item::join('hireds','items.id','=','hireds.item_id')
    ->selectRaw('items.quantity - hireds.quantity as quantity')
    ->whereRaw('items.quantity>hireds.quantity')->get();

Upvotes: 1

Yasii
Yasii

Reputation: 1842

Assuming that your models are Item and Hire Add the following relationship to the Hire model.

public function item(){
    return $this->belongsTo(Item::class);
}

And in the controller 

$model = Hire::with('item')->get();

Upvotes: 0

Bhuvanesh Soni
Bhuvanesh Soni

Reputation: 206

You can create query with raw in laravel:-

$query = DB::table('work_times as wt')
                ->join('work_time_details as wtd', 'wt.work_time_id','=','wtd.work_time_id')
                ->where('admin_id', $employee_id)
                ->whereBetween('work_time_date', [$start_date, $end_date])
                ->groupBy('wtd.project_id')
                ->orderBy('wtd.project_id')
                ->select(
                    DB::raw('sum(work_time_detail_hrs) as total_hour'),
                    'wtd.work_time_detail_id',
                    'wtd.project_id',
                    'wtd.work_type_id',
                    'work_types.work_type_name',
                    'p.project_name'
                )
                ->get()
                ->toArray();

For this you needed to use DB class: 

use Illuminate\Support\Facades\DB;

Upvotes: 0

ajthinking
ajthinking

Reputation: 4558

That might look something like

DB::table('items')
    ->join('hireds', 'items.id', '=', 'hireds.item_id')
    ->select('item_id', 'quantity')
    ->where('items.quantity', '>', 'hireds.quantity')
    ->get()->sortBy('id');

Not 100% sure how to handle the conflicting attribute names.

Upvotes: 0

Related Questions