How to show correct filtering values and deal with addBaseClause()

[mariovillani]

Hello,
i have defined some base clauses in my setupListOperation() method and struggling with some wrong results.
I'm dealing with a Order model:

    protected function setupListOperation()
    {
        if (backpack_user()->hasRole('Reseller')) {
            CRUD::addBaseClause('whereNotIn', 'order_status', [1,2]);
            CRUD::addBaseClause('whereIn', 'created_by', backpack_user()->children()->pluck('id')); //show only my children's orders
            CRUD::addBaseClause('orWhere', 'created_by', backpack_user()->id); //show orders created by me
            CRUD::addBaseClause('orWhere', 'customer_id', backpack_user()->id_company); //show orders that belong to my company
            CRUD::addBaseClause('orWhere', 'created_by_company', backpack_user()->id_company); //show orders created by my company
        }
        ... other stuff ...

I've defined some custom filters, i take one as example which is responsible for filtering the orders of Type 1 and Type 2

       CRUD::filter('type')
           ->type('select2')
           ->values(function () {
               return [
                   '0' => "Type 0",
                   '1' => "Type 1",
               ];
           })
           ->whenActive(function ($value) {
               CRUD::addClause('where', 'type', $value);
           })->label("Order Type");
      ... other stuff and filters...

I cannot figure out how to do a correct query and i'm getting totally random results since i cannot really control the queries. I've checked the query from Telescope and understood the behaviour:

     select
       count(*) as total_rows
     from
       (
         select
           `orders`.`id`
         from
           `orders`
         where
           (
             `order_status` not in (1, 2) <-- Coming from whereNotIn condition
             and 0 = 1 <-- Coming from whereIn condition (relationship is null)
             or `created_by` = 15 <-- first orWhere condition
             or `customer_id` = 9 <-- second orWhere condition
             or `created_by_company` = 9 <-- third orWhere condition
             and `type` = '0' <-- !!!!! Filter query !!!!!
           )
           and `orders`.`deleted_at` is null
       ) as `orders_aggregator`

What i need to achieve is adding an "and" condition OUTSIDE the where base clause, my expected output would be:

     select
       count(*) as total_rows
     from
       (
         select
           `orders`.`id`
         from
           `orders`
         where
           (
             `order_status` not in (1, 2) <-- Coming from whereNotIn condition
             and 0 = 1 <-- Coming from whereIn condition (relationship is null)
             or `created_by` = 15 <-- first orWhere condition
             or `customer_id` = 9 <-- second orWhere condition
             or `created_by_company` = 9 <-- third orWhere condition
           )
           and `type` = '0' <-- !! Filter query !!
           and `orders`.`deleted_at` is null
       ) as `orders_aggregator`

I would like to apply the same behaviour for all filters of my OrderCRUDControllers.
Any ideas on how to achieve that? Documentation seems lacking and i'm relying on setupList operation to define what every user can see depending on his permissions.
I've thought about writing a Scope but i would slow down other controllers.
Hope i was clear, thank you in advance.

[karandatwani92]

Try building nested query . Something similar to this:

    $this->crud->addClause(function ($query) {
        $query->where(function ($query) {
            $query->whereNotIn('order_status', [1, 2]) // whereNotIn condition
                  ->whereIn('id', []); // this will always return false, effectively `0 = 1`
        })
        ->orWhere('created_by', backpack_user()->id) // show orders created by me
        ->orWhere('customer_id', backpack_user()->id_company) // show orders that belong to my company
        ->orWhere(function ($query) {
            $query->where('created_by_company', backpack_user()->id_company) // show orders created by my company
        });
    });

[mariovillani]

Thanks. I'll let you know if this solves my issue. In the meantime, i would suggest to update the referenced part of the documentation since it is not much clear on how to use nested queries. The example you gave me could be used to enrich that doc part.

[mariovillani]

Seems working!

if (backpack_user()->hasRole('Reseller')) {
    $this->crud->addClause(function ($query) {
        $query->where(function ($query) {
            $query->whereNotIn('order_status', [1,2])
                ->whereIn('created_by', backpack_user()->children()->pluck('id'))
                ->orWhere('created_by', backpack_user()->id)
                ->orWhere('customer_id', backpack_user()->id_company) 
                ->orWhere('created_by_company', backpack_user()->id_company);
            });
    });
}

produces:

select
  count(*) as total_rows
from
  (
    select
      `orders`.`id`
    from
      `orders`
    where
      (
        `order_status` not in (1, 2)
        and 0 = 1
        or `created_by` = 15
        or `customer_id` = 9
        or `created_by_company` = 9
      )
      and `type` = '0' <-- got via filter (not in the code shown in the previous block)
      and `orders`.`deleted_at` is null
  ) as `orders_aggregator`

Thank you very much.