Get Object from database by many-to-many relation's Object field

Question

I'm building an API for a webapplication. One of the custom routes I'm building can be used to get all reports from the database that use a dataset from a specific location. At the moment this route is a bit of a bottleneck and I'm trying to speed things up.

A report can contain multiple sets of data. Data can also belong to multiple reports. The Data table contains all locations of datasets from another (external) database.

To clarify my database look like this:

  Report              pivot table          Data
|-----------|        |-----------|        |-------------|
| Report_id |-------<| Report_id |>-------| Data_id     |
|           |        | Data_id   |        | Location_id |
|-----------|        |-----------|        |-------------|

I have written the following script to get all reports using a certain dataset but I feel like this can be done more efficiently.

 $reports = Report::whereIn('id',
                \DB::table('report_data')->select('report_id')->whereIn('data_id',
                    \DB::table('data')->select('id')->where('location_id', '=', $id)
                )
             )->get();

Am I missing something obvious here or is this the best way to go about doing this?

Answer 1

in models

Data.php

public function Report()
{
return $this->hasMany('App\Report');
}

Report.php

public function Data()
{
return $this->belongsToMany('App\Data');
}

(you can also give which all fields should come in select query) and in controller

// To get reports which are having any data
$reports1 = Report::whereHas('Data',function($query) use($id){
    $query->select('id');
    $query->where('location_id',$id);
})->orderBy('id', 'desc')
  ->get();

// To get all reports

$reports2 = Report::with(array('Data'=>function($query)use($id){
    $query->select('id');
    $query->where('location_id',$id);
}))->orderBy('id', 'desc')
    ->get();