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.

Marcin Nabiałek · Accepted Answer · 2017-03-30 15:31:24Z

9

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.

answered Mar 30, 2017 at 15:31

Marcin Nabiałek

112k43 gold badges271 silver badges299 bronze badges

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

1 Comment

Mdalz Over a year ago

Thank you, that worked. I guess I was a bit confused because I've eager loaded relationships like this in the past without requiring an identifying key. Not quite sure what the difference between the two models is, but I'll have to investigate further.

Collectives™ on Stack Overflow

Eager loading relationship returns empty using SELECT in WITH clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related