Laravel 5 Eloquent ORM select where - array as parameter

Question

I'm getting grade_id from the database:

$grade_id = DB::table('grades')->where('teacher_id',$teacher_id)->select('grade_id')->get();

and then I want to use that grade_id array in the where eloquent clause so I run

$home_feed = Home::join('home_grade', 'home_grade.home_id', '=', 'homes.id')
                               ->whereIn('grade_id', $grade_id)
                               ->get();

but when I run this I'm getting an error: Object of class stdClass could not be converted to string

What could be the problem? Thanks guys.

Try $grade_id = DB::table('grades')->where('teacher_id',$teacher_id)->select('grade_id')->first(); — Risul Islam
– Risul Islam, Commented Oct 9, 2016 at 13:18
Your first query probably returns collection object, so try $grade_id = DB::table('grades')->where('teacher_id',$teacher_id)->select‌('grade_id')->get()-‌>lists('grade_id')->‌all(); or DB::table('grades')->where('teacher_id',$teacher_id)->pluck('grade_id')->all() then second query — Ravisha Hesh
– Ravisha Hesh, Commented Oct 9, 2016 at 15:34

Paul Spiegel · Accepted Answer · 2016-10-09 23:05:56Z

2

Depending on laravels version your $grade_id is either an array or a collection of objects. What you need is an array or a collection of values. You can achieve that using the pluck() method insted of select() like IzzEps suggested.

But you can get the same result by passing a subquery to the whereIn() method:

$gradeSubquery = DB::table('grades')->where('teacher_id',$teacher_id)->select('grade_id');

$home_feed = Home::join('home_grade', 'home_grade.home_id', '=', 'homes.id')
                 ->whereIn('grade_id', $gradeSubquery)
                 ->get();

This way you will run only one query instead of two.

Update: Before version 5.2 you have to use lists() instead of pluck(). And the whereIn() method doesn't accept a Builder as second parameter. To get the same query you would need to use a closure:

$home_feed = Home::join('home_grade', 'home_grade.home_id', '=', 'homes.id')
    ->whereIn('grade_id', function($query) use($teacher_id) {
        $query->from('grades')
            ->where('teacher_id', $teacher_id)
            ->select('grade_id');
    })
    ->get();

edited Oct 9, 2016 at 23:05

answered Oct 9, 2016 at 17:35

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

5 Comments

user3714932 Over a year ago

When I use the subquery I get the following error Argument 1 passed to Illuminate\Database\Grammar::parameterize() must be of the type array, object given

Paul Spiegel Over a year ago

What is your laravel version?

Paul Spiegel Over a year ago

@user3714932 i tested the following code on 5.2.39:

$subQuery = DB::table('categories')->where('name', $catName)->select('id'); $products = App\Product::join('category_product as cp', 'cp.product_id', '=', 'products.id')->whereIn('category_id', $subQuery)->get();

. And it executes the following working query:

select * from `products` inner join `category_product` as `cp` on `cp`.`product_id` = `products`.`id` where `category_id` in (select `id` from `categories` where `name` = ?)

.

user3714932 Over a year ago

I'm using 5.0, maybe that's why

Paul Spiegel Over a year ago

@user3714932 You're right - it's not supported before v5.2 :-( (just checked in laravels source code).

IzzEps · Accepted Answer · 2016-10-09 16:25:10Z

0

your first query is returning a collection, not the grade_id.

Try this instead: $grade_id = DB::table('grades')->where('teacher_id',$teacher_id)->pluck('grade_id');

answered Oct 9, 2016 at 16:25

IzzEps

5826 silver badges20 bronze badges

2 Comments

Paul Spiegel Over a year ago

Collection isn't the problem. The problem is that it contains objects, while whereIn() needs an array/collection of simple values. But your code seems to be correct.

user3714932 Over a year ago

When I use pluck only one result (the first one) is returned.

user3714932 · Accepted Answer · 2016-10-09 20:43:01Z

0

Using lists worked. $grade_id = Grade::where('teacher_id', $teacher_id)->lists('grade_id'); They return an array instead of a collection

answered Oct 9, 2016 at 20:43

user3714932

1,4042 gold badges19 silver badges32 bronze badges

Comments

Adam Kozlowski · Accepted Answer · 2017-10-16 12:40:09Z

0

You need to create the array correctly. To do this use two functions that Eloquent work with: pluck() and toArray(). Look at example below:

$grade_id = DB::table('grades')->where('teacher_id',$teacher_id)->pluck('grade_id')->toArray();

answered Oct 16, 2017 at 12:40

Adam Kozlowski

5,9064 gold badges37 silver badges55 bronze badges

Collectives™ on Stack Overflow

Laravel 5 Eloquent ORM select where - array as parameter

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related