laravel - Select Specific columns from multiple Table with with() and select ()

Question

I have 2 tables test and goals. One goal has many tests. I would like to fetch data like below.

$tests = test::with('goals')
                ->where('Goal_id', $goal_id)
                ->select('test.id as id','goals.Goal_id as goal_id','test.mode as mode')
                ->get();

But I am getting error IlluminateDatabaseQueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'goals.Goal_id' in 'field list'

question from:https://stackoverflow.com/questions/65916366/select-specific-columns-from-multiple-table-with-with-and-select

Answer 1

The goals relation is being eager loaded in a separate query so you will not have access to goals.Goal_id in your main query builder, instead you can modify your with() clause to pick specific columns from eager loaded relation as with('goals:Goal_id,another_column')

$tests = test::with('goals:Goal_id')
                ->where('Goal_id', $goal_id)
                ->select(['test.id as id','test.mode as mode'])
                ->get();