How to check for a string against json encode array in a table in Laravel?

Question

I am having an issue with a Laravel project. I have a few checkbox of computer specification which I want to check against the database field that I am having called tech_specs. This tech_specs field contains the serialize array of all the tech_specs that are available for that specific product. I just want to list out the products having that particular specification. One more thing I have divided the products into 3 levels of categories and currently all the products are placed within the 3rd level category. I am trying to check for the specs of the particular 3rd level category based on the url parameter. Please let me know how to proceed.

Controller Function

public function GetPage(Request $request,$page_slug){

        $page_title=self::replaceAll($page_slug);
        $category_level1_id=null;
        $category_level2_id=null;
        $category_level3_id=null;
        $products=null;
        $products_count=null;
        $product_lookup=null;
        $page_breadcrumb=null;
        $product_type=null;
        $brand=null;
        $final_selected_brand=array();
        $tech_specs_filter=null;
        $specs_filters=null;

        //Sorting Vars
        $sort_by=$request->query('sort_by');
        $min_price=$request->query('min_price');
        $max_price=$request->query('max_price');

        //Filter Vars
        $product_condition=$request->query('product_condition');
        $product_brand=$request->query('brand');
        $is_assured=$request->query('assured');
        $is_cod_allowed=$request->query('cod');
        $product_specs=$request->query('tech_spec');


        $final_selected_brand=null;
        if(!empty($product_brand)){
            $final_selected_brand=explode('|',$product_brand);
        }

        $final_selected_brand_ids=null;
        if(!empty($final_selected_brand)){
            foreach($final_selected_brand as $fsb){
                $final_selected_brand_ids[]=self::GetBrandIDFromName($fsb);
            }
        }

        $final_selected_spec=null;
        if(!empty($product_specs)){
            $final_selected_spec=explode('|',$product_specs);
        }

        // Get the categories
        $getCatlvl1 = Category::whereCategorySlug($page_slug);
        $getCatlvl2 = SubCategory::whereCategorySlug($page_slug);
        $getCatlvl3 = SubCategoryLvl3::whereCategorySlug($page_slug);


        //Check if is category level 1
        if ($getCatlvl1->count()) {
            $category_level1_id = $getCatlvl1->pluck('id')->toArray();
            $product_type = $getCatlvl1->pluck('product_type')->toArray()[0];
            $category_level1_name = $getCatlvl1->pluck('category_name')->toArray()[0];

            $brand=Brand::where('product_type',$product_type)->orderBy('brand_name')->get();

            $category_level2_id = SubCategory::where('parent_category', $category_level1_id)
                ->pluck('id')
                ->toArray();

            $category_level3_id = SubCategoryLvl3::whereIn('parent_category', $category_level2_id)
                ->pluck('id')
                ->toArray();

            $product_lookup = DB::table('products')
                                ->where('is_approved','Yes')
                                ->where('category_level',3)
                                ->whereIn('category',$category_level3_id)
                                ->when($product_condition, function($query) use($product_condition){
                                    $query->where('product_condition', $product_condition);
                                })
                                ->when($is_assured, function($query) use($is_assured){
                                    $query->where('is_assured','Yes');
                                })
                                ->when($is_cod_allowed, function($query) use($is_cod_allowed){
                                    $query->where('merchant_allowed_for_cod','Yes');
                                })
                                ->when($final_selected_brand_ids, function($query) use($final_selected_brand_ids) {
                                    if(count($final_selected_brand_ids)>1){
                                        $query->whereIn('brand',$final_selected_brand_ids);
                                    } else {
                                        $query->where('brand',$final_selected_brand_ids[0]);
                                    }
                                })
                                ->when($min_price and $max_price, function($query) use($min_price, $max_price) {
                                    if($min_price=='+50000'){
                                        $query->where('selling_price','>=',$min_price);
                                        $query->where('selling_price',$max_price);
                                    } else if($max_price=='+50000'){
                                        $query->where('selling_price',$min_price);
                                        $query->where('selling_price','>=',$max_price);
                                    } else {
                                        $query->whereBetween('selling_price', [
                                            $min_price,
                                            $max_price
                                        ]);
                                    }
                                })
                                ->when($sort_by, function($query) use($sort_by) {
                                    if($sort_by=='high-price'){
                                        $query->orderBy('selling_price', 'DESC');
                                    }
                                    if($sort_by=='low-price'){
                                        $query->orderBy('selling_price', 'ASC');
                                    }
                                    if($sort_by=='new-arrivals'){
                                        $query->orderBy('id', 'DESC');
                                    }
                                });

            $page_breadcrumb.= '<li class="breadcrumb-item active" aria-current="page">'.$category_level1_name.'</li>';
        }

        //Check if is category level 2
        if ($getCatlvl2->count()) {

            $category_level2_id = $getCatlvl2->pluck('id')->toArray();
            $product_type = $getCatlvl2->pluck('product_type')->toArray()[0];
            $category_level2_name = $getCatlvl2->pluck('category_name')->toArray()[0];

            $brand=Brand::where('product_type',$product_type)->orderBy('brand_name')->get();

            $parent_id = $getCatlvl2->pluck('parent_category')->toArray()[0];
            $get_cat_parent_id = Category::where('id',$parent_id)->get()->toArray()[0];
            $category_level1_name = $get_cat_parent_id['category_name'];
            $category_level1_link = $get_cat_parent_id['category_slug'];

            $category_level3_id = SubCategoryLvl3::whereIn('parent_category', $category_level2_id)
                ->pluck('id')
                ->toArray();

            $product_lookup = DB::table('products')
                ->where('is_approved','Yes')
                ->where('category_level',3)
                ->whereIn('category',$category_level3_id)
                ->when($product_condition, function($query) use($product_condition){
                    $query->where('product_condition', $product_condition);
                })
                ->when($is_assured, function($query) use($is_assured){
                    $query->where('is_assured','Yes');
                })
                ->when($is_cod_allowed, function($query) use($is_cod_allowed){
                    $query->where('merchant_allowed_for_cod','Yes');
                })
                ->when($final_selected_brand_ids, function($query) use($final_selected_brand_ids) {
                    if(count($final_selected_brand_ids)>1){
                        $query->whereIn('brand',$final_selected_brand_ids);
                    } else {
                        $query->where('brand',$final_selected_brand_ids[0]);
                    }
                })
                ->when($min_price and $max_price, function($query) use($min_price, $max_price) {
                    if($min_price=='+50000'){
                        $query->where('selling_price','>=',$min_price);
                        $query->where('selling_price',$max_price);
                    } else if($max_price=='+50000'){
                        $query->where('selling_price',$min_price);
                        $query->where('selling_price','>=',$max_price);
                    } else {
                        $query->whereBetween('selling_price', [
                            $min_price,
                            $max_price
                        ]);
                    }
                })
                ->when($sort_by, function($query) use($sort_by) {
                    if($sort_by=='high-price'){
                        $query->orderBy('selling_price', 'DESC');
                    }
                    if($sort_by=='low-price'){
                        $query->orderBy('selling_price', 'ASC');
                    }
                    if($sort_by=='new-arrivals'){
                        $query->orderBy('id', 'DESC');
                    }
                });

            $page_breadcrumb.= '<li class="breadcrumb-item"><a href="'.url('/').'/'.$category_level1_link.'">'.$category_level1_name.'</a></li>';
            $page_breadcrumb.= '<li class="breadcrumb-item active">'.$category_level2_name.'</li>';

        }

        //Check if is category level 3
        if ($getCatlvl3->count()) {
            $category_level3_id = $getCatlvl3->pluck('id')->toArray();
            $product_type = $getCatlvl3->pluck('product_type')->toArray()[0];
            $category_level3_name = $getCatlvl3->pluck('category_name')->toArray()[0];

            $brand=Brand::where('product_type',$product_type)->orderBy('brand_name')->get();

            $matchThese=["product_type"=>$product_type,"category"=>$category_level3_id,"category_level"=>3];
            $tech_specs_filter=TechSpecsFilter::where($matchThese)->get()->toArray();

            if(!empty($tech_specs_filter)){
                $temp=$tech_specs_filter[0];
                $specs_filters=json_decode($temp['filter_specs']);
            }

            $parent_id_lvl2 = $getCatlvl3->pluck('parent_category')->toArray()[0];
            $get_cat_parent_id_lvl2 = SubCategory::where('id',$parent_id_lvl2)->get()->toArray()[0];

            $category_level2_name = $get_cat_parent_id_lvl2['category_name'];
            $category_level2_link = $get_cat_parent_id_lvl2['category_slug'];

            $get_cat_parent_id = Category::where('id',$get_cat_parent_id_lvl2['parent_category'])->get()->toArray()[0];
            $category_level1_name = $get_cat_parent_id['category_name'];
            $category_level1_link = $get_cat_parent_id['category_slug'];

            $product_lookup = DB::table('products')
                ->where('is_approved','Yes')
                ->where('category_level',3)
                ->whereIn('category',$category_level3_id)
                ->when($product_condition, function($query) use($product_condition){
                    $query->where('product_condition', $product_condition);
                })
                ->when($is_assured, function($query) use($is_assured){
                    $query->where('is_assured','Yes');
                })
                ->when($is_cod_allowed, function($query) use($is_cod_allowed){
                    $query->where('merchant_allowed_for_cod','Yes');
                })
                ->when($final_selected_brand_ids, function($query) use($final_selected_brand_ids) {
                    if(count($final_selected_brand_ids)>1){
                        $query->whereIn('brand',$final_selected_brand_ids);
                    } else {
                        $query->where('brand',$final_selected_brand_ids[0]);
                    }
                })
                /*->when($final_selected_spec, function($query) use($final_selected_spec) {
                    if(count($final_selected_spec)>1){
                        //$query->whereIn('brand',$final_selected_spec);
                    } else {
                        $query->where('brand',$final_selected_spec[0]);
                    }
                })*/
                ->when($min_price and $max_price, function($query) use($min_price, $max_price) {
                    if($min_price=='+50000'){
                        $query->where('selling_price','>=',$min_price);
                        $query->where('selling_price',$max_price);
                    } else if($max_price=='+50000'){
                        $query->where('selling_price',$min_price);
                        $query->where('selling_price','>=',$max_price);
                    } else {
                        $query->whereBetween('selling_price', [
                            $min_price,
                            $max_price
                        ]);
                    }
                })
                ->when($sort_by, function($query) use($sort_by) {
                    if($sort_by=='high-price'){
                        $query->orderBy('selling_price', 'DESC');
                    }
                    if($sort_by=='low-price'){
                        $query->orderBy('selling_price', 'ASC');
                    }
                    if($sort_by=='new-arrivals'){
                        $query->orderBy('id', 'DESC');
                    }
                });

            $page_breadcrumb.= '<li class="breadcrumb-item"><a href="'.url('/').'/'.$category_level1_link.'">'.$category_level1_name.'</a></li>';
            $page_breadcrumb.= '<li class="breadcrumb-item"><a href="'.url('/').'/'.$category_level2_link.'">'.$category_level2_name.'</a></li>';
            $page_breadcrumb.= '<li class="breadcrumb-item active">'.$category_level3_name.'</li>';
        }

        $temp_products =  $product_lookup->get();

        if(count($temp_products)>0){
            $products_count = $product_lookup->get()->count();
            $products = $product_lookup->paginate(50);
        }

        //Category Tree
        $content='<ul class="category-page-tree">';
        $get_category_tree_cat_level1 = Category::orderBy('category_name')->get();
        foreach($get_category_tree_cat_level1 as $gct1){
            $content.='<li>';
                $content.='<a href="'.url('/').'/'.$gct1->category_slug.'" class="level1_cat_color"><i class="fas fa-angle-right"></i> '.$gct1->category_name.'</a>';
                $get_category_tree_cat_level2 = SubCategory::orderBy('category_name')
                    ->where('parent_category',$gct1->id)
                    ->get();
                $content.='<ul>';
                    foreach($get_category_tree_cat_level2 as $gct2){
                        $content.='<li>';
                            $content.='<a href="'.url('/').'/'.$gct2->category_slug.'" class="level2_cat_color"><i class="fas fa-angle-right"></i> '.$gct2->category_name.'</a>';
                            $get_category_tree_cat_level3 = SubCategoryLvl3::orderBy('category_name')
                                ->where('parent_category',$gct2->id)
                                ->get();
                            $content.='<ul>';
                            foreach($get_category_tree_cat_level3 as $gct3){
                                $content.='<li>';
                                $content.='<a href="'.url('/').'/'.$gct3->category_slug.'" class="level3_cat_color"><i class="fas fa-angle-right"></i> '.$gct3->category_name.'</a>';
                                $content.='</li>';
                            }
                            $content.='</ul>';
                        $content.='</li>';
                    }
                $content.='</ul>';
            $content.='</li>';
        }
        $content.='</ul>';


        return view("frontend.pages.category_page")->with(
            array(
                'products'=>$products,
                'products_count'=>$products_count,
                'page_slug'=>$page_slug,
                'page_breadcrumb'=>$page_breadcrumb,
                'product_type'=>$product_type,
                'brands'=>$brand,
                'page_title'=>$page_title,
                'category_tree'=>$content,
                'category_level_column'=>2,
                'min_price'=>$min_price,
                'max_price'=>$max_price,
                'selected_brand'=>(array)$final_selected_brand,
                'is_assured'=>$is_assured,
                'is_cod_allowed'=>$is_cod_allowed,
                'specs_filters'=>$specs_filters,
                'selected_specs'=>(array)$final_selected_spec,
            )
        );
    }

