CODEWITHSUNDEEP
laravellaravel-5eloquent
Matthew
Matthew

Reputation: 1655

Laravel DB query with filtering from another table

At the moment in one of my controllers, I have the following query:

$maintenances = DB::table("equipment_attachments")
                ->select(DB::raw('year(date) as year'), DB::raw("COUNT(*) as count"))
                ->where('attachmentCategory','Maintenance')
                ->orderBy(DB::raw("year(date)"))
                ->groupBy(DB::raw("year(date)"))
                ->get();

And this works alright, but I'd like to filter it down even more, and I believe I know how to do this through models, but not quite sure with using the DB functions.

What I would like to be able to do is use another table called equipment and use a field called type to filter down to those with a type of only 3.

So basically I'd like to filter down from what is above but then proceed to use the field in the table equipment_attachments to where the equipment referenced in the field equipment_attachments.unitID is equal to a equipment where the equipment.type field is equal to 3.

Upvotes: 1

Views: 3770

Answers (2)

Ruman
Ruman

Reputation: 191

You can simply add a join and the use a where clause. your final query would look something like this.

DB::table("equipment_attachments") 
    ->join('equipments', 'equipments.id' , '=', 'equipment_attachments.unitID'
    ->select(DB::raw('year(date) as year'), DB::raw("COUNT(*) as count")) 
    ->where('attachmentCategory','Maintenance') 
    ->where('equipments.type', 3)
    ->orderBy(DB::raw("year(date)")) 
    ->groupBy(DB::raw("year(date)")) 
    ->get();

Also you can simply use selectRaw to select using SQL functions.

   DB::table("equipment_attachments") 
    ->join('equipments', 'equipments.id' , '=', 'equipment_attachments.unitID'
    ->selectRaw("year(date) as year, COUNT(*) as count") 
    ->where('attachmentCategory','Maintenance') 
    ->where('equipments.type', 3)
    ->orderBy(DB::raw("year(date)")) 
    ->groupBy(DB::raw("year(date)")) 
    ->get();

Upvotes: 1

OAFCROB
OAFCROB

Reputation: 81

You have two options;

  1. If you're using the query builder i.e. DB::query() then you'll need to join on the relevant tables and apply a where clause in that way.

Query Builder

    $equipment = DB::table('equipment_attachments')
            ->join('equipment', 'equipment.id', '=', 'equipment_attachments.equipment_id')
            ->where('type', 3)
            ->select('equipment_attachments.*')
            ->get();
  1. If you're using the eloquent ORM then you can use a whereHas() statement, but you'll need to define the relationship within the model first.

Eloquent Relationships 

    EquipmentAttachment::whereHas('equipment', function($query) {
         $query->where('type', 3);
    })->all();

Personally, I would look at using the ORM as technically you wouldn't need to do any raw SQL meaning you can swap DB language easier if you wanted. I'm not sure on your date format in the DB, but if it's a timestamp you could achieve the same in the ORM with; 

    EquipmentAttachment::where('date', (new DateTime())->format('Y')->all();

Finally, if you take of the first(), get() or all() you can put ->toSql() and the SQL statement can be echoed out, for example;

    echo EquipmentAttachment::where('date', '2018-06-14')->toSql();
    dd();

Hope that helps a little.

Upvotes: 0

Related Questions