Advanced Query Help

Post models and tables

Possible Models:

Facility: Methods(techs(), files('fileable'))
Tech: Methods(facility(), files()) //table has(facility_id)
File: Methods(facilities(), techs()) //table has(morph(fileable(example: 'App\Models\Facility' or 'App\Models\Tech'), uploader_id/user_id/tech_id))

Then you can pull the data a few different ways:

$facility = Facility::find(id);
$facility->files _> foreach(file $file->tech/uploader_id)
$facility->techs _> foreah(tech $files->if(matches facility id))

You can use "groupBy" clause:

$user_id = Auth::id();
$data = DB::table('facilities')->where('user_id', '=', $user_id)
            ->join('facility_tech', 'facilities.id', 'facility_tech.facility_id')
            ->join('techs', 'facility_tech.tech_id', 'techs.id')
            ->join('files', 'techs.id', 'files.fileable_id')
	    ->groupBy('techs.id')
            ->get();

You can fetch the files from a second query:

$user_id = Auth::id();

// As you are interested only on techs
$data = DB::table('techs')
    ->whereExists(function ($query) use ($user_id) {
        $query->selectRaw(1)
            ->from('facilities')
            ->join('facility_tech', 'facilities.id', 'facility_tech.facility_id')
            ->whereColumn('facility_tech.tech_id', 'techs.id')
            ->where('user_id', '=', $user_id);
    })
    ->get();

$files = DB::table('files')
    ->whereIn('id', $data->pluck('id'))
    ->get();

// merge the files with the original data
foreach ($data as $record) {
    $record->files = $files->where('fileable_id', $record->id)
        ->values()
        ->toArray();
}

dd($data);

Seems a lot of work, right?

As @adgower implictly suggested, Laravel does the heavy lifting if you are using Eloquent Models with the correct relationships set.

Thanks to everyone that responded. First, I do have relationships set up. Here is what I currently have.

class Facility extends Model
{
    public function user()
    {
        return $this->belongsTo('App\User');
    }

    public function techs()
    {
        return $this->belongsToMany(Tech::class);
    }

    public function files()
    {
        return $this->morphMany(File::class, 'fileable');
    }

class File extends Model
{
    protected $fillable = [ 'filename', 'file_extension', 'file_url_path', 'fileable_id', 'fileable_type' ];

    public function fileable()
    {
        return $this->morphTo();
    }
}

class Tech extends Model
{
    protected $table = 'techs';

    protected $fillable = ['first_name', 'last_name', 'email', 'phone'];
    
    public function getFullNameAttribute()
    {
        return "{$this->first_name} {$this->last_name}";
    }

    public function files()
    {
        return $this->morphMany('App\File', 'fileable');
    }

    public function facilities()
    {
        return $this->belongsToMany(Facility::class);
    }
}

class User extends Authenticatable
{
    use Notifiable;

    protected $fillable = [
        'first_name', 'last_name', 'role', 'email', 'password',
    ];

    public function facility()
    {
        return $this->belongsTo('App\Facility', 'id', 'user_id');
    }

}

so mr-shahin, I tried that early using groupBy. It groups the techs but only gives one file per tech. So in this example David Smith has two files associated with him, but it only gives one.

$user_id = Auth::id();
$data = DB::table('facilities')->where('user_id', '=', $user_id)
            ->join('facility_tech', 'facilities.id', 'facility_tech.facility_id')
            ->join('techs', 'facility_tech.tech_id', 'techs.id')
            ->join('files', 'techs.id', 'files.fileable_id')
	    ->groupBy('techs.id')
            ->get();

dd
Illuminate\Support\Collection {#1428 ▼
  #items: array:2 [▼
    0 => {#1429 ▼
      +"id": 8
      +"name": "Feelgood ER"
      +"address_one": "123 Test"
      +"address_two": null
      +"city": "Test City"
      +"state": "texas"
      +"zip": "12345"
      +"phone": "9876543210"
      +"message": "Hello Feelgood ER, this is a test message."
      +"call_schedule_one_title": "Nov 2020 Call Schedule"
      +"call_schedule_one": "8"
      +"call_schedule_two_title": "Dec 2020 Call Schedule"
      +"call_schedule_two": "9"
      +"user_id": 2
      +"created_at": "2020-10-20 20:00:52"
      +"updated_at": "2020-10-20 20:00:52"
      +"facility_id": 1
      +"tech_id": 4
      +"first_name": "David"
      +"last_name": "Smith"
      +"email": "[email protected]"
      +"filename": "4_sample-pdf.pdf"
      +"file_extension": "pdf"
      +"file_url_path": "10-2020/4_sample-pdf.pdf"
      +"fileable_id": 4
      +"fileable_type": "App\Tech"
    }
    1 => {#1426 ▼
      +"id": 10
      +"name": "Feelgood ER"
      +"address_one": "123 Test"
      +"address_two": null
      +"city": "Test City"
      +"state": "texas"
      +"zip": "12345"
      +"phone": "8172158600"
      +"message": "Hello Feelgood ER, this is a test message."
      +"call_schedule_one_title": "Nov 2020 Call Schedule"
      +"call_schedule_one": "8"
      +"call_schedule_two_title": "Dec 2020 Call Schedule"
      +"call_schedule_two": "9"
      +"user_id": 2
      +"created_at": "2020-10-20 20:01:23"
      +"updated_at": "2020-10-20 20:01:23"
      +"facility_id": 1
      +"tech_id": 5
      +"first_name": "Jackie"
      +"last_name": "Smith"
      +"email": "[email protected]"
      +"filename": "5_sample-pdf.pdf"
      +"file_extension": "pdf"
      +"file_url_path": "10-2020/5_sample-pdf.pdf"
      +"fileable_id": 5
      +"fileable_type": "App\Tech"
    }
  ]
}