jQuery Code

$(document).on("click", ".tech_specs_filter_box input[type='checkbox']", function(e) {
        var selected_specs=$(this).val();
        var current_url=window.location.href;
        var page_slug=$("#page_slug").val();
        var url_parameters = [];
        var specs_append = [];

        //Parameters
        var product_condition = getParam('product_condition');
        var sort_by = getParam('sort_by');
        var min_price = getParam('min_price');
        var max_price = getParam('max_price');
        var brand = getParam('brand');
        var tech_spec = getParam('tech_spec');
        var to_add_quesion_mark='no';

        if($(this).prop("checked") == true){
            if(current_url.indexOf('?tech_spec') == -1){
                url_parameters.push('?tech_spec='+selected_specs);
            } else {
                if(current_url.indexOf('?tech_spec') !== -1){
                    specs_append.push('?tech_spec='+tech_spec);
                    specs_append.push('|'+selected_specs);
                    url_parameters.push(specs_append.join(""));
                } else if(current_url.indexOf('&tech_spec') !== -1){
                    specs_append.push('&tech_spec='+tech_spec);
                    specs_append.push('|'+selected_specs);
                    url_parameters.push(specs_append.join(""));
                } else if(current_url.indexOf('&tech_spec') !== -1) {
                    url_parameters.push('&tech_spec='+selected_specs);
                }
            }
        } else if($(this).prop("checked") == false) {

            var specsArr = [];
            $('.tech_specs_filter_box input:checked').each(function(index) {
                var temp_val=$(this).attr('id');
                specsArr.push($("#"+temp_val).val());
            });
            var getSpecStr = specsArr.join("|");

            if(getSpecStr!==""){
                if(current_url.indexOf('?tech_spec') !== -1){
                    url_parameters.push('?tech_spec='+getSpecStr);
                }
            } else {
                to_add_quesion_mark="yes";
            }
        }


        if(sort_by!==""){
            url_parameters.push('sort_by='+sort_by);
        }
        if(product_condition!==""){
            url_parameters.push('product_condition='+product_condition);
        }
        if(min_price!==""){
            url_parameters.push('min_price='+min_price);
        }
        if(max_price!==""){
            url_parameters.push('max_price='+max_price);
        }
        if(brand!==""){
            url_parameters.push('brand='+brand);
        }

        if(to_add_quesion_mark=='yes'){
            for(var i=0;i<url_parameters.length;i++){
                if(i==0) url_parameters[i] = '?'+url_parameters[i];
            }
        }

        target_location = url_parameters.join("&");
        window.location = page_slug+target_location;

    });

HTML View

Abhay Maurya · Accepted Answer · 2019-01-30 13:31:10Z

1

I am not sure if I understand the question right but seems like you have a field in your DB, let's call it "tech_specs" which contains "serialize array" and you want to check against it when someone checks a spec in your front end.

Well, can't you use simple "LIKE" in order to check against it?

Something like:

$user_input_spec = $request->input('spec');
$result = Model::where('tech_specs', 'LIKE', '%'.$user_input_spec.'%')->get();

Shouldn't that work?

answered Jan 30, 2019 at 13:31

Abhay Maurya

12.3k9 gold badges50 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nitin Johnson Over a year ago

@Leaner yes you are right i want it that way only. Let me try out your suggesstion

Collectives™ on Stack Overflow

How to check for a string against json encode array in a table in Laravel?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related