I have created a custom index blade which return orders.
When loaded the data are filtered with the status of the order. status_id is equal to 2.
if($user_role_id == 5){
$orders->whereJsonContains('order_items.laboratory_id', ['1']);
$orders->whereIn('order_items.status_id', [2]);
$orders->whereNull('order_items.deleted_at');
} else if($user_role_id == 6){
$orders->whereJsonContains('order_items.laboratory_id', ['2']);
$orders->whereIn('order_items.status_id', [2]);
$orders->where('order_items.deleted_at', '=', NULL);
} else if ...
The data are loaded and displayed correctly.
When I search a term inside the returned rows and columns,
the data are returned accordingly (means only the order having the search term into one or several columns are returned)
but also the order with a status_id different of 2. You can see in the above picture the left dropdown in black is in fact an order with a status_id equal to 3 (which mean order processed).
I have tried several things :
...
$orders = $orders->whereIn('order_items.status_id', [2])->paginate(10);
...
$orders->where('order_items.status_id', [2]);
Would appreciate your expertise. Thanks.
Method
/**
* Display a listing of the orders filtered with status_id == 2
*
* @param \App\Http\Requests $request
* @return \Illuminate\View\View
*/
public function orderManagement(Request $request)
{
$staffs = $this->getStaffs();
$laboratory_staffs = $this->getLaboratorystaffs();
$status_orders = $this->getStatusorders();
$user_role_id = $this->getUserRoleId();
$term = $request->search_term;
$orders = DB::table('order_items');
$orders->leftJoin('order_status','order_items.status_id', '=', 'order_status.id');
$orders->leftJoin('users AS users_a','order_items.orderer_id', '=', 'users_a.id');
$orders->leftJoin('users AS users_b','order_items.customer_id', '=', 'users_b.id');
$orders->leftJoin('users AS users_c','order_items.owner_id', '=', 'users_c.id');
$orders->leftJoin('delivery_slots', 'order_items.delivery_slot_id', '=', 'delivery_slots.id');
$orders->leftJoin('products', 'order_items.product_id', '=', 'products.id');
$orders->leftJoin('product_categories', 'order_items.product_category_id', '=', 'product_categories.id');
$orders->leftJoin('cake_servings','order_items.cake_serving_id', '=', 'cake_servings.id');
$orders->leftJoin('tart_servings','order_items.tart_serving_id', '=', 'tart_servings.id');
$orders->leftJoin('thin_tart_servings','order_items.thin_tart_serving_id', '=', 'thin_tart_servings.id');
$orders->leftJoin('party_loaf_servings','order_items.party_loaf_serving_id', '=', 'party_loaf_servings.id');
$orders->leftJoin('party_loaf_weights','order_items.party_loaf_weight_id', '=', 'party_loaf_weights.id');
$orders->leftJoin('party_loaf_assortment_one','order_items.party_loaf_assortment_one_id', '=', 'party_loaf_assortment_one.id');
$orders->leftJoin('party_loaf_assortment_two','order_items.party_loaf_assortment_two_id', '=', 'party_loaf_assortment_two.id');
$orders->leftJoin('party_loaf_assortment_three','order_items.party_loaf_assortment_three_id', '=', 'party_loaf_assortment_three.id');
$orders->leftJoin('party_loaf_assortment_four','order_items.party_loaf_assortment_four_id', '=', 'party_loaf_assortment_four.id');
$orders->leftJoin('savoury_canapes_variants','order_items.savoury_canapes_variant_id', '=', 'savoury_canapes_variants.id');
$orders->leftJoin('savoury_mini_croissants_variants','order_items.savoury_mini_croissants_variant_id', '=', 'savoury_mini_croissants_variants.id');
$orders->leftJoin('savoury_mini_delices_variants','order_items.savoury_mini_delices_variant_id', '=', 'savoury_mini_delices_variants.id');
$orders->leftJoin('savoury_mini_puffs_variants','order_items.savoury_mini_puffs_variant_id', '=', 'savoury_mini_puffs_variants.id');
$orders->leftJoin('savoury_mini_ramequins_variants','order_items.savoury_mini_ramequins_variant_id', '=', 'savoury_mini_ramequins_variants.id');
$orders->leftJoin('savoury_mini_vols_au_vent_variants','order_items.savoury_mini_vols_au_vent_variant_id', '=', 'savoury_mini_vols_au_vent_variants.id');
$orders->leftJoin('savoury_petit_pain_bouchee_variants','order_items.savoury_petit_pain_bouchee_variant_id', '=', 'savoury_petit_pain_bouchee_variants.id');
$orders->leftJoin('sweet_sweets','sweet_sweet_id', '=', 'sweet_sweets.id');
$orders->leftJoin('ice_cream_small_pots_flavors','ice_cream_small_pots_flavor_id', '=', 'ice_cream_small_pots_flavors.id');
$orders->leftJoin('ice_cream_pots_flavors','ice_cream_pots_flavor_id', '=', 'ice_cream_pots_flavors.id');
$orders->leftJoin('ice_cream_frozen_fruits','ice_cream_frozen_fruit_id', '=', 'ice_cream_frozen_fruits.id');
$orders->leftJoin('yul_log_christmas_servings','yul_log_christmas_serving_id', '=', 'yul_log_christmas_servings.id');
$orders->leftJoin('yul_log_christmas_flavor','yul_log_christmas_flavor_id', '=', 'yul_log_christmas_flavor.id');
$orders->leftJoin('yul_log_frozen_liters','yul_log_frozen_liter_id', '=', 'yul_log_frozen_liters.id');
$orders->leftJoin('yul_log_frozen_flavor_one','yul_log_frozen_flavor_one_id', '=', 'yul_log_frozen_flavor_one.id');
$orders->leftJoin('yul_log_frozen_flavor_two','yul_log_frozen_flavor_two_id', '=', 'yul_log_frozen_flavor_two.id');
$orders->leftJoin('yul_log_frozen_flavor_three','yul_log_frozen_flavor_three_id', '=', 'yul_log_frozen_flavor_three.id');
$orders->select(
'order_items.id as id',
'order_items.product_category_id as product_category_id',
'product_categories.name as product_category_name',
'order_items.order_id as order_id',
'order_items.status_id as status_id',
'order_status.status_order as status_order',
'order_items.orderer_id as orderer_id',
'order_items.customer_id as customer_id',
'users_a.name as orderer_name',
'users_b.name as customer_name',
'users_b.mobile AS customer_mobile',
'order_items.owner_id as owner_id',
'users_c.name as owner_name',
'order_items.delivery_date as delivery_date',
'delivery_slots.delivery_slot as delivery_time',
'products.id as product_id',
'products.name as product_name',
'order_items.quantity as quantity',
'on_tray as on_tray',
'cut_bread as cut_bread',
'cake_servings.servings as cake_serving',
'tart_servings.servings as tart_serving',
'thin_tart_servings.servings as thin_tart_serving',
'party_loaf_servings.servings as party_loaf_serving',
'party_loaf_weights.weight as party_loaf_weight',
'party_loaf_assortment_one.assortment as party_loaf_assortment_one',
'party_loaf_assortment_two.assortment as party_loaf_assortment_two',
'party_loaf_assortment_three.assortment as party_loaf_assortment_three',
'party_loaf_assortment_four.assortment as party_loaf_assortment_four',
'savoury_canapes_variants.variant as canape_variant',
'savoury_mini_delices_variants.variant as mini_delice_variant',
'savoury_mini_croissants_variants.variant as mini_croissant_variant',
'savoury_mini_puffs_variants.variant as mini_puff_variant',
'savoury_mini_ramequins_variants.variant as mini_ramequin_variant',
'savoury_mini_vols_au_vent_variants.variant as mini_vol_au_vent_variant',
'savoury_petit_pain_bouchee_variants.variant as petit_pain_bouchee_variant',
'sweet_sweets.variant as sweet_variant',
'ice_cream_small_pots_flavors.flavor as ice_cream_small_pot_flavor',
'ice_cream_pots_flavors.flavor as ice_cream_pot_flavor',
'ice_cream_frozen_fruits.fruit as ice_cream_frozen_fruit',
'custom_laboratories as custom_laboratories',
'custom_name as custom_name',
'custom_servings as custom_servings',
'custom_weights as custom_weight',
'custom_size as custom_size',
'yul_log_christmas_servings.servings as yul_log_christmas_serving',
'yul_log_christmas_flavor.flavor as yul_log_christmas_flavor',
'yul_log_frozen_liters.liter as yul_log_frozen_liter',
'yul_log_frozen_flavor_one.flavor as yul_log_frozen_flavor_one',
'yul_log_frozen_flavor_two.flavor as yul_log_frozen_flavor_two',
'yul_log_frozen_flavor_three.flavor as yul_log_frozen_flavor_three',
'inscription as inscription',
'comment as comment',
'order_items.deleted_at as deleted_at',
);
if($user_role_id == 5){
$orders->whereJsonContains('order_items.laboratory_id', ['1']);
$orders->whereIn('order_items.status_id', [2]);
$orders->whereNull('order_items.deleted_at');
} else if($user_role_id == 6){
$orders->whereJsonContains('order_items.laboratory_id', ['2']);
$orders->whereIn('order_items.status_id', [2]);
$orders->where('order_items.deleted_at', '=', NULL);
} else if($user_role_id == 7){
$orders->whereJsonContains('order_items.laboratory_id', ['3']);
$orders->whereIn('order_items.status_id', [2]);
$orders->where('order_items.deleted_at', '=', NULL);
} else if($user_role_id == 8){
$orders->whereJsonContains('order_items.laboratory_id', ['4']);
$orders->whereIn('order_items.status_id', [2]);
$orders->where('order_items.deleted_at', '=', NULL);
} else if($user_role_id == 9){
$orders->whereJsonContains('order_items.laboratory_id', ['5']);
$orders->whereIn('order_items.status_id', [2]);
$orders->where('order_items.deleted_at', '=', NULL);
} else if($user_role_id == 10){
$orders->whereJsonContains('order_items.laboratory_id', ['6']);
$orders->whereIn('order_items.status_id', [2]);
$orders->where('order_items.deleted_at', '=', NULL);
}
if($request->search_term!="") {
$orders->where('order_items.id', 'LIKE', "%" . $term . "%")
->orWhere('order_items.order_id', 'LIKE', "%" . $term . "%")
->orWhere('users_a.name', 'LIKE', "%" . $term . "%")
->orWhere('users_b.name', 'LIKE', "%" . $term . "%")
->orWhere('users_b.mobile', 'LIKE', "%" . $term . "%")
->orWhere('order_items.delivery_date', 'LIKE', "%" . $term . "%")
->orWhere('product_categories.name', 'LIKE', "%" . $term . "%")
->orWhere('products.name', 'LIKE', "%" . $term . "%")
->orWhere('custom_name', 'LIKE', "%" . $term . "%")
->orWhere('inscription', 'LIKE', "%" . $term . "%")
->orWhere('savoury_canapes_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('savoury_mini_croissants_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('savoury_mini_delices_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('savoury_mini_puffs_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('savoury_mini_ramequins_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('savoury_mini_vols_au_vent_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('savoury_petit_pain_bouchee_variants.variant', 'LIKE', "%" . $term . "%")
->orWhere('ice_cream_small_pots_flavors.flavor', 'LIKE', "%" . $term . "%")
->orWhere('ice_cream_pots_flavors.flavor', 'LIKE', "%" . $term . "%")
->orWhere('ice_cream_frozen_fruits.fruit', 'LIKE', "%" . $term . "%")
->orWhere('party_loaf_servings.servings', 'LIKE', "%" . $term . "%")
->orWhere('party_loaf_assortment_one.assortment', 'LIKE', "%" . $term . "%")
->orWhere('party_loaf_assortment_two.assortment', 'LIKE', "%" . $term . "%")
->orWhere('party_loaf_assortment_three.assortment', 'LIKE', "%" . $term . "%")
->orWhere('party_loaf_assortment_four.assortment', 'LIKE', "%" . $term . "%")
->orWhere('custom_servings', 'LIKE', "%" . $term . "%")
->orWhere('custom_weights', 'LIKE', "%" . $term . "%")
->orWhere('custom_size', 'LIKE', "%" . $term . "%")
->orWhere('yul_log_christmas_flavor.flavor', 'LIKE', "%" . $term . "%")
->orWhere('yul_log_frozen_flavor_one.flavor', 'LIKE', "%" . $term . "%")
->orWhere('yul_log_frozen_flavor_two.flavor', 'LIKE', "%" . $term . "%")
->orWhere('yul_log_frozen_flavor_three.flavor', 'LIKE', "%" . $term . "%")
->orWhere('inscription', 'LIKE', "%" . $term . "%")
->orWhere('comment', 'LIKE', "%" . $term . "%");
}
$orders->where('order_items.deleted_at', '=', NULL);
$orders->distinct();
$orders->orderBy('order_items.delivery_date', 'asc');
$orders = $orders->whereIn('order_items.status_id', [2])->paginate(10);
## START CREATE THE VIEW
// return view('orders.management', compact('laboratory_staffs', 'staffs','orders','status_orders', 'user_role_id'));
return view('orders.management-second', compact('laboratory_staffs', 'staffs','orders','status_orders', 'user_role_id'));
## END CREATE THE VIEW
}
CodePudding user response：
Your query conditions are not organized correctly. Try the following:
if($request->search_term != "") {
$orders->where(function ($query) use ($term) {
$query->orWhere('order_items.id', 'LIKE', "%" . $term . "%")
->orWhere('order_items.order_id', 'LIKE', "%" . $term . "%")
// ...
->orWhere('comment', 'LIKE', "%" . $term . "%");
});
}