rodrigo.pedra

this one gives us two techs data only without any of their files. So yes, I am interested in techs and the files associated with them but I also need the rest of the tables I joined information. This results set does not return any techs files.

I am creating a Facility Dashboard. so when the user logins, it will see all of their facility data, along with any techs that are associated with the facility, and of course, that techs files.

Illuminate\Support\Collection {#1421 ▼
  #items: array:2 [▼
    0 => {#1422 ▼
      +"id": 4
      +"first_name": "David"
      +"last_name": "Smith"
      +"email": "[email protected]"
      +"phone": "9876543210"
      +"created_at": "2020-10-20 20:00:52"
      +"updated_at": "2020-10-20 20:00:52"
      +"files": []
    }
    1 => {#1395 ▼
      +"id": 5
      +"first_name": "Jackie"
      +"last_name": "Smith"
      +"email": "[email protected]"
      +"phone": "8172158600"
      +"created_at": "2020-10-20 20:01:23"
      +"updated_at": "2020-10-20 20:01:23"
      +"files": []
    }
  ]
}

when I start using Eloquent. I continued to have issues getting a techs files. for example.

$user_id = Auth::id();
$user = User::findOrFail($user_id);

$facility = $user->facility;   // this works, I get the facility data associated with the User
$techs = $facility->techs;  // this works since I have the $facility instance. It returns the 2 techs associated with the facility. 

$techfiles = $techs->files;   //  returns empty result set. I assume since $techs is a multiple results set, there are two different $techs in the result set. 

So then I moved to using the query builder as below. which gives me everything I need except the grouping option. It returns the 3 files in result sets. I need the result set to return all data, and return the 2 techs grouped together, and have their files results inside each tech.

  $user_id = Auth::id();
  $data = DB::table('facilities')->where('user_id', '=', $user_id)
            ->join('facility_tech', 'facilities.id', 'facility_tech.facility_id')
            ->join('techs', 'facility_tech.tech_id', 'techs.id')
            ->join('files', 'techs.id', 'files.fileable_id')
            ->groupBy('techs.id')
            ->get();
        dd($data);

Thanks again for everyones help!

Mine has a typo on the second query:

$files = DB::table('files')
    ->whereIn('fileable_id', $data->pluck('id'))
    ->get();

I am not at my office right now, but I can check later how to do it using your Eloquent models and relations

rodrigo.pedra

yes adjusting the typo from id to fileable_id did the trick.

  #items: array:2 [▼
    0 => {#1422 ▼
      +"id": 4
      +"first_name": "David"
      +"last_name": "Smith"
      +"email": "[email protected]"
      +"phone": "9876543210"
      +"created_at": "2020-10-20 20:00:52"
      +"updated_at": "2020-10-20 20:00:52"
      +"files": array:2 [▼
        0 => {#218 ▼
          +"id": 8
          +"filename": "4_sample-pdf.pdf"
          +"file_extension": "pdf"
          +"file_url_path": "10-2020/4_sample-pdf.pdf"
          +"fileable_id": 4
          +"fileable_type": "App\Tech"
          +"created_at": "2020-10-20 20:00:52"
          +"updated_at": "2020-10-20 20:00:52"
        }
        1 => {#1379 ▼
          +"id": 9
          +"filename": "4_sample-pdf-2.pdf"
          +"file_extension": "pdf"
          +"file_url_path": "10-2020/4_sample-pdf-2.pdf"
          +"fileable_id": 4
          +"fileable_type": "App\Tech"
          +"created_at": "2020-10-20 20:00:52"
          +"updated_at": "2020-10-20 20:00:52"
        }
      ]
    }
    1 => {#1395 ▼
      +"id": 5
      +"first_name": "Jackie"
      +"last_name": "Smith"
      +"email": "[email protected]"
      +"phone": "8172158600"
      +"created_at": "2020-10-20 20:01:23"
      +"updated_at": "2020-10-20 20:01:23"
      +"files": array:1 [▶]
    }
  ]
}

thanks so much for your help.

Great it worked out!

2 things:

1 - Another fix to the second query

As you are using a polymorphic relation on your files database, consider adding a second where clause to filter the fileable_type to avoid conflicts on entities with the same fileable_id and different fileable_type:

$files = DB::table('files')
    ->whereIn('fileable_id', $data->pluck('id'))
    ->where('fileable_type', 'App\Tech') // Maybe you'll need to escape it: 'App\Tech'
    ->get();

2 - Eloquent version

Based on your Eloquent models this should work:

$user_id = Auth::id();

$data = Tech::query()
    ->with(['files'])
    ->whereHas('facilities', function ($relation) use ($user_id) {
        $relation->where('user_id', $user_id);
    })
    ->get();

dd($data);

Both queries, and matching by fileable_id and fileable_type is done automatically for you under the hood.

And, in my opinion, is much more expressive.

Have a nice day =)