Eager loading relationship returns empty using SELECT in WITH clause

Question

Using Laravel 5.4, I have a query that correctly returns a relationship. Using the "with" clause in the query, I am attempting to return only selected columns from the relationship in my controller.

When I add the select to the with clause, relationship returns an empty array. Oddly enough, if I add a different parameter, such as a groupBy or join the query DOES return results. So something about my setup dislikes the select on the query.

Thus far I have tried:

• using selectRaw
• using select(DB::raw)
• tried defining this as a separate relationship on my model.

Nothing has worked this far. Sql log looks good when I dump it.

Here is my model:

// MODEL
namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;

class ClassBlocks extends Model
{
    public $timestamps = false;

    public function schedule(){
        return $this->hasMany('App\ClassSchedules', 'class_block_id', 'id');
    }
}

And here is my controller:

//CONTROLLER
use App;
use DateTime;
use Illuminate\Http\Request;

class ProgramsController extends Controller
{
    public function filterClass(Request $request, App\ClassBlocks $block)
    {

        $block = $block->newQuery();

        // Attempt to eager load relationship
        // Returns results when "select" disabled
        $block->with([
            'schedule' => function($query){
                $query->select('time_start');
                $query->groupBy('day');
            },
        ]);

        return $block->get();

    }
}

Here is a sample result with select enabled (schedule returns empty):

[
  {
    "id": 13,
    "program_id": "1",
    "class_group_id": "1",
    "schedule": [

    ]
  }
]

And here is a result with select disabled (returns relationship when select disabled):

[
  {
    "id": 13,
    "program_id": "1",
    "class_group_id": "1",
    "schedule": [
      {
        "id": 338,
        "class_group_id": "1",
        "program_id": "1",
        "class_block_id": "13",
        "date": "06/13/2017",
        "day": "Tuesday",
        "instructor_id": "1",
        "time_start": "6:30am",
        "time_end": "6:30am"
      },
      {
        "id": 339,
        "class_group_id": "1",
        "program_id": "1",
        "class_block_id": "13",
        "date": "06/14/2017",
        "day": "Wednesday",
        "instructor_id": "2",
        "time_start": "6:30am",
        "time_end": "6:30am"
      }
    ]
  },
]

Any insight would be greatly appreciated.

Answer 1

The problem here is:

$query->select('time_start');

Laravel need to have column that is connection between 2 records. In this case you should probably use:

$query->select('time_start', 'class_block_id');

to make it work.

Obviously you will have class_block_id in response this way. If you really don't want it, probably you should create some transformer that will return exactly you want in response.