Laravel Query builder table join

Question

I've a requirement to convert this to Laravel Query Builder:

Raw SQL:

Select * from requests where user_id in (select user_id from profiles where total_sales <= 10);

This is my Query builder:

public static function apply(Builder $builder, $value)
{
 return $builder->where('user_id', function($query) use ($value) {
        $query->where('profiles.total_sales','<=',$value);
    });
}

This is not working. What am I missing?

PS: This is a portion of large dynamic query and this is the portion that is not working so, I'm posting only this portion of the query.

what is not working? do you get error message? anyway, you need sub-query, your example is just adding 2 **where** conditions — ljubadr, Oct 24 '17 at 15:59
maybe something like [this](https://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in) could point you in the right direction — ljubadr, Oct 24 '17 at 16:02
@ljubadr, yes, I get error: QueryException SQLSTATE[42601]: Syntax error: 7 ERROR: SELECT * with no tables specified is not valid LINE 1: ...regate from "requests" where "user_id" in (select * where "p... ^ (SQL: select count(*) as aggregate from "requests" where "user_id" in (select * where "profiles"."total_sales" <= 5)) — chuck, Oct 24 '17 at 16:12

score 0 · Accepted Answer · answered Oct 24 '17 at 16:55

I created the query with help from link provided by @ljubadr :

public static function apply(Builder $builder, $value){ return $builder->whereIn('user_id', function($query){
            $query->select('user_id')
                ->from(with(new UserProfile)->getTable())
                ->where('profiles.total_sale','<', 1);
        });

}

Thank you @ljubadr.

Laravel Query builder table join

1 Answers1