Laravel DB query with filtering from another table

Question

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.

Could try a join()

apokryfos
– apokryfos

2018-06-14 13:58:48 +00:00
Commented Jun 14, 2018 at 13:58 — apokryfos
– apokryfos, Commented Jun 14, 2018 at 13:58

Ruman · Accepted Answer · 2018-06-15 12:30:44Z

1

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();

edited Jun 15, 2018 at 12:30

answered Jun 15, 2018 at 12:25

Ruman

1911 silver badge10 bronze badges

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

Comments

OAFCROB · Accepted Answer · 2018-06-14 22:07:13Z

You have two options;

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();

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.

Collectives™ on Stack Overflow

Laravel DB query with filtering from another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related