Show users not in table

what do u wants to know

@NIPUN - I stated what I want to know in my post

I need to provide list of users who have items listed but never sold any. Tables are connected like this

Query for not sold and get related user. Without a not_sold field, it could be where date = null, since only sold items have a date, and order by user_id. A join would do the trick similar to the query from a few days ago.

are you expecting the query

@jirdw How do you query for not sold? Like... how do you query for something that isn't there?

I have this but it's definitely not right

$list = User::query()
            ->select('fname as fname','sname as sname','mobile as mobile', 'email as email', 'users.id as id', 'um.id as meal_id')->distinct()
            ->leftJoin('user_meals as um', 'um.id', '=', 'users.id')
            ->leftJoin('shift_meals as sm', 'sm.meal_id', '=', 'um.id')
            ->where('users.membertype','=','cook')
            ->whereNull('sm.meal_id')
            ->get();

user_meals is items and shift_meals is items_sold

This is pretty easy if you're using Eloquent relationships. You could just use doesntHave() or whereDoesntHave() and get the users that don't have sales.

https://laravel.com/docs/5.7/eloquent-relationships#querying-relationship-absence

@cronix - ok I've decided to go with your suggestion as it's cleaner in my mind. The query now looks like this

$list = Meal::query()
            ->whereDoesntHave('shiftmeals')
            ->get();

However this is returning users who may have three of four items listed and sold 2 and not sold 2.

I need it to only return users who have sold nothing at all. It's bursting my head.

so... for example this is returning 2 instances of "John" if John has 2 items that haven't sold, even if he has 3 items that did.

That's hard to answer as you don't show any relationship definitions, but I don't think you're using whereDoesntHave properly. That should use a callback to specify the criteria to use as the where portion.

I'm thinking I need to do a

hasManyThrough

and query on Users

For anyone interested I solved this by setting up a hasManyThrough relationship from "users" to "item_sold"

public function shiftmeals()
    {
        return $this->hasManyThrough(
            'App\ShiftMeal', //orderitem
            'App\Meal', //order
            'user_id', // Foreign key on Meal table... foreign from users to Meal
            'meal_id', // Foreign key on ShiftMeal table... foreighn from ShiftMeal to Meal
            'id', // Local key on user table... local for users to Meal
            'id'); // Local key on Meal table... local for Meal to ShiftMeal
    }

Also a hasMany relation from users to Meals

public function meals()
    {
        return $this->hasMany('App\Meal', 'user_id', 'id');
    }

and then a very simple eloquent query did the trick

$list = User::query()
            ->whereHas('meals')
            ->whereDoesntHave('shiftmeals')
            ->get();

@mick79 Beautiful and very easy to read syntax. Nice job!

@MICK79 - Could you show how you are looping over in view? Nicely documented answer.

Thank you @cronix and @jirdw

Here is the loop inside the blade:

 @foreach ($list as $item)
        <tr>
            <td>{{$item->fname}} {{$item->sname}}</td>
            <td>{{$item->email}}</td>
            <td>{{$item->mobile}}</td>
            <td>
                @foreach ($item->meals as $element)
                     {{$element->meal_name}}<br>
                @endforeach
            </td>
        </tr>
    @endforeach

This is showing the details of the user and the items they have listed but never sold.