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 Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'goals.Goal_id' in 'field list'

M Khalid Junaid · Accepted Answer · 2021-01-27 09:43:20Z

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();

answered Jan 27, 2021 at 9:43

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

Comments

Daniyal Ishaq · Accepted Answer · 2021-01-27 09:48:11Z

0

This should work:

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

answered Jan 27, 2021 at 9:48

Daniyal Ishaq

1747 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related