How to Get All Orders of Customers Assigned to a Specific Agent Using Query Builder

I would look at using eloquent for that, and eloquent has all of the query Builder methods.

@jlrdw Thanks but I tried Eloquent but didn't couldn't get anywhere. I only came far using Query builder.

I don't know your data, but first look at reducing the query as much as possible,

->whereIn('customer_tags.id', [234])
                      ->whereNotIn('customer_tags.id', [3343, 445, 333]);

if all you need is 234, can't you just select that. Do you really need whereNotIn.

Also do you have a good query visual designer. Maybe a query you need might be better suited for the db facade.

A good example : https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Or consider learning eloquent better and learn eloquent relations.

@jlrdw Please re-read the full details of the question. I did provide the data structure and what I'm trying to achieve. I do know a few things about Eloquent but this is more complicated than writing two 'where' statements.

I need to write a SQL statement that get all Orders of Customers who are assigned to the current logged in user (by agent_id) and has this tag (ID: 234) but not any of these tags [3343, 445, 333] and has purchased any of these product (IDs: 18, 33, 29).

It's a must that I include orders of contacts that have a certain tag but don't have certain tags. One contact can have all of the listed tags listed in the have and don't have tags.

Bear in mind, a complex query can take time and trial and error.

Try other things, like:

->whereBetween('customer_tags.id', [233, 235])

Just start redoing small things in the query one at a time as needed. No one just whips a query like that out the first try.

I cannot figure out why the whereIn / whereNotIn combo isn't working.

Sometimes the order of statements matter: https://stackoverflow.com/questions/27919603/laravel-wherenotin-and-where-not-working-together-together

Can you try the following (It is not test, since i haven't got the code):

App\Order::whereHas('customers', function (Builder $query) {
    $query->where('agent_id', Auth::id());
})
->whereHas('customers.tags', function (Builder $query) {
    $query->where('id', 243);
})
->whereDoesntHave('customers.tags', function (Builder $query) {
    $query->whereNotIn('id', [3343, 445, 333]);
})
->whereHas('products', function (Builder $query) {
    $query->whereIn('id', [18, 33, 29]);
})
->get();

@design_studio thanks for the respond. Unfortunately, it's not working since whereDoesntHave() can not filter out the customers with the unable tags in whereNotIn since whereDoesHave is only loading customers who don't have tags at all. https://laravel.com/docs/5.8/eloquent-relationships#querying-relationship-absence

You are right. I think it could actually work with whereHas. Like this:

App\Order::whereHas('customers', function (Builder $query) {
    $query->where('agent_id', Auth::id());
})
->whereHas('customers.tags', function (Builder $query) {
    $query->where('id', 243)->whereNotIn('id', [3343, 445, 333]);
})
->whereHas('products', function (Builder $query) {
    $query->whereIn('id', [18, 33, 29]);
})
->get();

I am not sure this will result in what you want since i think this will not give you customers without tags at all. for that maybe you need to add statement that adds customers with no tags at all.

May be like this:

App\Order::whereHas('customers', function (Builder $query) {
    $query->where('agent_id', Auth::id());
})
->whereHas('customers.tags', function (Builder $query) {
    $query->where('id', 243)->whereNotIn('id', [3343, 445, 333]);
})
->doesntHave('customers.tags', 'or')
->whereHas('products', function (Builder $query) {
    $query->whereIn('id', [18, 33, 29]);
})
->get();