0

Here is the query I am using to get results of a camp:

$camp = Camp::where('camps.id', $camp_id)
    ->with(['athletes' => function ($q) use ($camp_id) {
    $q->with(['kickoffs' => function ($q) use ($camp_id) {
        $q->where('camp_id', $camp_id);
        $q->orderBy('id', 'desc');
    }]);
  $q->with(['kickoff_results' => function ($q) use ($camp_id) {
       $q->where('camp_id', $camp_id);
       $q->orderBy('score', 'desc');
   }]);

But the results are not getting ordered correctly. I have learned that I must join the tables so now my query looks like this:

$camp = Camp::where('camps.id', $camp_id)
    ->with(['athletes' => function ($q) use ($camp_id) {
    $q->with(['kickoffs' => function ($q) use ($camp_id) {
        $q->where('camp_id', $camp_id);
        $q->orderBy('id', 'desc');
    }])->join('kickoff_results', 'athletes.id', '=', 'kickoff_results.athlete_id')
       ->orderBy('kickoff_results.score', 'desc');

But this seems to be returning me the same thing. I feel like my results are more accurate with my first query, but the ordering is incorrect.

Any suggestions are greatly appreciated!

Community
  • 1
  • 1
Damon
  • 4,151
  • 13
  • 52
  • 108

1 Answers1

0

Create foreign key in the secondary table and join the table as follows:

DB::table('leads')->select('sec_tbname.sec_tbfk','sec_tbname.join_pm','prm_tbname.prm_tbpm')
            ->join('pm_tbname','sec_tbname.sec_tbfk','=','pm_tbname.id')
            ->get();
Kayal
  • 23
  • 7