Laravel where with 2 tables

Question

I'm trying to check the another table to remove the matches from the results but unable to figure this out.

$value = people::select(array('people.blog_id'))    
         ->join('blocks', 'people.user_id', '=', 'blocks.blocker')
         ->where('people.user_id', $user->id)
         ->where('blocks.blocker',  '!=', 'people.user_id')
         ->get()
         ->toArray();

What I am trying to achieve, is to strip away the results when getting user_id from people where blocker is found as well in the blocks table, but the following returns an empty array.

Instead of `->where('blocks.blocker', '!=', 'people.user_id')` you need something like `->where('blocks.blocker', '=', 'null')`. https://stackoverflow.com/a/4076157/3585500. I don't know the exact eloquent syntax, but maybe `whereNull('blocks.blocker')` function? — ourmandave, Dec 25 '18 at 02:48
what do want exactly from your query? , i mean , tell for example 'i want people who hasn't blocked , or ...) and tell me your table structure. — Saman Ahmadi, Dec 25 '18 at 06:55

Erfan Ahmed · Accepted Answer · 2018-12-25T10:31:17.727

As per laravel doc

You may use the table method on the DB facade to begin a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get method.

Change your query statement like bellow-

$articles = DB::table('people')
            ->join('blocks', 'people.user_id', '=', 'blocks.blocker')
            ->where('blocks.blocker', '<>', 'people.user_id')
            ->select('people.blog_id')
            ->get();

Raj · Answer 2 · 2018-12-25T06:58:15.997

I think you should use right join here instead of simple join, You can do it like.

$value = people::select(array('people.blog_id'))
    ->join('blocks', 'people.user_id', '=', 'blocks.blocker', 'right')
    ->where('people.user_id', $user->id)
    ->get()->toArray();

Please notice the fourth parameter in the join statement, this will include only the results where blocks will find. Hope this will help

Laravel where with 2 tables

2 Answers2