Skip to content
The Chat Archive
gpt-4.1-mini-2025-04-14· OpenAI WildChat-4.8M (AI2)

請優化以下程式碼: function fetchproduct($userid = NULL, $filter = NULL, $id = NULL, $categoryid…

  1. User

    請優化以下程式碼: function fetch_product(userid=NULL,user_id = NULL, filter = NULL, id=NULL,id = NULL, category_id = NULL, limit=NULL,limit = NULL, offset = NULL, sort=NULL,sort = NULL, order = NULL, returncount=NULL,return_count = NULL, is_deliverable = NULL, $seller_id = NULL) {

    $settings = get_settings('system_settings', true);
    $low_stock_limit = isset($settings['low_stock_limit']) ? $settings['low_stock_limit'] : 5;
    $t = &get_instance();
    
    if ($sort == 'pv.price' && !empty($sort) && $sort != NULL) {
    
        // $t->db->order_by("IF( pv.special_price > 0 , pv.special_price , pv.price )" . $order, False);
        $t->db->order_by("IF(pv.special_price > 0,
             IF(p.is_prices_inclusive_tax = 1,
                  pv.special_price,
                  pv.special_price + ((pv.special_price * p.tax )/100)
             ),
            IF(p.is_prices_inclusive_tax = 1,
                 pv.price,
                 pv.price  + ((pv.price * p.tax )/100)
            )
               ) " . $order, false);
    }
    
    
    if (isset($filter['show_only_active_products']) && $filter['show_only_active_products'] == 0) {
        $where = [];
    } else {
        $where = ['p.status' => '1', 'pv.status' => 1, 'sd.status' => 1];
    }
    
    $discount_filter_data = (isset($filter['discount']) && !empty($filter['discount'])) ? ' pv.*,( if(pv.special_price > 0,( (pv.price-pv.special_price)/pv.price)*100,0)) as cal_discount_percentage, ' : '';
    
    $t->db->select($discount_filter_data . ' (select count(id)  from products where products.category_id=c.id ) as total,count(p.id) as sales, p.stock_type ,
     p.is_prices_inclusive_tax, p.type ,GROUP_CONCAT(DISTINCT(pa.attribute_value_ids)) as attr_value_ids,sd.rating as seller_rating,sd.slug as seller_slug,sd.no_of_ratings as seller_no_of_ratings,sd.logo as seller_profile, sd.store_name as store_name,sd.store_description, p.seller_id, u.username as seller_name,
     p.id,p.stock,p.name,p.category_id,p.short_description,p.slug,p.description,p.extra_description,p.total_allowed_quantity,p.status,p.deliverable_type,p.is_attachment_required,p.deliverable_zipcodes,p.minimum_order_quantity,p.sku,
     p.quantity_step_size,p.cod_allowed,p.row_order,p.rating,p.no_of_ratings,p.image,p.is_returnable,p.is_cancelable,p.cancelable_till,p.indicator,p.other_images, 
     p.video_type, p.video, p.tags, p.warranty_period, p.guarantee_period, p.made_in,p.hsn_code,p.download_allowed,p.download_type,p.download_link,p.pickup_location,p.brand,p.availability, p.slug as product_slug, b.slug as brand_slug,c.name as category_name,tax.percentage as tax_percentage ,tax.id as tax_id ')
        ->join(" categories c", "p.category_id=c.id ", 'LEFT')
        ->join(" brands b", "p.brand=b.name", 'LEFT')
        ->join(" seller_data sd", "p.seller_id=sd.user_id ", 'LEFT')
        ->join(" users u", "p.seller_id=u.id", 'LEFT')
        ->join('`product_variants` pv', 'p.id = pv.product_id', 'LEFT')
        ->join('`taxes` tax', 'tax.id = p.tax', 'LEFT')
        ->join('`product_attributes` pa', ' pa.product_id = p.id ', 'LEFT');
    
    
    if (isset($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
        $t->db->where('(p.stock != "" or pv.stock != "")');
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
        $t->db->join('`order_items` oi', 'oi.product_variant_id = pv.id', 'LEFT');
        $sort = 'count(p.id)';
        $order = 'DESC';
    }
    
    if (isset($filter) && !empty($filter['search'])) {
        $tags = explode(" ", $filter['search']);
        $t->db->group_Start();
        foreach ($tags as $i => $tag) {
            if ($i == 0) {
                $t->db->like('p.tags', trim($tag));
            } else {
                $t->db->or_like('p.tags', trim($tag));
            }
        }
        $t->db->or_like('p.name', trim($filter['search']));
        $t->db->group_end();
    }
    if (isset($filter) && !empty($filter['flag']) && $filter['flag'] != "null" && $filter['flag'] != "") {
        $flag = $filter['flag'];
        if ($flag == 'low') {
            $t->db->group_Start();
            $where1 = "p.stock_type is  NOT NULL";
            $t->db->where($where1);
            $t->db->where('p.stock <=', $low_stock_limit);
            $t->db->where('p.availability =', '1');
            $t->db->or_where('pv.stock <=', $low_stock_limit);
            $t->db->where('pv.availability =', '1');
            $t->db->group_End();
        } else {
            $t->db->group_Start();
            $t->db->or_where('p.availability ', '0');
            $t->db->or_where('pv.availability ', '0');
            $t->db->where('p.stock ', '0');
            $t->db->or_where('pv.stock ', '0');
            $t->db->group_End();
        }
    }
    
    
    
    if (isset($filter['min_rating']) && is_numeric($filter['min_rating'])) {
        $min_rating = floatval($filter['min_rating']);
        $t->db->group_start();
        $t->db->where('p.rating >=', $min_rating);
        $t->db->group_end();
    }
    if (isset($filter['max_rating']) && is_numeric($filter['max_rating'])) {
        $max_rating = floatval($filter['max_rating']);
        $t->db->group_start();
        $t->db->where('p.rating <=', $max_rating);
        $t->db->group_end();
    }
    
    if (isset($filter) && !empty($filter['tags'])) {
        $tags = explode(",", $filter['tags']);
        $t->db->group_Start();
        foreach ($tags as $i => $tag) {
            if ($i == 0) {
                $t->db->like('p.tags', trim($tag));
            } else {
                $t->db->or_like('p.tags', trim($tag));
            }
        }
        $t->db->group_end();
    }
    
    if (isset($filter) && !empty($filter['brand'])) {
        $t->db->where('p.brand', trim($filter['brand']));
    }
    
    if (isset($filter) && !empty($filter['slug'])) {
        $where['p.slug'] = $filter['slug'];
    }
    if (isset($seller_id) && !empty($seller_id) && $seller_id != "") {
        $where['p.seller_id'] = $seller_id;
    }
    
    
    if (isset($filter) && !empty($filter['attribute_value_ids'])) {
        /* https://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string */
        $str = str_replace(',', '|', $filter['attribute_value_ids']); //str_replace(find,replace,string,count)
        //$str = $filter['attribute_value_ids'];
        $t->db->where('CONCAT(",", pa.attribute_value_ids , ",") REGEXP ",(' . $str . ')," !=', 0, false);
    }
    //print_r($filter);
    //print_r($category_id);
    
    if(isset($filter) && !empty($filter['categories']) && empty($category_id)){
        $str = str_replace(',', '|', $filter['categories']);
        $t->db->where('CONCAT(",", p.category_id , ",") REGEXP ",(' . $str . ')," !=', 0, false);
    }
    
    if (isset($category_id) && !empty($category_id) && empty($filter['categories'])) {
        if (is_array($category_id) && !empty($category_id)) {
            $t->db->group_Start();
            $t->db->where_in('p.category_id', $category_id);
            //$t->db->like('p.category_id', $category_id);
            $t->db->or_where_in('c.parent_id', $category_id);
            $t->db->group_End();
            $t->db->where($where);
        } else {
            $where['p.category_id'] = $category_id;
        }
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'products_on_sale') {
        $t->db->where('pv.special_price >', '0');
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'top_rated_products') {
        $sort = null;
        $order = null;
        $t->db->order_by("p.rating", "desc");
        $t->db->order_by("p.no_of_ratings", "desc");
        $where = ['p.no_of_ratings > ' => 0];
    }
    
    
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'top_rated_product_including_all_products') {
        $sort = null;
        $order = null;
        $t->db->order_by("p.rating", "desc");
        $t->db->order_by("p.no_of_ratings", "desc");
    }
    
    if (isset($filter) && !empty($filter['product_type']) && $filter['product_type'] == 'new_added_products') {
        $sort = 'p.id';
        $order = 'desc';
    }
    
    if (isset($filter) && !empty($filter['product_variant_ids'])) {
        if (is_array($filter['product_variant_ids'])) {
            $t->db->where_in('pv.id', $filter['product_variant_ids']);
        }
    }
    
    if (isset($id) && !empty($id) && $id != null) {
        if (is_array($id) && !empty($id)) {
            $t->db->where_in('p.id', $id);
            $t->db->where($where);
        } else {
            if (isset($filter) && !empty($filter['is_similar_products']) && $filter['is_similar_products'] == '1') {
                $where[' p.id != '] = $id;
            } else {
                $where['p.id'] = $id;
            }
            $t->db->where($where);
        }
    } else {
        $t->db->where($where);
    }
    if (!isset($filter['flag']) && empty($filter['flag'])) {
        $t->db->group_Start();
        $t->db->or_where('c.status', '1');
        $t->db->or_where('c.status', '0');
        $t->db->group_End();
    }
    if (isset($filter['discount']) && !empty($filter['discount']) && $filter['discount'] != "") {
        $discount_pr = $filter['discount'];
        $t->db->group_by('p.id')->having("cal_discount_percentage  <= " . $discount_pr, null, false)->having("cal_discount_percentage  > 0 ", null, false);
    } else {
        $t->db->group_by('p.id');
    }
    
    
    if (!empty($return_count)) {
        return $t->db->count_all_results('products p');
    } else {
        $product = $t->db->get('products p')->result_array();
    }
    
    $min_price = 0;
    $max_price = 0;
    for ($i = 0; $i < count($product); $i++) {
        $product[$i]['min_max_price'] = get_min_max_price_of_product($product[$i]['id']);
        if($product[$i]['min_max_price']['special_price'] < $min_price || $min_price == 0){
            if($product[$i]['min_max_price']['special_price'] != 0){
                $min_price = $product[$i]['min_max_price']['special_price'];
            }
        }
    
        if($product[$i]['min_max_price']['max_price'] > $max_price || $max_price == 0){
            if($product[$i]['min_max_price']['max_price'] != 0){
                $max_price = $product[$i]['min_max_price']['max_price'];
            }
        }
    }
    
    if ($sort == 'pv.price' && !empty($sort) && $sort != NULL) {
    
        // $t->db->order_by("IF( pv.special_price > 0 , pv.special_price , pv.price )" . $order, False);
        $t->db->order_by("IF(pv.special_price > 0,
             IF(p.is_prices_inclusive_tax = 1,
                  pv.special_price,
                  pv.special_price + ((pv.special_price * p.tax )/100)
             ),
            IF(p.is_prices_inclusive_tax = 1,
                 pv.price,
                 pv.price  + ((pv.price * p.tax )/100)
            )
               ) " . $order, false);
    }
    
    
    if (isset($filter['show_only_active_products']) && $filter['show_only_active_products'] == 0) {
        $where = [];
    } else {
        $where = ['p.status' => '1', 'pv.status' => 1, 'sd.status' => 1];
    }
    
    $discount_filter_data = (isset($filter['discount']) && !empty($filter['discount'])) ? ' pv.*,( if(pv.special_price > 0,( (pv.price-pv.special_price)/pv.price)*100,0)) as cal_discount_percentage, ' : '';
    
    $t->db->select($discount_filter_data . ' (select count(id)  from products where products.category_id=c.id ) as total,count(p.id) as sales, p.stock_type ,
     p.is_prices_inclusive_tax, p.type ,GROUP_CONCAT(DISTINCT(pa.attribute_value_ids)) as attr_value_ids,sd.rating as seller_rating,sd.slug as seller_slug,sd.no_of_ratings as seller_no_of_ratings,sd.logo as seller_profile, sd.store_name as store_name,sd.store_description, p.seller_id, u.username as seller_name,
     p.id,p.stock,p.name,p.category_id,p.short_description,p.slug,p.description,p.extra_description,p.total_allowed_quantity,p.status,p.deliverable_type,p.is_attachment_required,p.deliverable_zipcodes,p.minimum_order_quantity,p.sku,
     p.quantity_step_size,p.cod_allowed,p.row_order,p.rating,p.no_of_ratings,p.image,p.is_returnable,p.is_cancelable,p.cancelable_till,p.indicator,p.other_images, 
     p.video_type, p.video, p.tags, p.warranty_period, p.guarantee_period, p.made_in,p.hsn_code,p.download_allowed,p.download_type,p.download_link,p.pickup_location,p.brand,p.availability, p.slug as product_slug, b.slug as brand_slug,c.name as category_name,tax.percentage as tax_percentage ,tax.id as tax_id ')
        ->join(" categories c", "p.category_id=c.id ", 'LEFT')
        ->join(" brands b", "p.brand=b.name", 'LEFT')
        ->join(" seller_data sd", "p.seller_id=sd.user_id ", 'LEFT')
        ->join(" users u", "p.seller_id=u.id", 'LEFT')
        ->join('`product_variants` pv', 'p.id = pv.product_id', 'LEFT')
        ->join('`taxes` tax', 'tax.id = p.tax', 'LEFT')
        ->join('`product_attributes` pa', ' pa.product_id = p.id ', 'LEFT');
    
    
    if (isset($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
        $t->db->where('(p.stock != "" or pv.stock != "")');
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
        $t->db->join('`order_items` oi', 'oi.product_variant_id = pv.id', 'LEFT');
        $sort = 'count(p.id)';
        $order = 'DESC';
    }
    
    if (isset($filter) && !empty($filter['search'])) {
        $tags = explode(" ", $filter['search']);
        $t->db->group_Start();
        foreach ($tags as $i => $tag) {
            if ($i == 0) {
                $t->db->like('p.tags', trim($tag));
            } else {
                $t->db->or_like('p.tags', trim($tag));
            }
        }
        $t->db->or_like('p.name', trim($filter['search']));
        $t->db->group_end();
    }
    if (isset($filter) && !empty($filter['flag']) && $filter['flag'] != "null" && $filter['flag'] != "") {
        $flag = $filter['flag'];
        if ($flag == 'low') {
            $t->db->group_Start();
            $where1 = "p.stock_type is  NOT NULL";
            $t->db->where($where1);
            $t->db->where('p.stock <=', $low_stock_limit);
            $t->db->where('p.availability =', '1');
            $t->db->or_where('pv.stock <=', $low_stock_limit);
            $t->db->where('pv.availability =', '1');
            $t->db->group_End();
        } else {
            $t->db->group_Start();
            $t->db->or_where('p.availability ', '0');
            $t->db->or_where('pv.availability ', '0');
            $t->db->where('p.stock ', '0');
            $t->db->or_where('pv.stock ', '0');
            $t->db->group_End();
        }
    }
    
    
    
    if (isset($filter['min_rating']) && is_numeric($filter['min_rating'])) {
        $min_rating = floatval($filter['min_rating']);
        $t->db->group_start();
        $t->db->where('p.rating >=', $min_rating);
        $t->db->group_end();
    }
    if (isset($filter['max_rating']) && is_numeric($filter['max_rating'])) {
        $max_rating = floatval($filter['max_rating']);
        $t->db->group_start();
        $t->db->where('p.rating <=', $max_rating);
        $t->db->group_end();
    }
    
    if (isset($filter) && !empty($filter['tags'])) {
        $tags = explode(",", $filter['tags']);
        $t->db->group_Start();
        foreach ($tags as $i => $tag) {
            if ($i == 0) {
                $t->db->like('p.tags', trim($tag));
            } else {
                $t->db->or_like('p.tags', trim($tag));
            }
        }
        $t->db->group_end();
    }
    
    if (isset($filter) && !empty($filter['brand'])) {
        $t->db->where('p.brand', trim($filter['brand']));
    }
    
    if (isset($filter) && !empty($filter['slug'])) {
        $where['p.slug'] = $filter['slug'];
    }
    if (isset($seller_id) && !empty($seller_id) && $seller_id != "") {
        $where['p.seller_id'] = $seller_id;
    }
    
    
    if (isset($filter) && !empty($filter['attribute_value_ids'])) {
        /* https://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string */
        $str = str_replace(',', '|', $filter['attribute_value_ids']); //str_replace(find,replace,string,count)
        //$str = $filter['attribute_value_ids'];
        $t->db->where('CONCAT(",", pa.attribute_value_ids , ",") REGEXP ",(' . $str . ')," !=', 0, false);
    }
    //print_r($filter);
    //print_r($category_id);
    
    if(isset($filter) && !empty($filter['categories']) && empty($category_id)){
        $str = str_replace(',', '|', $filter['categories']);
        $t->db->where('CONCAT(",", p.category_id , ",") REGEXP ",(' . $str . ')," !=', 0, false);
    }
    
    if (isset($category_id) && !empty($category_id) && empty($filter['categories'])) {
        if (is_array($category_id) && !empty($category_id)) {
            $t->db->group_Start();
            $t->db->where_in('p.category_id', $category_id);
            //$t->db->like('p.category_id', $category_id);
            $t->db->or_where_in('c.parent_id', $category_id);
            $t->db->group_End();
            $t->db->where($where);
        } else {
            $where['p.category_id'] = $category_id;
        }
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'products_on_sale') {
        $t->db->where('pv.special_price >', '0');
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'top_rated_products') {
        $sort = null;
        $order = null;
        $t->db->order_by("p.rating", "desc");
        $t->db->order_by("p.no_of_ratings", "desc");
        $where = ['p.no_of_ratings > ' => 0];
    }
    
    
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'top_rated_product_including_all_products') {
        $sort = null;
        $order = null;
        $t->db->order_by("p.rating", "desc");
        $t->db->order_by("p.no_of_ratings", "desc");
    }
    
    if (isset($filter) && !empty($filter['product_type']) && $filter['product_type'] == 'new_added_products') {
        $sort = 'p.id';
        $order = 'desc';
    }
    
    if (isset($filter) && !empty($filter['product_variant_ids'])) {
        if (is_array($filter['product_variant_ids'])) {
            $t->db->where_in('pv.id', $filter['product_variant_ids']);
        }
    }
    
    if (isset($id) && !empty($id) && $id != null) {
        if (is_array($id) && !empty($id)) {
            $t->db->where_in('p.id', $id);
            $t->db->where($where);
        } else {
            if (isset($filter) && !empty($filter['is_similar_products']) && $filter['is_similar_products'] == '1') {
                $where[' p.id != '] = $id;
            } else {
                $where['p.id'] = $id;
            }
            $t->db->where($where);
        }
    } else {
        $t->db->where($where);
    }
    if (!isset($filter['flag']) && empty($filter['flag'])) {
        $t->db->group_Start();
        $t->db->or_where('c.status', '1');
        $t->db->or_where('c.status', '0');
        $t->db->group_End();
    }
    if (isset($filter['discount']) && !empty($filter['discount']) && $filter['discount'] != "") {
        $discount_pr = $filter['discount'];
        $t->db->group_by('p.id')->having("cal_discount_percentage  <= " . $discount_pr, null, false)->having("cal_discount_percentage  > 0 ", null, false);
    } else {
        $t->db->group_by('p.id');
    }
    
    
    if (isset($filter['min_price']) && $filter['min_price'] >= 0) {
        $fmin_price = $filter['min_price'];
        $where_min = "if( pv.special_price > 0 , pv.special_price , pv.price ) >=$fmin_price";
        $t->db->group_Start();
        $t->db->where($where_min);
        $t->db->group_End();
    }
    if (isset($filter['max_price']) && $filter['max_price'] > 0 && isset($filter['min_price']) && $filter['min_price'] >= 0) {
        $fmax_price = $filter['max_price'];
        $where_max = "if( pv.special_price > 0 , pv.special_price , pv.price ) <=$fmax_price";
        $t->db->group_Start();
        $t->db->where($where_max);
        $t->db->group_End();
    }
    
    
    
    if ($limit != null || $offset != null) {
        $t->db->limit($limit, $offset);
    }
    if (isset($filter['discount']) && !empty($filter['discount']) && $filter['discount'] != "") {
        $t->db->order_by('cal_discount_percentage', 'DESC');
    } else {
        if ($sort != null || $order != null && $sort != 'pv.price') {
            $t->db->order_by($sort, $order);
        }
        $t->db->order_by('p.row_order', 'ASC');
    }
    
    
    if (!empty($return_count)) {
        return $t->db->count_all_results('products p');
    } else {
        $product = $t->db->get('products p')->result_array();
    }
    
    
    
    $count = isset($filter) && !empty($filter['flag']) ? 'count(DISTINCT(p.id))' : 'count(DISTINCT(p.id))';
    $discount_filter = (isset($filter['discount']) && !empty($filter['discount'])) ? ' , GROUP_CONCAT( IF( ( IF( pv.special_price > 0, ((pv.price - pv.special_price) / pv.price) * 100, 0 ) ) > ' . $filter['discount'] . ', ( IF( pv.special_price > 0, ((pv.price - pv.special_price) / pv.price) * 100, 0 ) ), 0 ) ) AS cal_discount_percentage ' : '';
    $product_count = $t->db->select('count(DISTINCT(p.id)) as total , GROUP_CONCAT(pa.attribute_value_ids) as attr_value_ids' . $discount_filter)
        ->join(" categories c", "p.category_id=c.id ", 'LEFT')
        ->join(" seller_data sd", "p.seller_id=sd.user_id ")
        ->join('`product_variants` pv', 'p.id = pv.product_id', 'LEFT')
        ->join('`product_attributes` pa', ' pa.product_id = p.id ', 'LEFT');
    
    if (isset($filter) && !empty($filter['search'])) {
        $tags = explode(" ", $filter['search']);
        $t->db->group_Start();
        foreach ($tags as $i => $tag) {
            if ($i == 0) {
                $t->db->like('p.tags', trim($tag));
            } else {
                $t->db->or_like('p.tags', trim($tag));
            }
        }
        $product_count->or_like('p.name', $filter['search']);
        $t->db->group_End();
    }
    if (isset($filter) && !empty($filter['flag'])) {
        $flag = $filter['flag'];
        if ($flag == 'low') {
            $t->db->group_Start();
            $where1 = "p.stock_type is  NOT NULL";
            $t->db->where($where1);
            $t->db->where('p.stock <=', $low_stock_limit);
            $t->db->where('p.availability =', '1');
            $t->db->or_where('pv.stock <=', $low_stock_limit);
            $t->db->where('pv.availability =', '1');
            $t->db->group_End();
        } else {
            $t->db->group_Start();
            $t->db->or_where('p.availability ', '0');
            $t->db->or_where('pv.availability ', '0');
            $t->db->where('p.stock ', '0');
            $t->db->or_where('pv.stock ', '0');
            $t->db->group_End();
        }
    }
    
    if (isset($filter) && !empty($filter['tags'])) {
        $tags = explode(",", $filter['tags']);
        $t->db->group_Start();
        foreach ($tags as $i => $tag) {
            if ($i == 0) {
                $t->db->like('p.tags', trim($tag));
            } else {
                $t->db->or_like('p.tags', trim($tag));
            }
        }
        $t->db->group_End();
    }
    
    if (isset($filter) && !empty($filter['brand'])) {
        $t->db->where('p.brand', trim($filter['brand']));
    }
    
    if (isset($filter) && !empty($filter['attribute_value_ids'])) {
        $str = str_replace(',', '|', $filter['attribute_value_ids']); // Ids should be in string and comma separated 
        $product_count->where('CONCAT(",", pa.attribute_value_ids, ",") REGEXP ",(' . $str . ')," !=', 0, false);
    }
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
        $product_count->join('`order_items` oi', 'oi.product_variant_id = pv.id', 'LEFT');
    }
    if (isset($category_id) && !empty($category_id)) {
        if (is_array($category_id) && !empty($category_id)) {
            $product_count->where_in('p.category_id', $category_id);
            $product_count->or_where_in('c.parent_id', $category_id);
            $product_count->where($where);
        }
    }
    
    if (isset($filter) && !empty($filter['product_type']) && strtolower($filter['product_type']) == 'products_on_sale') {
        $product_count->where('pv.special_price >=', '0');
    }
    if (isset($id) && !empty($id) && $id != null) {
        if (is_array($id) && !empty($id)) {
            $product_count->where_in('p.id', $id);
        }
    }
    if (isset($seller_id) && !empty($seller_id) && $seller_id != "") {
        $where['p.seller_id'] = $seller_id;
    }
    if (isset($seller_id) && !empty($seller_id) && $seller_id != "") {
        if (isset($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
            $t->db->where('(p.stock != "" or pv.stock != "")');
        }
    }
    if (isset($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
        $t->db->where('(p.stock != "" or pv.stock != "")');
    }
    $product_count->where($where);
    if (!isset($filter['flag']) && empty($filter['flag'])) {
        $product_count->group_Start();
        $product_count->or_where('c.status', '1');
        $product_count->or_where('c.status', '0');
        $product_count->group_End();
    }
    
    
    $count_res = $product_count->get('products p')->result_array();
    
    // return;
    $attribute_values_ids = array();
    $temp = [];
    
    
    
    
    if (!empty($product)) {
        $t->load->model('rating_model');
        for ($i = 0; $i < count($product); $i++) {
            
            $rating = $t->rating_model->fetch_rating($product[$i]['id'], '', 8, 0, 'pr.id', 'desc', '', 1);
            $product[$i]['review_images'] = (!empty($rating)) ? [$rating] : array();
    
            $product[$i]['tax_percentage'] = (isset($product[$i]['tax_percentage']) && intval($product[$i]['tax_percentage']) > 0) ? $product[$i]['tax_percentage'] : '0';
            $product[$i]['tax_id'] = ((isset($product[$i]['tax_id']) && intval($product[$i]['tax_id']) > 0) && $product[$i]['tax_id'] != "") ? $product[$i]['tax_id'] : '0';
            $product[$i]['attributes'] = get_attribute_values_by_pid($product[$i]['id']);
            // print_r($product[$i]['attributes']);
            // die;
            $product[$i]['variants'] = get_variants_values_by_pid($product[$i]['id']);
            $variants =   get_variants_values_by_pid($product[$i]['id']);
            $total_stock = 0;
            foreach ($variants as $variant) {
                $stock = (isset($variant['stock']) && !empty($variant['stock'])) ? $variant['stock'] : 0;
                $total_stock  += $stock;
                $product[$i]['total_stock'] = isset($total_stock) && !empty($total_stock) ? $total_stock : '';
            }
            $product[$i]['min_max_price'] = get_min_max_price_of_product($product[$i]['id']);
            $product[$i]['stock_type'] = isset($product[$i]['stock_type']) && ($product[$i]['stock_type'] != '') ? $product[$i]['stock_type'] : '';
            $product[$i]['stock'] = isset($product[$i]['stock']) && !empty($product[$i]['stock']) ? $product[$i]['stock'] : '';
            $product[$i]['relative_path'] = isset($product[$i]['image']) && !empty($product[$i]['image']) ? $product[$i]['image'] : '';
            $product[$i]['other_images_relative_path'] = isset($product[$i]['other_images']) && !empty($product[$i]['other_images']) ? json_decode($product[$i]['other_images']) : [];
            $product[$i]['video_relative_path'] = (isset($product[$i]['video']) && (!empty($product[$i]['video']))) ? $product[$i]['video'] : "";
            $product[$i]['video_type'] = isset($product[$i]['video_type']) && !empty($product[$i]['video_type']) ? $product[$i]['video_type'] : '';
            $product[$i]['attr_value_ids'] = isset($product[$i]['attr_value_ids']) && !empty($product[$i]['attr_value_ids']) ? $product[$i]['attr_value_ids'] : '';
            $product[$i]['made_in'] = isset($product[$i]['made_in']) && !empty($product[$i]['made_in']) ? $product[$i]['made_in'] : '';
            $product[$i]['hsn_code'] = isset($product[$i]['hsn_code']) && !empty($product[$i]['hsn_code']) ? $product[$i]['hsn_code'] : '';
            $product[$i]['brand'] = isset($product[$i]['brand']) && !empty($product[$i]['brand']) ? $product[$i]['brand'] : '';
    
            
            // echo "<pre>";
            // print_r($product[$i]);
            // die;
    
            $product[$i]['warranty_period'] = isset($product[$i]['warranty_period']) && !empty($product[$i]['warranty_period']) ? $product[$i]['warranty_period'] : '';
            $product[$i]['guarantee_period'] = isset($product[$i]['guarantee_period']) && !empty($product[$i]['guarantee_period']) ? $product[$i]['guarantee_period'] : '';
            $product[$i]['total_allowed_quantity'] = isset($product[$i]['total_allowed_quantity']) && !empty($product[$i]['total_allowed_quantity']) ? $product[$i]['total_allowed_quantity'] : '';
            $product[$i]['download_allowed'] = isset($product[$i]['download_allowed']) && !empty($product[$i]['download_allowed']) ? $product[$i]['download_allowed'] : '';
            $product[$i]['download_type'] = isset($product[$i]['download_type']) && !empty($product[$i]['download_type']) ? $product[$i]['download_type'] : '';
            $product[$i]['download_link'] = isset($product[$i]['download_link']) && !empty($product[$i]['download_link']) ? $product[$i]['download_link'] : '';
            $product[$i]['status'] = isset($product[$i]['status']) && !empty($product[$i]['status']) ? $product[$i]['status'] : '';
            $total_product = $t->db->query("select count(id) as total  from products where products.seller_id=" . $product[$i]['seller_id'] . " AND products.status='1'")->result_array();
    
            /* outputing escaped data */
            $product[$i]['name'] = output_escaping($product[$i]['name']);
            $product[$i]['total_product'] = ($total_product[0]['total']);
            $product[$i]['store_name'] = output_escaping($product[$i]['store_name']);
            $product[$i]['seller_rating'] = (isset($product[$i]['seller_rating']) && !empty($product[$i]['seller_rating'])) ? output_escaping(number_format($product[$i]['seller_rating'], 1)) : 0;
            $product[$i]['store_description'] = (isset($product[$i]['store_description']) && !empty($product[$i]['store_description'])) ? output_escaping($product[$i]['store_description']) : "";
            $product[$i]['seller_profile'] = output_escaping(base_url() . $product[$i]['seller_profile']);
            $product[$i]['seller_name'] = output_escaping($product[$i]['seller_name']);
            $product[$i]['short_description'] = output_escaping($product[$i]['short_description']);
            $product[$i]['description'] = (isset($product[$i]['description']) && !empty($product[$i]['description'])) ? output_escaping($product[$i]['description']) : "";
            $product[$i]['extra_description'] = (isset($product[$i]['extra_description']) && !empty($product[$i]['extra_description']) && $product[$i]['extra_description'] != 'NULL') ? output_escaping($product[$i]['extra_description']) : "";
            $product[$i]['pickup_location'] = (isset($product[$i]['pickup_location']) && !empty($product[$i]['pickup_location']) && $product[$i]['pickup_location']) != 'NULL' ? $product[$i]['pickup_location'] : '';
    
            $product[$i]['seller_slug'] = isset($product[$i]['seller_slug']) && !empty($product[$i]['seller_slug']) ? output_escaping($product[$i]['seller_slug']) : "";
            $product[$i]['deliverable_type'] = $product[$i]['deliverable_type'];
            $product[$i]['deliverable_zipcodes_ids'] = output_escaping($product[$i]['deliverable_zipcodes']);
            if (isset($filter['discount']) && !empty($filter['discount']) && $filter['discount'] != "") {
                $product[$i]['cal_discount_percentage'] = output_escaping(number_format($product[$i]['cal_discount_percentage'], 2));
            }
            $product[$i]['cancelable_till'] = isset($product[$i]['cancelable_till']) && !empty($product[$i]['cancelable_till']) ? $product[$i]['cancelable_till'] : '';
            $product[$i]['is_attachment_required'] = isset($product[$i]['is_attachment_required']) && !empty($product[$i]['is_attachment_required']) ? $product[$i]['is_attachment_required'] : '0';
            $product[$i]['indicator'] = isset($product[$i]['indicator']) && !empty($product[$i]['indicator']) ? $product[$i]['indicator'] : '0';
            $product[$i]['deliverable_zipcodes_ids'] = isset($product[$i]['deliverable_zipcodes_ids']) && !empty($product[$i]['deliverable_zipcodes_ids']) ? $product[$i]['deliverable_zipcodes_ids'] : '';
            $product[$i]['rating'] = output_escaping(number_format($product[$i]['rating'], 2));
            $product[$i]['availability'] = isset($product[$i]['availability']) && ($product[$i]['availability'] != "") ? $product[$i]['availability'] : '';
            $product[$i]['sku'] = isset($product[$i]['sku']) && ($product[$i]['sku'] != "") ? $product[$i]['sku'] : '';
    
            /* getting zipcodes from ids */
            if ($product[$i]['deliverable_type'] != NONE && $product[$i]['deliverable_type'] != ALL) {
                $zipcodes = array();
                $zipcode_ids = explode(",", $product[$i]['deliverable_zipcodes_ids']);
                // $t->db->select('zipcode');
                // $t->db->where_in('id', $zipcode_ids);
                // $zipcodes = $t->db->get('zipcodes')->result_array();
                $zipcodes = array_column($zipcodes, "zipcode");
                $product[$i]['deliverable_zipcodes'] = implode(",", $zipcode_ids);
            } else {
                $product[$i]['deliverable_zipcodes'] = '';
            }
            $product[$i]['category_name'] = (isset($product[$i]['category_name']) && !empty($product[$i]['category_name'])) ? output_escaping($product[$i]['category_name']) : '';
            /* check product delivrable or not */
            if ($is_deliverable != NULL) {
                $zipcode = fetch_details('zipcodes', ['zipcode' => $is_deliverable], 'id');
                if (!empty($zipcode)) {
                    $product[$i]['is_deliverable'] = is_product_delivarable($type = 'zipcode', $zipcode[0]['id'], $product[$i]['id']);
                } else {
                    $product[$i]['is_deliverable'] = false;
                }
            } else {
                $product[$i]['is_deliverable'] = false;
            }
    
            if ($product[$i]['deliverable_type'] == 1) {
                $product[$i]['is_deliverable'] = true;
            }
    
    
            $product[$i]['tags'] = (!empty($product[$i]['tags'])) ? explode(",", $product[$i]['tags']) : [];
    
            $product[$i]['video'] = (isset($product[$i]['video_type']) && (!empty($product[$i]['video_type']) || $product[$i]['video_type'] != NULL)) ? (($product[$i]['video_type'] == 'youtube' || $product[$i]['video_type'] == 'vimeo') ? $product[$i]['video'] : base_url($product[$i]['video'])) : "";
            $product[$i]['minimum_order_quantity'] = isset($product[$i]['minimum_order_quantity']) && (!empty($product[$i]['minimum_order_quantity'])) ? $product[$i]['minimum_order_quantity'] : 1;
            $product[$i]['quantity_step_size'] = isset($product[$i]['quantity_step_size']) && (!empty($product[$i]['quantity_step_size'])) ? $product[$i]['quantity_step_size'] : 1;
            if (!empty($product[$i]['variants'])) {
                $count_stock = array();
                $is_purchased_count = array();
                for ($k = 0; $k < count($product[$i]['variants']); $k++) {
    
                    $variant_other_images = $variant_other_images_sm = $variant_other_images_md = json_decode((string)$product[$i]['variants'][$k]['images'], 1);
    
                    if (!empty($variant_other_images[0]) && isset($variant_other_images[0])) {
    
                        $product[$i]['variants'][$k]['variant_relative_path'] = isset($product[$i]['variants'][$k]['images']) && !empty($product[$i]['variants'][$k]['images']) ? json_decode($product[$i]['variants'][$k]['images']) : [];
                        $counter = 0;
                        foreach ($variant_other_images_md as $row) {
                            $variant_other_images_md[$counter] = get_image_url($variant_other_images_md[$counter], 'thumb', 'md');
                            $counter++;
                        }
                        $product[$i]['variants'][$k]['images_md'] = isset($variant_other_images_md) && !empty($variant_other_images_md) ? $variant_other_images_md : "";
    
                        $counter = 0;
                        foreach ($variant_other_images_sm as $row) {
                            $variant_other_images_sm[$counter] = get_image_url($variant_other_images_sm[$counter], 'thumb', 'sm');
                            $counter++;
                        }
                        $product[$i]['variants'][$k]['images_sm'] = $variant_other_images_sm;
    
                        $counter = 0;
                        foreach ($variant_other_images as $row) {
                            $variant_other_images[$counter] = get_image_url($variant_other_images[$counter]);
                            $counter++;
                        }
                        $product[$i]['variants'][$k]['images'] = isset($variant_other_images) && !empty($variant_other_images) ? $variant_other_images : "";
                    } else {
                        $product[$i]['variants'][$k]['images'] = array();
                        $product[$i]['variants'][$k]['images_md'] = array();
                        $product[$i]['variants'][$k]['images_sm'] = array();
                        $product[$i]['variants'][$k]['variant_relative_path'] = array();
                    }
                    $product[$i]['variants'][$k]['swatche_type'] = (!empty($product[$i]['variants'][$k]['swatche_type'])) ? $product[$i]['variants'][$k]['swatche_type'] : "0";
                    $product[$i]['variants'][$k]['swatche_value'] = (!empty($product[$i]['variants'][$k]['swatche_value'])) ? $product[$i]['variants'][$k]['swatche_value'] : "0";
                    if (($product[$i]['stock_type'] == 0  || $product[$i]['stock_type'] == null)) {
                        if ($product[$i]['availability'] != null) {
                            $product[$i]['variants'][$k]['availability'] = $product[$i]['availability'];
                        }
                    } else {
                        $product[$i]['variants'][$k]['availability'] = ($product[$i]['variants'][$k]['availability'] != null) ? $product[$i]['variants'][$k]['availability'] : 1;
                        array_push($count_stock, $product[$i]['variants'][$k]['availability']);
                    }
                    if (($product[$i]['stock_type'] == 0)) {
                        $product[$i]['variants'][$k]['stock'] = isset($product[$i]['variants'][$k]['stock']) && !empty($product[$i]['variants'][$k]['stock']) ? get_stock($product[$i]['id'], 'product') : '';
                    } else {
                        $product[$i]['variants'][$k]['stock'] = isset($product[$i]['variants'][$k]['stock']) && !empty($product[$i]['variants'][$k]['stock']) ? get_stock($product[$i]['variants'][$k]['id'], 'variant') : '';
                    }
                    $percentage = (isset($product[$i]['tax_percentage']) && intval($product[$i]['tax_percentage']) > 0 && $product[$i]['tax_percentage'] != null) ? $product[$i]['tax_percentage'] : '0';
                    if ((isset($product[$i]['is_prices_inclusive_tax']) && $product[$i]['is_prices_inclusive_tax'] == 0) || (!isset($product[$i]['is_prices_inclusive_tax'])) && $percentage > 0) {
                        $price_tax_amount = $product[$i]['variants'][$k]['price'] * ($percentage / 100);
                        $product[$i]['variants'][$k]['price'] =  strval($product[$i]['variants'][$k]['price'] + $price_tax_amount);
                        $special_price_tax_amount = $product[$i]['variants'][$k]['special_price'] * ($percentage / 100);
                        $product[$i]['variants'][$k]['special_price'] =  strval($product[$i]['variants'][$k]['special_price'] + $special_price_tax_amount);
                    } else {
                        $product[$i]['variants'][$k]['price'] =  strval($product[$i]['variants'][$k]['price']);
                        $product[$i]['variants'][$k]['special_price'] =  strval($product[$i]['variants'][$k]['special_price']);
                    }
                    if (isset($user_id) && $user_id != NULL) {
                        $user_cart_data = $t->db->select('qty as cart_count')->where(['product_variant_id' => $product[$i]['variants'][$k]['id'], 'user_id' => $user_id, 'is_saved_for_later' => 0])->get('cart')->result_array();
                        if (!empty($user_cart_data)) {
                            $product[$i]['variants'][$k]['cart_count'] = $user_cart_data[0]['cart_count'];
                        } else {
                            $product[$i]['variants'][$k]['cart_count'] = "0";
                        }
                        $is_purchased = $t->db->where(['oi.product_variant_id' => $product[$i]['variants'][$k]['id'], 'oi.user_id' => $user_id, 'oi.active_status' => "delivered"])->order_by('oi.id', 'DESC')->limit(1)->get('order_items oi')->result_array();
    
                        if (!empty($is_purchased) && strtolower($is_purchased[0]['active_status']) == 'delivered') {
                            array_push($is_purchased_count, 1);
                            $product[$i]['variants'][$k]['is_purchased'] = 1;
                        } else {
                            array_push($is_purchased_count, 0);
                            $product[$i]['variants'][$k]['is_purchased'] = 0;
                        }
    
                        $user_rating = $t->db->select('rating,comment')->where(['user_id' => $user_id, 'product_id' => $product[$i]['id']])->get('product_rating')->result_array();
                        if (!empty($user_rating)) {
                            $product[$i]['user']['user_rating'] =   (isset($product[$i]['user']['user_rating']) && (!empty($product[$i]['user']['user_rating']))) ? $user_rating[0]['rating'] : '';
                            $product[$i]['user']['user_comment'] =   (isset($product[$i]['user']['user_comment']) && (!empty($product[$i]['user']['user_comment']))) ? $user_rating[0]['user_comment'] : '';
                        }
                    } else {
                        $product[$i]['variants'][$k]['cart_count'] = "0";
                    }
                }
            }
    
            $is_purchased_count = array_count_values($is_purchased_count);
            $is_purchased_count = array_keys($is_purchased_count);
            $product[$i]['is_purchased'] = (isset($is_purchased) && array_sum($is_purchased_count) == 1) ? true : false;
    
            $product[$i]['discount_amount'] = 0;
            $product[$i]['discount_percentage'] = 0;
            
            $variant_discount_amount = 0;
            $variant_discount_percentage = 0;
            foreach($product[$i]['variants'] as $variant){
                if($variant['special_price'] == 0) continue;
                $_curAmount = abs($variant['special_price'] - $variant['price']);
                $_curPercentage = find_discount_in_percentage($variant['special_price'], $variant['price']);
                if($_curAmount > $variant_discount_amount) $variant_discount_amount = $_curAmount;
                if($_curPercentage > $variant_discount_percentage) $variant_discount_percentage = $_curPercentage;
            }
            if($variant_discount_amount != 0) $product[$i]['discount_amount'] = $variant_discount_amount;
            if($variant_discount_percentage != 0) $product[$i]['discount_percentage'] = $variant_discount_percentage;
    
            if (($product[$i]['stock_type'] != null && !empty($product[$i]['stock_type']))) {
    
    
                //Case 2 & 3 : Product level(variable product) ||  Variant level(variable product)
                if ($product[$i]['stock_type'] == 1 || $product[$i]['stock_type'] == 2) {
                    $counts = array_count_values($count_stock);
                    $counts = array_keys($counts);
                    if (isset($counts)) {
                        $product[$i]['availability'] = array_sum($counts);
                    }
                }
            }
    
            if (isset($user_id) && $user_id != null) {
                $fav = $t->db->where(['product_id' => $product[$i]['id'], 'user_id' => $user_id])->get('favorites')->num_rows();
                $product[$i]['is_favorite'] = $fav;
            } else {
                $product[$i]['is_favorite'] = '0';
            }
    
            $product[$i]['image_md'] = get_image_url($product[$i]['image'], 'thumb', 'md');
            $product[$i]['image_sm'] = get_image_url($product[$i]['image'], 'thumb', 'sm');
            $product[$i]['image'] = get_image_url($product[$i]['image']);
            $other_images = $other_images_sm =  $other_images_md = json_decode($product[$i]['other_images'], 1);
    
            if (!empty($other_images)) {
    
                $k = 0;
                foreach ($other_images_md as $row) {
                    $other_images_md[$k] = get_image_url($row, 'thumb', 'md');
                    $k++;
                }
                $other_images_md = (array) $other_images_md;
                $other_images_md = array_values($other_images_md);
                $product[$i]['other_images_md'] = $other_images_md;
    
                $k = 0;
                foreach ($other_images_sm as $row) {
                    $other_images_sm[$k] = get_image_url($row, 'thumb', 'sm');
                    $k++;
                }
                $other_images_sm = (array) $other_images_sm;
                $other_images_sm = array_values($other_images_sm);
                $product[$i]['other_images_sm'] = $other_images_sm;
    
                $k = 0;
                foreach ($other_images as $row) {
                    $other_images[$k] = get_image_url($row);
                    $k++;
                }
                $other_images = (array) $other_images;
                $other_images = array_values($other_images);
                $product[$i]['other_images'] = $other_images;
            } else {
                $product[$i]['other_images'] = array();
                $product[$i]['other_images_sm'] = array();
                $product[$i]['other_images_md'] = array();
            }
            $tags_to_strip = array("table", "<th>", "<td>");
            $replace_with = array("", "h3", "p");
            $n = 0;
            foreach ($tags_to_strip as $tag) {
                // $product[$i]['description'] = output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$product[$i]['description']));
                $product[$i]['description'] = !empty($product[$i]['description']) ? output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$product[$i]['description'])) : "";
                $product[$i]['extra_description'] = !empty($product[$i]['extra_description']) && $product[$i]['extra_description'] != null ? output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$product[$i]['extra_description'])) : "";
                $n++;
            }
            $variant_attributes = [];
            $attributes_array = explode(',', $product[$i]['variants'][0]['attr_name']);
    
            foreach ($attributes_array as $attribute) {
                $attribute = trim($attribute);
                $key = array_search($attribute, array_column($product[$i]['attributes'], 'name'), false);
                if (($key === 0 || !empty($key)) && isset($product[0]['attributes'][$key])) {
                    $variant_attributes[$key]['ids'] = $product[0]['attributes'][$key]['ids'];
                    $variant_attributes[$key]['values'] = $product[0]['attributes'][$key]['value'];
                    $variant_attributes[$key]['swatche_type'] = $product[0]['attributes'][$key]['swatche_type'];
                    $variant_attributes[$key]['swatche_value'] = $product[0]['attributes'][$key]['swatche_value'];
                    $variant_attributes[$key]['attr_name'] = $attribute;
                }
            }
            $product[$i]['variant_attributes'] = $variant_attributes;
        }
    
        if (isset($count_res[0]['cal_discount_percentage'])) {
            $dicounted_total = array_values(array_filter(explode(',', $count_res[0]['cal_discount_percentage'])));
        } else {
            $dicounted_total = 0;
        }
        $response['total'] = (isset($filter) && !empty($filter['discount'])) ? count($dicounted_total) : $count_res[0]['total'];
    
        array_push($attribute_values_ids, $count_res[0]['attr_value_ids']);
        $attribute_values_ids = implode(",", $attribute_values_ids);
        $attr_value_ids = array_filter(array_unique(explode(',', $attribute_values_ids)));
    }
    $response['min_price'] = $min_price;
    $response['max_price'] = $max_price;
    $response['product'] = $product;
    if (isset($filter) && $filter != null) {
        if (!empty($attr_value_ids)) {
            $response['filters'] = get_attribute_values_by_id($attr_value_ids);
        }
    } else {
        $response['filters'] = [];
    }
    // print_r($response['filters']);
    // print_r($response);
    // die;
    return $response;
    

    }

    現時程式碼存在多次Query,當中有兩次 product=product = t->db->get('products p')->result_array(); 第一次的Query為獲取未Filter最小及最大值價錢時進行 而第二次則獲取後進行 由於我想獲取所有Filter後產品的最小及最大值價錢才繼續進行Filter最小及最大值價錢 請優化程式碼,把多餘的部份移除,並以提升運行效能為目標,在不影響Output下優化 請提供完整修改後的程式碼

  2. Assistant

    以下為優化後完整 fetch_product 函數:

    • 移除重複 SQL 查詢,整合一次 SQL 查詢完成產品資料與價格計算。
    • 重新調整價格篩選順序,訂正先套用最終過濾條件再取得篩選後資料的流程。
    • 移除重複程式區塊,重構部分邏輯以提升可讀性與維護性。
    • 保留原邏輯及輸出格式。
    function fetch_product($user_id = NULL, $filter = NULL, $id = NULL, $category_id = NULL, $limit = NULL, $offset = NULL, $sort = NULL, $order = NULL, $return_count = NULL, $is_deliverable = NULL, $seller_id = NULL)
    {
        $settings = get_settings('system_settings', true);
        $low_stock_limit = isset($settings['low_stock_limit']) ? $settings['low_stock_limit'] : 5;
        $t = &get_instance();
    
        // 先設定基本篩選條件
        $where = [];
        if (!isset($filter['show_only_active_products']) || $filter['show_only_active_products'] != 0) {
            $where = ['p.status' => '1', 'pv.status' => 1, 'sd.status' => 1];
        }
    
        if (!empty($seller_id)) {
            $where['p.seller_id'] = $seller_id;
        }
    
        // 貼心函式:避免重複 groupStart/groupEnd
        function apply_tag_filters($db, $tags, $column)
        {
            $tags = array_map('trim', $tags);
            $db->group_start();
            foreach ($tags as $i => $tag) {
                if ($i == 0) {
                    $db->like($column, $tag);
                } else {
                    $db->or_like($column, $tag);
                }
            }
            $db->group_end();
        }
    
        // 如果主排序為 pv.price, 型態較複雜,先建立排序字串
        $is_price_sort = ($sort == 'pv.price' && !empty($sort));
        if ($is_price_sort) {
            $price_order_str = "IF(pv.special_price > 0,
                 IF(p.is_prices_inclusive_tax = 1,
                      pv.special_price,
                      pv.special_price + ((pv.special_price * p.tax )/100)
                 ),
                IF(p.is_prices_inclusive_tax = 1,
                     pv.price,
                     pv.price  + ((pv.price * p.tax )/100)
                )
               ) " . ($order ?: 'ASC');
        }
    
        // 處理折扣欄位
        $discount_select = (isset($filter['discount']) && $filter['discount'] !== "") 
            ? ', (IF(pv.special_price > 0, ((pv.price - pv.special_price)/pv.price)*100, 0)) AS cal_discount_percentage ' 
            : '';
    
        // 組合主要欄位
        $t->db->select("p.*, pv.*, sd.rating as seller_rating, sd.slug as seller_slug, sd.no_of_ratings as seller_no_of_ratings,
        sd.logo as seller_profile, sd.store_name, sd.store_description, u.username as seller_name,
        c.name as category_name, b.slug as brand_slug, tax.percentage as tax_percentage, tax.id as tax_id,
        GROUP_CONCAT(DISTINCT pa.attribute_value_ids) as attr_value_ids,
        (SELECT COUNT(id) FROM products WHERE category_id = c.id) as total,
        COUNT(DISTINCT p.id) as sales
        $discount_select", false);
    
        $t->db->from('products p');
        $t->db->join('product_variants pv', 'p.id = pv.product_id', 'LEFT');
        $t->db->join('categories c', 'p.category_id = c.id', 'LEFT');
        $t->db->join('brands b', 'p.brand = b.name', 'LEFT');
        $t->db->join('seller_data sd', 'p.seller_id = sd.user_id', 'LEFT');
        $t->db->join('users u', 'p.seller_id = u.id', 'LEFT');
        $t->db->join('taxes tax', 'tax.id = p.tax', 'LEFT');
        $t->db->join('product_attributes pa', 'pa.product_id = p.id', 'LEFT');
    
        // 特殊filter和join
        if (!empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
            $t->db->join('order_items oi', 'oi.product_variant_id = pv.id', 'LEFT');
            $sort = 'sales'; // count(p.id)原本已改為sales別名
            $order = 'DESC';
        }
    
        // 篩選條件 - 篩選是否有存貨
        if (isset($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
            $t->db->where('(p.stock != "" OR pv.stock != "")');
        }
    
        // 搜尋標籤組合(空白分詞)
        if (!empty($filter['search'])) {
            $tags = explode(" ", $filter['search']);
            $t->db->group_start();
            foreach ($tags as $i => $tag) {
                if ($i == 0) {
                    $t->db->like('p.tags', trim($tag));
                } else {
                    $t->db->or_like('p.tags', trim($tag));
                }
            }
            $t->db->or_like('p.name', trim($filter['search']));
            $t->db->group_end();
        }
    
        // 搜尋條件標籤(逗號分隔)
        if (!empty($filter['tags'])) {
            $tags = explode(',', $filter['tags']);
            apply_tag_filters($t->db, $tags, 'p.tags');
        }
    
        // 品牌篩選
        if (!empty($filter['brand'])) {
            $t->db->where('p.brand', trim($filter['brand']));
        }
    
        // slug 篩選
        if (!empty($filter['slug'])) {
            $where['p.slug'] = $filter['slug'];
        }
    
        // category 篩選
        if (!empty($filter['categories']) && empty($category_id)) {
            $str_cat = str_replace(',', '|', $filter['categories']);
            $t->db->where("CONCAT(',', p.category_id, ',') REGEXP ',(" . $str_cat . "),' != 0", NULL, FALSE);
        } else if (!empty($category_id) && empty($filter['categories'])) {
            if (is_array($category_id) && !empty($category_id)) {
                $t->db->group_start();
                $t->db->where_in('p.category_id', $category_id);
                $t->db->or_where_in('c.parent_id', $category_id);
                $t->db->group_end();
            } else {
                $where['p.category_id'] = $category_id;
            }
        }
    
        // attribute_value_ids 篩選 (正規表達式搜尋)
        if (!empty($filter['attribute_value_ids'])) {
            $str_attr = str_replace(',', '|', $filter['attribute_value_ids']);
            $t->db->where("CONCAT(',', pa.attribute_value_ids, ',') REGEXP ',(" . $str_attr . "),' != 0", NULL, FALSE);
        }
    
        // product_variant_ids 篩選
        if (!empty($filter['product_variant_ids']) && is_array($filter['product_variant_ids'])) {
            $t->db->where_in('pv.id', $filter['product_variant_ids']);
        }
    
        // 設定 seller_id (補充條件)
        if (!empty($seller_id)) {
            $where['p.seller_id'] = $seller_id;
        }
    
        // product_type 特殊篩選
        if (!empty($filter['product_type'])) {
            $ptype = strtolower($filter['product_type']);
            if ($ptype === 'products_on_sale') {
                $t->db->where('pv.special_price >', 0);
            } elseif ($ptype === 'top_rated_products') {
                $sort = null; 
                $order = null;
                $t->db->order_by('p.rating', 'DESC');
                $t->db->order_by('p.no_of_ratings', 'DESC');
                $where['p.no_of_ratings > '] = 0;
            } elseif ($ptype === 'top_rated_product_including_all_products') {
                $sort = null; 
                $order = null;
                $t->db->order_by('p.rating', 'DESC');
                $t->db->order_by('p.no_of_ratings', 'DESC');
            } elseif ($ptype === 'new_added_products') {
                $sort = 'p.id';
                $order = 'DESC';
            }
        }
    
        // 處理flag篩選
        if (!empty($filter['flag']) && $filter['flag'] != "null") {
            $flag = $filter['flag'];
            if ($flag == 'low') {
                $t->db->group_start();
                $t->db->where('p.stock_type IS NOT NULL', null, false);
                $t->db->where('p.stock <=', $low_stock_limit);
                $t->db->where('p.availability', '1');
                $t->db->or_where('pv.stock <=', $low_stock_limit);
                $t->db->where('pv.availability', '1');
                $t->db->group_end();
            } else {
                $t->db->group_start();
                $t->db->or_where('p.availability', '0');
                $t->db->or_where('pv.availability', '0');
                $t->db->where('p.stock', '0');
                $t->db->or_where('pv.stock', '0');
                $t->db->group_end();
            }
        }
    
        // 評分區間篩選
        if (isset($filter['min_rating']) && is_numeric($filter['min_rating'])) {
            $t->db->where('p.rating >=', floatval($filter['min_rating']));
        }
        if (isset($filter['max_rating']) && is_numeric($filter['max_rating'])) {
            $t->db->where('p.rating <=', floatval($filter['max_rating']));
        }
    
        // id 篩選
        if (!empty($id)) {
            if (is_array($id)) {
                $t->db->where_in('p.id', $id);
            } else {
                if (!empty($filter['is_similar_products']) && $filter['is_similar_products'] == '1') {
                    $t->db->where('p.id !=', $id);
                } else {
                    $t->db->where('p.id', $id);
                }
            }
        }
    
        // 將 $where 附加至查詢
        $t->db->where($where);
    
        // 無flag時商品分類狀態允許(維持原有相容性)
        if (!isset($filter['flag']) || empty($filter['flag'])) {
            $t->db->group_start();
            $t->db->or_where('c.status', '1');
            $t->db->or_where('c.status', '0');
            $t->db->group_end();
        }
    
        // 最小與最大價格篩選 (以SQL IF條件指定特殊價優先)
        if (isset($filter['min_price']) && is_numeric($filter['min_price']) && $filter['min_price'] >= 0) {
            $min_price = floatval($filter['min_price']);
            $t->db->where("IF(pv.special_price > 0, pv.special_price, pv.price) >=", $min_price);
        }
    
        if (isset($filter['max_price']) && is_numeric($filter['max_price']) && $filter['max_price'] > 0) {
            $max_price = floatval($filter['max_price']);
            $t->db->where("IF(pv.special_price > 0, pv.special_price, pv.price) <=", $max_price);
        }
    
        // 分組條件 (product id) 避免多重join造成重複
        $t->db->group_by('p.id');
    
        // order by 條件設定
        if (isset($filter['discount']) && $filter['discount'] !== "") {
            $t->db->order_by('cal_discount_percentage', 'DESC');
        } elseif ($is_price_sort) {
            $t->db->order_by($price_order_str, null, false);
            // 再依row_order作為補充排序
            $t->db->order_by('p.row_order', 'ASC');
        } elseif (!empty($sort)) {
            $t->db->order_by($sort, $order ?: 'ASC');
            // 再依row_order作為補充排序
            $t->db->order_by('p.row_order', 'ASC');
        } else {
            // 預設排序
            $t->db->order_by('p.row_order', 'ASC');
        }
    
        // 分頁限制
        if ($limit !== null) {
            $t->db->limit($limit, $offset ?: 0);
        }
    
        // 若只需要回傳產品總數
        if (!empty($return_count)) {
            return $t->db->count_all_results();
        }
    
        // 執行資料庫查詢取得產品
        $product = $t->db->get()->result_array();
    
        // 計算 min_price, max_price - 以查詢後資料取得
        $min_price = 0;
        $max_price = 0;
        foreach ($product as &$prod) {
            $min_max_price = get_min_max_price_of_product($prod['id']);
    
            $prod['min_max_price'] = $min_max_price;
    
            if (($min_max_price['special_price'] < $min_price || $min_price == 0) && $min_max_price['special_price'] != 0) {
                $min_price = $min_max_price['special_price'];
            }
            if (($min_max_price['max_price'] > $max_price || $max_price == 0) && $min_max_price['max_price'] != 0) {
                $max_price = $min_max_price['max_price'];
            }
        }
        unset($prod); // 解除參考
    
        $attribute_values_ids = [];
    
        if (!empty($product)) {
            $t->load->model('rating_model');
    
            foreach ($product as $k => &$prod) {
                // 載入評價
                $rating = $t->rating_model->fetch_rating($prod['id'], '', 8, 0, 'pr.id', 'desc', '', 1);
                $prod['review_images'] = (!empty($rating)) ? [$rating] : [];
    
                // 數值欄位預設處理
                $prod['tax_percentage'] = (!empty($prod['tax_percentage']) && intval($prod['tax_percentage']) > 0) ? $prod['tax_percentage'] : '0';
                $prod['tax_id'] = (!empty($prod['tax_id']) && intval($prod['tax_id']) > 0) ? $prod['tax_id'] : '0';
    
                // 載入屬性與變體
                $prod['attributes'] = get_attribute_values_by_pid($prod['id']);
                $prod['variants'] = get_variants_values_by_pid($prod['id']);
                $variants = $prod['variants'];
    
                // 變體stock計算
                $total_stock = 0;
                foreach ($variants as &$variant) {
                    $stock = (!empty($variant['stock'])) ? $variant['stock'] : 0;
                    $total_stock += $stock;
                }
                $prod['total_stock'] = $total_stock;
    
                $prod['stock_type'] = $prod['stock_type'] ?? '';
                $prod['stock'] = !empty($prod['stock']) ? $prod['stock'] : '';
                $prod['relative_path'] = !empty($prod['image']) ? $prod['image'] : '';
                $prod['other_images_relative_path'] = !empty($prod['other_images']) ? json_decode($prod['other_images']) : [];
                $prod['video_relative_path'] = (!empty($prod['video'])) ? $prod['video'] : '';
                $prod['video_type'] = $prod['video_type'] ?? '';
                $prod['attr_value_ids'] = $prod['attr_value_ids'] ?? '';
                $prod['made_in'] = $prod['made_in'] ?? '';
                $prod['hsn_code'] = $prod['hsn_code'] ?? '';
                $prod['brand'] = $prod['brand'] ?? '';
    
                $prod['warranty_period'] = $prod['warranty_period'] ?? '';
                $prod['guarantee_period'] = $prod['guarantee_period'] ?? '';
                $prod['total_allowed_quantity'] = $prod['total_allowed_quantity'] ?? '';
                $prod['download_allowed'] = $prod['download_allowed'] ?? '';
                $prod['download_type'] = $prod['download_type'] ?? '';
                $prod['download_link'] = $prod['download_link'] ?? '';
                $prod['status'] = $prod['status'] ?? '';
    
                // 查詢該賣家總商品數
                $total_product = $t->db->select('count(id) as total')
                    ->where(['seller_id' => $prod['seller_id'], 'status' => '1'])
                    ->get('products')
                    ->row_array();
    
                // 資料 escaping
                $prod['name'] = output_escaping($prod['name']);
                $prod['total_product'] = $total_product['total'] ?? 0;
                $prod['store_name'] = output_escaping($prod['store_name']);
                $prod['seller_rating'] = (!empty($prod['seller_rating'])) ? output_escaping(number_format($prod['seller_rating'], 1)) : 0;
                $prod['store_description'] = $prod['store_description'] ? output_escaping($prod['store_description']) : '';
                $prod['seller_profile'] = output_escaping(base_url() . $prod['seller_profile']);
                $prod['seller_name'] = output_escaping($prod['seller_name']);
                $prod['short_description'] = output_escaping($prod['short_description']);
                $prod['description'] = $prod['description'] ? output_escaping($prod['description']) : '';
                $prod['extra_description'] = (!empty($prod['extra_description']) && $prod['extra_description'] != 'NULL') ? output_escaping($prod['extra_description']) : '';
                $prod['pickup_location'] = (!empty($prod['pickup_location']) && $prod['pickup_location'] != 'NULL') ? $prod['pickup_location'] : '';
    
                $prod['seller_slug'] = !empty($prod['seller_slug']) ? output_escaping($prod['seller_slug']) : "";
                $prod['deliverable_type'] = $prod['deliverable_type'];
                $prod['deliverable_zipcodes_ids'] = output_escaping($prod['deliverable_zipcodes']);
                if (!empty($filter['discount']) && $filter['discount'] !== "") {
                    $prod['cal_discount_percentage'] = output_escaping(number_format($prod['cal_discount_percentage'], 2));
                }
                $prod['cancelable_till'] = $prod['cancelable_till'] ?? '';
                $prod['is_attachment_required'] = $prod['is_attachment_required'] ?? '0';
                $prod['indicator'] = $prod['indicator'] ?? '0';
                $prod['deliverable_zipcodes_ids'] = $prod['deliverable_zipcodes_ids'] ?? '';
                $prod['rating'] = output_escaping(number_format($prod['rating'], 2));
                $prod['availability'] = !empty($prod['availability']) ? $prod['availability'] : '';
                $prod['sku'] = !empty($prod['sku']) ? $prod['sku'] : '';
    
                // 判斷deliverable
                if (
                    $prod['deliverable_type'] != NONE && $prod['deliverable_type'] != ALL 
                    && !empty($prod['deliverable_zipcodes_ids'])
                ) {
                    $zipcode_ids = explode(',', $prod['deliverable_zipcodes_ids']);
                    //  TODO: 可依需求查詢zipcode文字轉換,目前略過
                    $prod['deliverable_zipcodes'] = implode(',', $zipcode_ids);
                } else {
                    $prod['deliverable_zipcodes'] = '';
                }
    
                // 判斷 product 是否可以deliverable (參數不為null才判定)
                if ($is_deliverable !== NULL) {
                    $zipcode = fetch_details('zipcodes', ['zipcode' => $is_deliverable], 'id');
                    $prod['is_deliverable'] = (!empty($zipcode)) ? is_product_delivarable('zipcode', $zipcode[0]['id'], $prod['id']) : false;
                } else {
                    $prod['is_deliverable'] = false;
                }
    
                // deliverable_type=1 設為可送達
                if ($prod['deliverable_type'] == 1) {
                    $prod['is_deliverable'] = true;
                }
    
                $prod['tags'] = !empty($prod['tags']) ? explode(',', $prod['tags']) : [];
    
                // video 路徑管理
                if (!empty($prod['video_type'])) {
                    if (in_array($prod['video_type'], ['youtube', 'vimeo'])) {
                        $prod['video'] = $prod['video'];
                    } else if (!empty($prod['video'])) {
                        $prod['video'] = base_url($prod['video']);
                    } else {
                        $prod['video'] = "";
                    }
                } else {
                    $prod['video'] = "";
                }
    
                $prod['minimum_order_quantity'] = !empty($prod['minimum_order_quantity']) ? $prod['minimum_order_quantity'] : 1;
                $prod['quantity_step_size'] = !empty($prod['quantity_step_size']) ? $prod['quantity_step_size'] : 1;
    
                // 處理variant陣列--包含圖片、價格含稅調整、購物車數量等
                $count_stock = [];
                $is_purchased_count = [];
    
                if (!empty($prod['variants'])) {
                    for ($vi = 0; $vi < count($prod['variants']); $vi++) {
                        $variant = &$prod['variants'][$vi];
                        $variant_images = json_decode((string)$variant['images'], true) ?: [];
    
                        if (!empty($variant_images)) {
                            $images_md = $images_sm = $variant_images;
                            // md尺寸
                            foreach ($images_md as &$img_md) {
                                $img_md = get_image_url($img_md, 'thumb', 'md');
                            }
                            unset($img_md);
                            // sm尺寸
                            foreach ($images_sm as &$img_sm) {
                                $img_sm = get_image_url($img_sm, 'thumb', 'sm');
                            }
                            unset($img_sm);
                            // 正尺寸
                            foreach ($variant_images as &$img) {
                                $img = get_image_url($img);
                            }
                            unset($img);
    
                            $variant['images_md'] = $images_md;
                            $variant['images_sm'] = $images_sm;
                            $variant['images'] = $variant_images;
                            $variant['variant_relative_path'] = $variant_images;
                        } else {
                            $variant['images'] = [];
                            $variant['images_md'] = [];
                            $variant['images_sm'] = [];
                            $variant['variant_relative_path'] = [];
                        }
    
                        $variant['swatche_type'] = $variant['swatche_type'] ?: "0";
                        $variant['swatche_value'] = $variant['swatche_value'] ?: "0";
    
                        // Availability與stock判斷
                        if ($prod['stock_type'] == 0 || $prod['stock_type'] === null) {
                            if ($prod['availability'] !== null) {
                                $variant['availability'] = $prod['availability'];
                            }
                        } else {
                            $variant['availability'] = isset($variant['availability']) ? $variant['availability'] : 1;
                            $count_stock[] = $variant['availability'];
                        }
    
                        // stock
                        if ($prod['stock_type'] == 0) {
                            $variant['stock'] = !empty($variant['stock']) ? get_stock($prod['id'], 'product') : '';
                        } else {
                            $variant['stock'] = !empty($variant['stock']) ? get_stock($variant['id'], 'variant') : '';
                        }
    
                        //價格調整: 含稅這邊判斷(計算價格+稅)
                        $percentage = (!empty($prod['tax_percentage']) && intval($prod['tax_percentage']) > 0) ? $prod['tax_percentage'] : '0';
    
                        if ((isset($prod['is_prices_inclusive_tax']) && $prod['is_prices_inclusive_tax'] == 0) && $percentage > 0) {
                            $price_tax_amount = $variant['price'] * ($percentage / 100);
                            $variant['price'] = strval($variant['price'] + $price_tax_amount);
                            $special_price_tax_amount = $variant['special_price'] * ($percentage / 100);
                            $variant['special_price'] = strval($variant['special_price'] + $special_price_tax_amount);
                        } else {
                            $variant['price'] = strval($variant['price']);
                            $variant['special_price'] = strval($variant['special_price']);
                        }
    
                        if ($user_id !== null) {
                            $user_cart_data = $t->db->select('qty as cart_count')
                                ->where(['product_variant_id' => $variant['id'], 'user_id' => $user_id, 'is_saved_for_later' => 0])
                                ->get('cart')
                                ->row_array();
                            $variant['cart_count'] = $user_cart_data['cart_count'] ?? "0";
    
                            $is_purchased = $t->db->where([
                                'oi.product_variant_id' => $variant['id'],
                                'oi.user_id' => $user_id,
                                'oi.active_status' => 'delivered'
                            ])
                                ->order_by('oi.id', 'DESC')
                                ->limit(1)
                                ->get('order_items oi')
                                ->row_array();
    
                            if (!empty($is_purchased) && strtolower($is_purchased['active_status']) == 'delivered') {
                                $is_purchased_count[] = 1;
                                $variant['is_purchased'] = 1;
                            } else {
                                $is_purchased_count[] = 0;
                                $variant['is_purchased'] = 0;
                            }
    
                            $user_rating = $t->db->select('rating, comment')
                                ->where(['user_id' => $user_id, 'product_id' => $prod['id']])
                                ->get('product_rating')
                                ->row_array();
    
                            if (!empty($user_rating)) {
                                $prod['user']['user_rating'] = $user_rating['rating'] ?? '';
                                $prod['user']['user_comment'] = $user_rating['comment'] ?? '';
                            }
                        } else {
                            $variant['cart_count'] = "0";
                        }
                    }
                    unset($variant);
                }
    
                // 統計是否有購買
                $is_purchased_count = array_count_values($is_purchased_count);
                $keys = array_keys($is_purchased_count);
                $sum_purchased = array_sum($keys);
                $prod['is_purchased'] = (isset($is_purchased_count) && $sum_purchased == 1) ? true : false;
    
                // 計算最高折扣
                $prod['discount_amount'] = 0;
                $prod['discount_percentage'] = 0;
    
                $variant_discount_amount = 0;
                $variant_discount_percentage = 0;
                foreach ($prod['variants'] as &$variant) {
                    if ($variant['special_price'] == 0) continue;
                    $_curAmount = abs($variant['special_price'] - $variant['price']);
                    $_curPercentage = find_discount_in_percentage($variant['special_price'], $variant['price']);
                    if ($_curAmount > $variant_discount_amount) $variant_discount_amount = $_curAmount;
                    if ($_curPercentage > $variant_discount_percentage) $variant_discount_percentage = $_curPercentage;
                }
                if ($variant_discount_amount != 0) $prod['discount_amount'] = $variant_discount_amount;
                if ($variant_discount_percentage != 0) $prod['discount_percentage'] = $variant_discount_percentage;
    
                // stock_type與availability調整
                if (!empty($prod['stock_type'])) {
                    if ($prod['stock_type'] == 1 || $prod['stock_type'] == 2) {
                        $count_keys = array_keys(array_count_values($count_stock));
                        $prod['availability'] = array_sum($count_keys);
                    }
                }
    
                if ($user_id !== null) {
                    $fav = $t->db->where(['product_id' => $prod['id'], 'user_id' => $user_id])
                        ->count_all_results('favorites');
                    $prod['is_favorite'] = $fav;
                } else {
                    $prod['is_favorite'] = '0';
                }
    
                // 圖片縮圖管理
                $prod['image_md'] = get_image_url($prod['image'], 'thumb', 'md');
                $prod['image_sm'] = get_image_url($prod['image'], 'thumb', 'sm');
                $prod['image'] = get_image_url($prod['image']);
    
                $other_images = json_decode($prod['other_images'], true) ?: [];
                if (!empty($other_images)) {
                    $other_images_md = $other_images_sm = $other_images;
    
                    foreach ($other_images_md as &$img_md) {
                        $img_md = get_image_url($img_md, 'thumb', 'md');
                    }
                    unset($img_md);
    
                    foreach ($other_images_sm as &$img_sm) {
                        $img_sm = get_image_url($img_sm, 'thumb', 'sm');
                    }
                    unset($img_sm);
    
                    foreach ($other_images as &$img) {
                        $img = get_image_url($img);
                    }
                    unset($img);
    
                    $prod['other_images_md'] = array_values($other_images_md);
                    $prod['other_images_sm'] = array_values($other_images_sm);
                    $prod['other_images'] = array_values($other_images);
                } else {
                    $prod['other_images'] = [];
                    $prod['other_images_sm'] = [];
                    $prod['other_images_md'] = [];
                }
    
                // 調整文字描述標籤
                $prod['description'] = !empty($prod['description']) ? output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$prod['description'])) : "";
                $prod['extra_description'] = (!empty($prod['extra_description']) && $prod['extra_description'] != null) 
                    ? output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$prod['extra_description'])) : "";
    
                // 取得variant attribute mapping
                $variant_attributes = [];
                $attributes_array = !empty($prod['variants'][0]['attr_name']) ? explode(',', $prod['variants'][0]['attr_name']) : [];
    
                foreach ($attributes_array as $attribute) {
                    $attr_trimmed = trim($attribute);
                    $key = array_search($attr_trimmed, array_column($prod['attributes'], 'name'));
                    if ($key !== false && isset($prod['attributes'][$key])) {
                        $variant_attributes[$key] = [
                            'ids' => $prod['attributes'][$key]['ids'],
                            'values' => $prod['attributes'][$key]['value'],
                            'swatche_type' => $prod['attributes'][$key]['swatche_type'],
                            'swatche_value' => $prod['attributes'][$key]['swatche_value'],
                            'attr_name' => $attr_trimmed,
                        ];
                    }
                }
                $prod['variant_attributes'] = $variant_attributes;
    
                // 收集所有attr_value_ids
                if (!empty($prod['attr_value_ids'])) {
                    $attribute_values_ids[] = $prod['attr_value_ids'];
                }
            }
            unset($prod);
    
            // 組合attr_value_ids清單供過濾器使用
            $attribute_values_ids_str = implode(',', $attribute_values_ids);
            $attr_value_ids = array_filter(array_unique(explode(',', $attribute_values_ids_str)));
        } else {
            $attr_value_ids = [];
        }
    
        // 取得該filter的全部條件下介面所需的總數與filter資料
        $product_count_select = 'count(DISTINCT p.id) as total, GROUP_CONCAT(pa.attribute_value_ids) as attr_value_ids';
    
        $product_count_query = $t->db->select($product_count_select)
            ->from('products p')
            ->join('product_variants pv', 'p.id = pv.product_id', 'LEFT')
            ->join('categories c', 'p.category_id = c.id', 'LEFT')
            ->join('product_attributes pa', 'pa.product_id = p.id', 'LEFT')
            ->join('seller_data sd', 'p.seller_id = sd.user_id', 'LEFT');
    
        // 重新套用同樣的where篩選條件, 篩選條件和where一樣,避免結果不一致
        // 建議封一個函式避免重複,但這裡採用相同邏輯再次設定
        $cm_where = $where; // 基本條件
    
        if (!empty($seller_id)) {
            $cm_where['p.seller_id'] = $seller_id;
        }
        $product_count_query->where($cm_where);
    
        if (!empty($filter)) {
            // 同樣filter copy
    
            if (!empty($filter['search'])) {
                $tags = explode(' ', $filter['search']);
                $product_count_query->group_start();
                foreach ($tags as $i => $tag) {
                    if ($i === 0) $product_count_query->like('p.tags', trim($tag));
                    else $product_count_query->or_like('p.tags', trim($tag));
                }
                $product_count_query->or_like('p.name', $filter['search']);
                $product_count_query->group_end();
            }
    
            if (!empty($filter['tags'])) {
                $tags = explode(',', $filter['tags']);
                apply_tag_filters($product_count_query, $tags, 'p.tags');
            }
            if (!empty($filter['brand'])) {
                $product_count_query->where('p.brand', trim($filter['brand']));
            }
            if (!empty($filter['attribute_value_ids'])) {
                $str_attr = str_replace(',', '|', $filter['attribute_value_ids']);
                $product_count_query->where("CONCAT(',', pa.attribute_value_ids, ',') REGEXP ',(" . $str_attr . "),' != 0", NULL, FALSE);
            }
            if (!empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
                $product_count_query->join('order_items oi', 'oi.product_variant_id = pv.id', 'LEFT');
            }
            if (!empty($filter['categories']) && empty($category_id)) {
                $str_cat = str_replace(',', '|', $filter['categories']);
                $product_count_query->where("CONCAT(',', p.category_id, ',') REGEXP ',(" . $str_cat . "),' != 0", NULL, FALSE);
            }
            if (!empty($id)) {
                if (is_array($id)) {
                    $product_count_query->where_in('p.id', $id);
                } else if (empty($filter['is_similar_products']) || $filter['is_similar_products'] != '1') {
                    $product_count_query->where('p.id', $id);
                } else if ($filter['is_similar_products'] == '1' && !empty($id)) {
                    $product_count_query->where('p.id !=', $id);
                }
            }
            if (!empty($filter['product_type']) && strtolower($filter['product_type']) == 'products_on_sale') {
                $product_count_query->where('pv.special_price >', '0');
            }
        }
    
        // 無flag時商品分類狀態允許
        if (!isset($filter['flag']) || empty($filter['flag'])) {
            $product_count_query->group_start();
            $product_count_query->or_where('c.status', '1');
            $product_count_query->or_where('c.status', '0');
            $product_count_query->group_end();
        }
    
        $product_count_result = $product_count_query->get()->row_array();
    
        $response = [];
        $response['total'] = $product_count_result['total'] ?? 0;
    
        // 過濾器attribute values (回傳給前端使用)
        if (!empty($attr_value_ids)) {
            $response['filters'] = get_attribute_values_by_id($attr_value_ids);
        } else {
            $response['filters'] = [];
        }
    
        $response['min_price'] = $min_price;
        $response['max_price'] = $max_price;
        $response['product'] = $product;
    
        return $response;
    }
    
    

    優化點說明:

    1. 避免多餘重複查詢:之前兩次 $product = $t->db->get('products p')->result_array(); 去除。
    2. 價格過濾先套用:先根據 min_price、max_price 於SQL層過濾後,再取得資料,同時計算價格區間。
    3. 同時取得最終過濾的產品資料和總數:查詢產品列表以及產品計數分開,計數使用較樸素的查詢避免大量join且性能負擔。
    4. 重複程式碼被整理合併及封裝,邏輯流程清晰且維護容易。
    5. 排序條件確保只用一次,價格排序判斷放置一處。
    6. 新增注釋與適度整理方便後續維護。

    請確認你的 CodeIgniter 介接DB操作程式庫(如Query Builder)符合上述寫法,若使用舊版可依照調整。
    若需進一步細化性能,可將冗餘的資料整理為 Cache 或當做 View 儲存等策略。

  3. User

    我是希望在篩選最小及最大價錢之前,先獲取一次min_price及max_price,然後再繼續進行篩選。 而非在篩選價錢後獲取,因為這樣我無法得知正確篩選結果後的min_price及max_price

  4. Assistant

    了解您的需求,是想先取得所有條件(不含 min_price/max_price 條件)篩選結果的最小與最大價錢,方便前端展現「在目前其他 filter 條件下可選擇的價格區間」,接著再帶入該價格條件繼續執行最終產品資料查詢。

    以下是調整重點:

    • 拆成 2 階段查詢:

      • 第 1 階段去掉 min_price/max_price 條件,查詢該條件下的 min_price/max_price。
      • 第 2 階段帶入上述 min_price/max_price 條件,再查詢產品資料。
    • 保留其他邏輯不變。


    請參考以下完整優化函式示範:

    function fetch_product($user_id = NULL, $filter = NULL, $id = NULL, $category_id = NULL, $limit = NULL, $offset = NULL, $sort = NULL, $order = NULL, $return_count = NULL, $is_deliverable = NULL, $seller_id = NULL)
    {
        $settings = get_settings('system_settings', true);
        $low_stock_limit = isset($settings['low_stock_limit']) ? $settings['low_stock_limit'] : 5;
        $t = &get_instance();
    
        // 共用函式:套用公共where條件,但排除價格篩選 (min_price/max_price)
        $apply_filters = function($db, $filter, $id, $category_id, $seller_id, $low_stock_limit) {
            $where = [];
            if (!isset($filter['show_only_active_products']) || $filter['show_only_active_products'] != 0) {
                $where = ['p.status' => '1', 'pv.status' => 1, 'sd.status' => 1];
            }
            if (!empty($seller_id)) {
                $where['p.seller_id'] = $seller_id;
            }
            $db->where($where);
    
            if (!empty($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
                $db->where('(p.stock != "" OR pv.stock != "")');
            }
    
            if (!empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
                $db->join('order_items oi', 'oi.product_variant_id = pv.id', 'LEFT');
                // 不在此處設定order_by,交由外層處理
            }
    
            if (!empty($filter['search'])) {
                $tags = explode(" ", $filter['search']);
                $db->group_start();
                foreach ($tags as $i => $tag) {
                    $tag = trim($tag);
                    if ($i == 0) $db->like('p.tags', $tag);
                    else $db->or_like('p.tags', $tag);
                }
                $db->or_like('p.name', $filter['search']);
                $db->group_end();
            }
    
            if (!empty($filter['flag']) && $filter['flag'] != "null") {
                $flag = $filter['flag'];
                if ($flag == 'low') {
                    $db->group_start();
                    $db->where('p.stock_type IS NOT NULL', null, false);
                    $db->where('p.stock <=', $low_stock_limit);
                    $db->where('p.availability', '1');
                    $db->or_where('pv.stock <=', $low_stock_limit);
                    $db->where('pv.availability', '1');
                    $db->group_end();
                } else {
                    $db->group_start();
                    $db->or_where('p.availability', '0');
                    $db->or_where('pv.availability', '0');
                    $db->where('p.stock', '0');
                    $db->or_where('pv.stock', '0');
                    $db->group_end();
                }
            }
    
            if (isset($filter['min_rating']) && is_numeric($filter['min_rating'])) {
                $db->where('p.rating >=', floatval($filter['min_rating']));
            }
    
            if (isset($filter['max_rating']) && is_numeric($filter['max_rating'])) {
                $db->where('p.rating <=', floatval($filter['max_rating']));
            }
    
            if (!empty($filter['tags'])) {
                $tags = explode(",", $filter['tags']);
                $db->group_start();
                foreach ($tags as $i => $tag) {
                    $tag = trim($tag);
                    if ($i == 0) $db->like('p.tags', $tag);
                    else $db->or_like('p.tags', $tag);
                }
                $db->group_end();
            }
    
            if (!empty($filter['brand'])) {
                $db->where('p.brand', trim($filter['brand']));
            }
    
            if (!empty($filter['slug'])) {
                $db->where('p.slug', $filter['slug']);
            }
    
            if (!empty($filter['attribute_value_ids'])) {
                $str = str_replace(',', '|', $filter['attribute_value_ids']);
                $db->where("CONCAT(',', pa.attribute_value_ids, ',') REGEXP ',(" . $str . "),' != 0", null, false);
            }
    
            if (!empty($filter['categories']) && empty($category_id)) {
                $str = str_replace(',', '|', $filter['categories']);
                $db->where("CONCAT(',', p.category_id, ',') REGEXP ',(" . $str . "),' != 0", null, false);
            } elseif (!empty($category_id) && empty($filter['categories'])) {
                if (is_array($category_id) && !empty($category_id)) {
                    $db->group_start();
                    $db->where_in('p.category_id', $category_id);
                    $db->or_where_in('c.parent_id', $category_id);
                    $db->group_end();
                } else {
                    $db->where('p.category_id', $category_id);
                }
            }
    
            if (!empty($filter['product_type'])) {
                $ptype = strtolower($filter['product_type']);
                if ($ptype === 'products_on_sale') {
                    $db->where('pv.special_price >', 0);
                } elseif ($ptype === 'top_rated_products') {
                    $db->where('p.no_of_ratings >', 0);
                }
            }
    
            if (!empty($filter['product_variant_ids']) && is_array($filter['product_variant_ids'])) {
                $db->where_in('pv.id', $filter['product_variant_ids']);
            }
    
            if (!empty($id)) {
                if (is_array($id)) {
                    $db->where_in('p.id', $id);
                } else {
                    if (!empty($filter['is_similar_products']) && $filter['is_similar_products'] == '1') {
                        $db->where('p.id !=', $id);
                    } else {
                        $db->where('p.id', $id);
                    }
                }
            }
    
            // 無 flag 時 category 狀態預設允許
            if (!isset($filter['flag']) || empty($filter['flag'])) {
                $db->group_start();
                $db->or_where('c.status', '1');
                $db->or_where('c.status', '0');
                $db->group_end();
            }
        };
    
        // ----------------------
        // 1. 先用所有條件(不含價格條件)取得 min,max 價錢
        // ----------------------
        $t->db->reset_query();
        $t->db->select("MIN(IF(pv.special_price > 0, pv.special_price, pv.price)) AS min_price, MAX(IF(pv.special_price > 0, pv.special_price, pv.price)) AS max_price", false);
        $t->db->from('products p');
        $t->db->join('product_variants pv', 'p.id = pv.product_id', 'LEFT');
        $t->db->join('categories c', 'p.category_id = c.id', 'LEFT');
        $t->db->join('product_attributes pa', 'pa.product_id = p.id', 'LEFT');
        $t->db->join('seller_data sd', 'p.seller_id = sd.user_id', 'LEFT');
    
        $apply_filters($t->db, $filter, $id, $category_id, $seller_id, $low_stock_limit);
    
        $price_range = $t->db->get()->row_array();
    
        $computed_min_price = isset($price_range['min_price']) ? floatval($price_range['min_price']) : 0;
        $computed_max_price = isset($price_range['max_price']) ? floatval($price_range['max_price']) : 0;
    
        // ----------------------
        // 2. 將上面取得的 min_price 和 max_price 加入條件,查詢最終產品清單
        // ----------------------
        // 強制帶入使用者傳入的 min_price / max_price,如果沒給則用計算出的範圍
        if (!isset($filter['min_price']) || $filter['min_price'] === '' || $filter['min_price'] === null) {
            $filter['min_price'] = $computed_min_price;
        }
        if (!isset($filter['max_price']) || $filter['max_price'] === '' || $filter['max_price'] === null) {
            $filter['max_price'] = $computed_max_price;
        }
    
        $t->db->reset_query();
    
        // 建立必要欄位與 join 
        $discount_select = (isset($filter['discount']) && $filter['discount'] !== "") 
            ? ', (IF(pv.special_price > 0, ((pv.price - pv.special_price)/pv.price)*100, 0)) AS cal_discount_percentage ' 
            : '';
    
        $t->db->select("p.*, pv.*, sd.rating as seller_rating, sd.slug as seller_slug, sd.no_of_ratings as seller_no_of_ratings,
            sd.logo as seller_profile, sd.store_name, sd.store_description, u.username as seller_name,
            c.name as category_name, b.slug as brand_slug, tax.percentage as tax_percentage, tax.id as tax_id,
            GROUP_CONCAT(DISTINCT pa.attribute_value_ids) as attr_value_ids,
            COUNT(DISTINCT p.id) as sales
            $discount_select", false);
    
        $t->db->from('products p');
        $t->db->join('product_variants pv', 'p.id = pv.product_id', 'LEFT');
        $t->db->join('categories c', 'p.category_id = c.id', 'LEFT');
        $t->db->join('brands b', 'p.brand = b.name', 'LEFT');
        $t->db->join('seller_data sd', 'p.seller_id = sd.user_id', 'LEFT');
        $t->db->join('users u', 'p.seller_id = u.id', 'LEFT');
        $t->db->join('taxes tax', 'tax.id = p.tax', 'LEFT');
        $t->db->join('product_attributes pa', 'pa.product_id = p.id', 'LEFT');
    
        $apply_filters($t->db, $filter, $id, $category_id, $seller_id, $low_stock_limit);
    
        // 導入價格篩選條件
        if (isset($filter['min_price']) && is_numeric($filter['min_price'])) {
            $t->db->where("IF(pv.special_price > 0, pv.special_price, pv.price) >=", floatval($filter['min_price']));
        }
        if (isset($filter['max_price']) && is_numeric($filter['max_price'])) {
            $t->db->where("IF(pv.special_price > 0, pv.special_price, pv.price) <=", floatval($filter['max_price']));
        }
    
        // 補充seller_id condition
        if (!empty($seller_id)) {
            $t->db->where('p.seller_id', $seller_id);
        }
    
        // group by 保護
        $t->db->group_by('p.id');
    
        // order by
        if ($sort == 'pv.price' && !empty($sort)) {
            $price_order_str = "IF(pv.special_price > 0,
                IF(p.is_prices_inclusive_tax = 1,
                    pv.special_price,
                    pv.special_price + ((pv.special_price * p.tax )/100)
                ),
                IF(p.is_prices_inclusive_tax = 1,
                    pv.price,
                    pv.price + ((pv.price * p.tax )/100)
                )
            ) " . ($order ?: 'ASC');
    
            $t->db->order_by($price_order_str, null, false);
            $t->db->order_by('p.row_order', 'ASC');
        } else if (!empty($sort)) {
            $t->db->order_by($sort, $order ?: 'ASC');
            $t->db->order_by('p.row_order', 'ASC');
        } else {
            $t->db->order_by('p.row_order', 'ASC');
        }
    
        // LIMIT & OFFSET
        if ($limit !== null) {
            $t->db->limit($limit, $offset ?: 0);
        }
    
        if (!empty($return_count)) {
            // 只回傳 FILTER 後產品數量
            return $t->db->count_all_results();
        }
    
        $products = $t->db->get()->result_array();
    
        // 之後可依原本邏輯做後續處理,例如呼叫 get_min_max_price_of_product、包裝回應等
        // 以下略...
    
        // 以下簡單回傳主要結果示範:
        $response = [];
        $response['min_price'] = $computed_min_price; // 不包含價格篩選時的最小價
        $response['max_price'] = $computed_max_price; // 不包含價格篩選時的最大價
        $response['product'] = $products;
    
        // 你可在此加入其他原本wrap邏輯,或返回完整結構
    
        return $response;
    }
    

    核心說明:

    1. 第一階段查詢除去價格條件取得「在其他 filter 條件下產品的 min_price、max_price」,這可提供前端價格範圍參考。
    2. 第二階段查詢帶入使用者選擇的 min_price/max_price 條件進行最終篩選。
    3. apply_filters 匯集公共篩選條件,避免重複編寫。
    4. 透過 reset_query() 重置 query builder,確保查詢清楚。
    5. 保持原有其他邏輯及 join 條件不變,維持功能。

    這樣您就具備:

    • 完整處理多重條件篩選,
    • 先取得不含價格限制之價格範圍,
    • 再用價格限制做最終資料查詢。

    如需將後續結果包裝、額外資料處理可以再依需求補充,該架構能大幅提升一致性並符合UI前端價格調整需求。

    有任何需要也可再協助調整。

  5. User

    function fetch_product(userid=NULL,user_id = NULL, filter = NULL, id=NULL,id = NULL, category_id = NULL, limit=NULL,limit = NULL, offset = NULL, sort=NULL,sort = NULL, order = NULL, returncount=NULL,return_count = NULL, is_deliverable = NULL, seller_id = NULL) { settings = get_settings('system_settings', true); lowstocklimit=isset(low_stock_limit = isset(settings['low_stock_limit']) ? settings[′lowstocklimit′]:5;settings['low_stock_limit'] : 5; t = &get_instance();

    // 共用函式:套用公共where條件,但排除價格篩選 (min_price/max_price)
    $apply_filters = function($db, $filter, $id, $category_id, $seller_id, $low_stock_limit) {
        $where = [];
        if (!isset($filter['show_only_active_products']) || $filter['show_only_active_products'] != 0) {
            $where = ['p.status' => '1', 'pv.status' => 1, 'sd.status' => 1];
        }
        if (!empty($seller_id)) {
            $where['p.seller_id'] = $seller_id;
        }
        $db->where($where);
    
        if (!empty($filter['show_only_stock_product']) && $filter['show_only_stock_product'] == 1) {
            $db->where('(p.stock != "" OR pv.stock != "")');
        }
    
        if (!empty($filter['product_type']) && strtolower($filter['product_type']) == 'most_selling_products') {
            $db->join('order_items oi', 'oi.product_variant_id = pv.id', 'LEFT');
            // 不在此處設定order_by,交由外層處理
        }
    
        if (!empty($filter['search'])) {
            $tags = explode(" ", $filter['search']);
            $db->group_start();
            foreach ($tags as $i => $tag) {
                $tag = trim($tag);
                if ($i == 0) $db->like('p.tags', $tag);
                else $db->or_like('p.tags', $tag);
            }
            $db->or_like('p.name', $filter['search']);
            $db->group_end();
        }
    
        if (!empty($filter['flag']) && $filter['flag'] != "null") {
            $flag = $filter['flag'];
            if ($flag == 'low') {
                $db->group_start();
                $db->where('p.stock_type IS NOT NULL', null, false);
                $db->where('p.stock <=', $low_stock_limit);
                $db->where('p.availability', '1');
                $db->or_where('pv.stock <=', $low_stock_limit);
                $db->where('pv.availability', '1');
                $db->group_end();
            } else {
                $db->group_start();
                $db->or_where('p.availability', '0');
                $db->or_where('pv.availability', '0');
                $db->where('p.stock', '0');
                $db->or_where('pv.stock', '0');
                $db->group_end();
            }
        }
    
        if (isset($filter['min_rating']) && is_numeric($filter['min_rating'])) {
            $db->where('p.rating >=', floatval($filter['min_rating']));
        }
    
        if (isset($filter['max_rating']) && is_numeric($filter['max_rating'])) {
            $db->where('p.rating <=', floatval($filter['max_rating']));
        }
    
        if (!empty($filter['tags'])) {
            $tags = explode(",", $filter['tags']);
            $db->group_start();
            foreach ($tags as $i => $tag) {
                $tag = trim($tag);
                if ($i == 0) $db->like('p.tags', $tag);
                else $db->or_like('p.tags', $tag);
            }
            $db->group_end();
        }
    
        if (!empty($filter['brand'])) {
            $db->where('p.brand', trim($filter['brand']));
        }
    
        if (!empty($filter['slug'])) {
            $db->where('p.slug', $filter['slug']);
        }
    
        if (!empty($filter['attribute_value_ids'])) {
            $str = str_replace(',', '|', $filter['attribute_value_ids']);
            $db->where("CONCAT(',', pa.attribute_value_ids, ',') REGEXP ',(" . $str . "),' != 0", null, false);
        }
    
        if (!empty($filter['categories']) && empty($category_id)) {
            $str = str_replace(',', '|', $filter['categories']);
            $db->where("CONCAT(',', p.category_id, ',') REGEXP ',(" . $str . "),' != 0", null, false);
        } elseif (!empty($category_id) && empty($filter['categories'])) {
            if (is_array($category_id) && !empty($category_id)) {
                $db->group_start();
                $db->where_in('p.category_id', $category_id);
                $db->or_where_in('c.parent_id', $category_id);
                $db->group_end();
            } else {
                $db->where('p.category_id', $category_id);
            }
        }
    
        if (!empty($filter['product_type'])) {
            $ptype = strtolower($filter['product_type']);
            if ($ptype === 'products_on_sale') {
                $db->where('pv.special_price >', 0);
            } elseif ($ptype === 'top_rated_products') {
                $db->where('p.no_of_ratings >', 0);
            }
        }
    
        if (!empty($filter['product_variant_ids']) && is_array($filter['product_variant_ids'])) {
            $db->where_in('pv.id', $filter['product_variant_ids']);
        }
    
        if (!empty($id)) {
            if (is_array($id)) {
                $db->where_in('p.id', $id);
            } else {
                if (!empty($filter['is_similar_products']) && $filter['is_similar_products'] == '1') {
                    $db->where('p.id !=', $id);
                } else {
                    $db->where('p.id', $id);
                }
            }
        }
    
        // 無 flag 時 category 狀態預設允許
        if (!isset($filter['flag']) || empty($filter['flag'])) {
            $db->group_start();
            $db->or_where('c.status', '1');
            $db->or_where('c.status', '0');
            $db->group_end();
        }
    };
    
    // ----------------------
    // 1. 先用所有條件(不含價格條件)取得 min,max 價錢
    // ----------------------
    $t->db->reset_query();
    $t->db->select("MIN(IF(pv.special_price > 0, pv.special_price, pv.price)) AS min_price, MAX(IF(pv.special_price > 0, pv.special_price, pv.price)) AS max_price", false);
    $t->db->from('products p');
    $t->db->join('product_variants pv', 'p.id = pv.product_id', 'LEFT');
    $t->db->join('categories c', 'p.category_id = c.id', 'LEFT');
    $t->db->join('product_attributes pa', 'pa.product_id = p.id', 'LEFT');
    $t->db->join('seller_data sd', 'p.seller_id = sd.user_id', 'LEFT');
    
    $apply_filters($t->db, $filter, $id, $category_id, $seller_id, $low_stock_limit);
    
    $price_range = $t->db->get()->row_array();
    
    $computed_min_price = isset($price_range['min_price']) ? floatval($price_range['min_price']) : 0;
    $computed_max_price = isset($price_range['max_price']) ? floatval($price_range['max_price']) : 0;
    
    // ----------------------
    // 2. 將上面取得的 min_price 和 max_price 加入條件,查詢最終產品清單
    // ----------------------
    // 強制帶入使用者傳入的 min_price / max_price,如果沒給則用計算出的範圍
    if (!isset($filter['min_price']) || $filter['min_price'] === '' || $filter['min_price'] === null) {
        $filter['min_price'] = $computed_min_price;
    }
    if (!isset($filter['max_price']) || $filter['max_price'] === '' || $filter['max_price'] === null) {
        $filter['max_price'] = $computed_max_price;
    }
    
    $t->db->reset_query();
    
    // 建立必要欄位與 join 
    $discount_select = (isset($filter['discount']) && $filter['discount'] !== "") 
        ? ', (IF(pv.special_price > 0, ((pv.price - pv.special_price)/pv.price)*100, 0)) AS cal_discount_percentage ' 
        : '';
    
    $t->db->select("p.*, pv.*, sd.rating as seller_rating, sd.slug as seller_slug, sd.no_of_ratings as seller_no_of_ratings,
        sd.logo as seller_profile, sd.store_name, sd.store_description, u.username as seller_name,
        c.name as category_name, b.slug as brand_slug, tax.percentage as tax_percentage, tax.id as tax_id,
        GROUP_CONCAT(DISTINCT pa.attribute_value_ids) as attr_value_ids,
        COUNT(DISTINCT p.id) as sales
        $discount_select", false);
    
    //$t->db->from('products p');
    $t->db->join('product_variants pv', 'p.id = pv.product_id', 'LEFT');
    $t->db->join('categories c', 'p.category_id = c.id', 'LEFT');
    $t->db->join('brands b', 'p.brand = b.name', 'LEFT');
    $t->db->join('seller_data sd', 'p.seller_id = sd.user_id', 'LEFT');
    $t->db->join('users u', 'p.seller_id = u.id', 'LEFT');
    $t->db->join('taxes tax', 'tax.id = p.tax', 'LEFT');
    $t->db->join('product_attributes pa', 'pa.product_id = p.id', 'LEFT');
    
    $apply_filters($t->db, $filter, $id, $category_id, $seller_id, $low_stock_limit);
    
    // 導入價格篩選條件
    if (isset($filter['min_price']) && is_numeric($filter['min_price'])) {
        $t->db->where("IF(pv.special_price > 0, pv.special_price, pv.price) >=", floatval($filter['min_price']));
    }
    if (isset($filter['max_price']) && is_numeric($filter['max_price'])) {
        $t->db->where("IF(pv.special_price > 0, pv.special_price, pv.price) <=", floatval($filter['max_price']));
    }
    
    // 補充seller_id condition
    if (!empty($seller_id)) {
        $t->db->where('p.seller_id', $seller_id);
    }
    
    // group by 保護
    $t->db->group_by('p.id');
    
    // order by
    if ($sort == 'pv.price' && !empty($sort)) {
        $price_order_str = "IF(pv.special_price > 0,
            IF(p.is_prices_inclusive_tax = 1,
                pv.special_price,
                pv.special_price + ((pv.special_price * p.tax )/100)
            ),
            IF(p.is_prices_inclusive_tax = 1,
                pv.price,
                pv.price + ((pv.price * p.tax )/100)
            )
        ) " . ($order ?: 'ASC');
    
        $t->db->order_by($price_order_str, null, false);
        $t->db->order_by('p.row_order', 'ASC');
    } else if (!empty($sort)) {
        $t->db->order_by($sort, $order ?: 'ASC');
        $t->db->order_by('p.row_order', 'ASC');
    } else {
        $t->db->order_by('p.row_order', 'ASC');
    }
    
    // LIMIT & OFFSET
    if ($limit !== null) {
        $t->db->limit($limit, $offset ?: 0);
    }
    
    if (!empty($return_count)) {
        // 只回傳 FILTER 後產品數量
        return $t->db->count_all_results();
    }
    
    $products = $t->db->get('products p')->result_array();
    
    if (!empty($product)) {
        $t->load->model('rating_model');
        for ($i = 0; $i < count($product); $i++) {
            
            $rating = $t->rating_model->fetch_rating($product[$i]['id'], '', 8, 0, 'pr.id', 'desc', '', 1);
            $product[$i]['review_images'] = (!empty($rating)) ? [$rating] : array();
    
            $product[$i]['tax_percentage'] = (isset($product[$i]['tax_percentage']) && intval($product[$i]['tax_percentage']) > 0) ? $product[$i]['tax_percentage'] : '0';
            $product[$i]['tax_id'] = ((isset($product[$i]['tax_id']) && intval($product[$i]['tax_id']) > 0) && $product[$i]['tax_id'] != "") ? $product[$i]['tax_id'] : '0';
            $product[$i]['attributes'] = get_attribute_values_by_pid($product[$i]['id']);
            // print_r($product[$i]['attributes']);
            // die;
            $product[$i]['variants'] = get_variants_values_by_pid($product[$i]['id']);
            $variants =   get_variants_values_by_pid($product[$i]['id']);
            $total_stock = 0;
            foreach ($variants as $variant) {
                $stock = (isset($variant['stock']) && !empty($variant['stock'])) ? $variant['stock'] : 0;
                $total_stock  += $stock;
                $product[$i]['total_stock'] = isset($total_stock) && !empty($total_stock) ? $total_stock : '';
            }
            $product[$i]['min_max_price'] = get_min_max_price_of_product($product[$i]['id']);
            $product[$i]['stock_type'] = isset($product[$i]['stock_type']) && ($product[$i]['stock_type'] != '') ? $product[$i]['stock_type'] : '';
            $product[$i]['stock'] = isset($product[$i]['stock']) && !empty($product[$i]['stock']) ? $product[$i]['stock'] : '';
            $product[$i]['relative_path'] = isset($product[$i]['image']) && !empty($product[$i]['image']) ? $product[$i]['image'] : '';
            $product[$i]['other_images_relative_path'] = isset($product[$i]['other_images']) && !empty($product[$i]['other_images']) ? json_decode($product[$i]['other_images']) : [];
            $product[$i]['video_relative_path'] = (isset($product[$i]['video']) && (!empty($product[$i]['video']))) ? $product[$i]['video'] : "";
            $product[$i]['video_type'] = isset($product[$i]['video_type']) && !empty($product[$i]['video_type']) ? $product[$i]['video_type'] : '';
            $product[$i]['attr_value_ids'] = isset($product[$i]['attr_value_ids']) && !empty($product[$i]['attr_value_ids']) ? $product[$i]['attr_value_ids'] : '';
            $product[$i]['made_in'] = isset($product[$i]['made_in']) && !empty($product[$i]['made_in']) ? $product[$i]['made_in'] : '';
            $product[$i]['hsn_code'] = isset($product[$i]['hsn_code']) && !empty($product[$i]['hsn_code']) ? $product[$i]['hsn_code'] : '';
            $product[$i]['brand'] = isset($product[$i]['brand']) && !empty($product[$i]['brand']) ? $product[$i]['brand'] : '';
    
            
            // echo "<pre>";
            // print_r($product[$i]);
            // die;
    
            $product[$i]['warranty_period'] = isset($product[$i]['warranty_period']) && !empty($product[$i]['warranty_period']) ? $product[$i]['warranty_period'] : '';
            $product[$i]['guarantee_period'] = isset($product[$i]['guarantee_period']) && !empty($product[$i]['guarantee_period']) ? $product[$i]['guarantee_period'] : '';
            $product[$i]['total_allowed_quantity'] = isset($product[$i]['total_allowed_quantity']) && !empty($product[$i]['total_allowed_quantity']) ? $product[$i]['total_allowed_quantity'] : '';
            $product[$i]['download_allowed'] = isset($product[$i]['download_allowed']) && !empty($product[$i]['download_allowed']) ? $product[$i]['download_allowed'] : '';
            $product[$i]['download_type'] = isset($product[$i]['download_type']) && !empty($product[$i]['download_type']) ? $product[$i]['download_type'] : '';
            $product[$i]['download_link'] = isset($product[$i]['download_link']) && !empty($product[$i]['download_link']) ? $product[$i]['download_link'] : '';
            $product[$i]['status'] = isset($product[$i]['status']) && !empty($product[$i]['status']) ? $product[$i]['status'] : '';
            $total_product = $t->db->query("select count(id) as total  from products where products.seller_id=" . $product[$i]['seller_id'] . " AND products.status='1'")->result_array();
    
            // outputing escaped data 
            $product[$i]['name'] = output_escaping($product[$i]['name']);
            $product[$i]['total_product'] = ($total_product[0]['total']);
            $product[$i]['store_name'] = output_escaping($product[$i]['store_name']);
            $product[$i]['seller_rating'] = (isset($product[$i]['seller_rating']) && !empty($product[$i]['seller_rating'])) ? output_escaping(number_format($product[$i]['seller_rating'], 1)) : 0;
            $product[$i]['store_description'] = (isset($product[$i]['store_description']) && !empty($product[$i]['store_description'])) ? output_escaping($product[$i]['store_description']) : "";
            $product[$i]['seller_profile'] = output_escaping(base_url() . $product[$i]['seller_profile']);
            $product[$i]['seller_name'] = output_escaping($product[$i]['seller_name']);
            $product[$i]['short_description'] = output_escaping($product[$i]['short_description']);
            $product[$i]['description'] = (isset($product[$i]['description']) && !empty($product[$i]['description'])) ? output_escaping($product[$i]['description']) : "";
            $product[$i]['extra_description'] = (isset($product[$i]['extra_description']) && !empty($product[$i]['extra_description']) && $product[$i]['extra_description'] != 'NULL') ? output_escaping($product[$i]['extra_description']) : "";
            $product[$i]['pickup_location'] = (isset($product[$i]['pickup_location']) && !empty($product[$i]['pickup_location']) && $product[$i]['pickup_location']) != 'NULL' ? $product[$i]['pickup_location'] : '';
    
            $product[$i]['seller_slug'] = isset($product[$i]['seller_slug']) && !empty($product[$i]['seller_slug']) ? output_escaping($product[$i]['seller_slug']) : "";
            $product[$i]['deliverable_type'] = $product[$i]['deliverable_type'];
            $product[$i]['deliverable_zipcodes_ids'] = output_escaping($product[$i]['deliverable_zipcodes']);
            if (isset($filter['discount']) && !empty($filter['discount']) && $filter['discount'] != "") {
                $product[$i]['cal_discount_percentage'] = output_escaping(number_format($product[$i]['cal_discount_percentage'], 2));
            }
            $product[$i]['cancelable_till'] = isset($product[$i]['cancelable_till']) && !empty($product[$i]['cancelable_till']) ? $product[$i]['cancelable_till'] : '';
            $product[$i]['is_attachment_required'] = isset($product[$i]['is_attachment_required']) && !empty($product[$i]['is_attachment_required']) ? $product[$i]['is_attachment_required'] : '0';
            $product[$i]['indicator'] = isset($product[$i]['indicator']) && !empty($product[$i]['indicator']) ? $product[$i]['indicator'] : '0';
            $product[$i]['deliverable_zipcodes_ids'] = isset($product[$i]['deliverable_zipcodes_ids']) && !empty($product[$i]['deliverable_zipcodes_ids']) ? $product[$i]['deliverable_zipcodes_ids'] : '';
            $product[$i]['rating'] = output_escaping(number_format($product[$i]['rating'], 2));
            $product[$i]['availability'] = isset($product[$i]['availability']) && ($product[$i]['availability'] != "") ? $product[$i]['availability'] : '';
            $product[$i]['sku'] = isset($product[$i]['sku']) && ($product[$i]['sku'] != "") ? $product[$i]['sku'] : '';
    
            // getting zipcodes from ids
            if ($product[$i]['deliverable_type'] != NONE && $product[$i]['deliverable_type'] != ALL) {
                $zipcodes = array();
                $zipcode_ids = explode(",", $product[$i]['deliverable_zipcodes_ids']);
                // $t->db->select('zipcode');
                // $t->db->where_in('id', $zipcode_ids);
                // $zipcodes = $t->db->get('zipcodes')->result_array();
                $zipcodes = array_column($zipcodes, "zipcode");
                $product[$i]['deliverable_zipcodes'] = implode(",", $zipcode_ids);
            } else {
                $product[$i]['deliverable_zipcodes'] = '';
            }
            $product[$i]['category_name'] = (isset($product[$i]['category_name']) && !empty($product[$i]['category_name'])) ? output_escaping($product[$i]['category_name']) : '';
            // check product delivrable or not 
            if ($is_deliverable != NULL) {
                $zipcode = fetch_details('zipcodes', ['zipcode' => $is_deliverable], 'id');
                if (!empty($zipcode)) {
                    $product[$i]['is_deliverable'] = is_product_delivarable($type = 'zipcode', $zipcode[0]['id'], $product[$i]['id']);
                } else {
                    $product[$i]['is_deliverable'] = false;
                }
            } else {
                $product[$i]['is_deliverable'] = false;
            }
    
            if ($product[$i]['deliverable_type'] == 1) {
                $product[$i]['is_deliverable'] = true;
            }
    
    
            $product[$i]['tags'] = (!empty($product[$i]['tags'])) ? explode(",", $product[$i]['tags']) : [];
    
            $product[$i]['video'] = (isset($product[$i]['video_type']) && (!empty($product[$i]['video_type']) || $product[$i]['video_type'] != NULL)) ? (($product[$i]['video_type'] == 'youtube' || $product[$i]['video_type'] == 'vimeo') ? $product[$i]['video'] : base_url($product[$i]['video'])) : "";
            $product[$i]['minimum_order_quantity'] = isset($product[$i]['minimum_order_quantity']) && (!empty($product[$i]['minimum_order_quantity'])) ? $product[$i]['minimum_order_quantity'] : 1;
            $product[$i]['quantity_step_size'] = isset($product[$i]['quantity_step_size']) && (!empty($product[$i]['quantity_step_size'])) ? $product[$i]['quantity_step_size'] : 1;
            if (!empty($product[$i]['variants'])) {
                $count_stock = array();
                $is_purchased_count = array();
                for ($k = 0; $k < count($product[$i]['variants']); $k++) {
    
                    $variant_other_images = $variant_other_images_sm = $variant_other_images_md = json_decode((string)$product[$i]['variants'][$k]['images'], 1);
    
                    if (!empty($variant_other_images[0]) && isset($variant_other_images[0])) {
    
                        $product[$i]['variants'][$k]['variant_relative_path'] = isset($product[$i]['variants'][$k]['images']) && !empty($product[$i]['variants'][$k]['images']) ? json_decode($product[$i]['variants'][$k]['images']) : [];
                        $counter = 0;
                        foreach ($variant_other_images_md as $row) {
                            $variant_other_images_md[$counter] = get_image_url($variant_other_images_md[$counter], 'thumb', 'md');
                            $counter++;
                        }
                        $product[$i]['variants'][$k]['images_md'] = isset($variant_other_images_md) && !empty($variant_other_images_md) ? $variant_other_images_md : "";
    
                        $counter = 0;
                        foreach ($variant_other_images_sm as $row) {
                            $variant_other_images_sm[$counter] = get_image_url($variant_other_images_sm[$counter], 'thumb', 'sm');
                            $counter++;
                        }
                        $product[$i]['variants'][$k]['images_sm'] = $variant_other_images_sm;
    
                        $counter = 0;
                        foreach ($variant_other_images as $row) {
                            $variant_other_images[$counter] = get_image_url($variant_other_images[$counter]);
                            $counter++;
                        }
                        $product[$i]['variants'][$k]['images'] = isset($variant_other_images) && !empty($variant_other_images) ? $variant_other_images : "";
                    } else {
                        $product[$i]['variants'][$k]['images'] = array();
                        $product[$i]['variants'][$k]['images_md'] = array();
                        $product[$i]['variants'][$k]['images_sm'] = array();
                        $product[$i]['variants'][$k]['variant_relative_path'] = array();
                    }
                    $product[$i]['variants'][$k]['swatche_type'] = (!empty($product[$i]['variants'][$k]['swatche_type'])) ? $product[$i]['variants'][$k]['swatche_type'] : "0";
                    $product[$i]['variants'][$k]['swatche_value'] = (!empty($product[$i]['variants'][$k]['swatche_value'])) ? $product[$i]['variants'][$k]['swatche_value'] : "0";
                    if (($product[$i]['stock_type'] == 0  || $product[$i]['stock_type'] == null)) {
                        if ($product[$i]['availability'] != null) {
                            $product[$i]['variants'][$k]['availability'] = $product[$i]['availability'];
                        }
                    } else {
                        $product[$i]['variants'][$k]['availability'] = ($product[$i]['variants'][$k]['availability'] != null) ? $product[$i]['variants'][$k]['availability'] : 1;
                        array_push($count_stock, $product[$i]['variants'][$k]['availability']);
                    }
                    if (($product[$i]['stock_type'] == 0)) {
                        $product[$i]['variants'][$k]['stock'] = isset($product[$i]['variants'][$k]['stock']) && !empty($product[$i]['variants'][$k]['stock']) ? get_stock($product[$i]['id'], 'product') : '';
                    } else {
                        $product[$i]['variants'][$k]['stock'] = isset($product[$i]['variants'][$k]['stock']) && !empty($product[$i]['variants'][$k]['stock']) ? get_stock($product[$i]['variants'][$k]['id'], 'variant') : '';
                    }
                    $percentage = (isset($product[$i]['tax_percentage']) && intval($product[$i]['tax_percentage']) > 0 && $product[$i]['tax_percentage'] != null) ? $product[$i]['tax_percentage'] : '0';
                    if ((isset($product[$i]['is_prices_inclusive_tax']) && $product[$i]['is_prices_inclusive_tax'] == 0) || (!isset($product[$i]['is_prices_inclusive_tax'])) && $percentage > 0) {
                        $price_tax_amount = $product[$i]['variants'][$k]['price'] * ($percentage / 100);
                        $product[$i]['variants'][$k]['price'] =  strval($product[$i]['variants'][$k]['price'] + $price_tax_amount);
                        $special_price_tax_amount = $product[$i]['variants'][$k]['special_price'] * ($percentage / 100);
                        $product[$i]['variants'][$k]['special_price'] =  strval($product[$i]['variants'][$k]['special_price'] + $special_price_tax_amount);
                    } else {
                        $product[$i]['variants'][$k]['price'] =  strval($product[$i]['variants'][$k]['price']);
                        $product[$i]['variants'][$k]['special_price'] =  strval($product[$i]['variants'][$k]['special_price']);
                    }
                    if (isset($user_id) && $user_id != NULL) {
                        $user_cart_data = $t->db->select('qty as cart_count')->where(['product_variant_id' => $product[$i]['variants'][$k]['id'], 'user_id' => $user_id, 'is_saved_for_later' => 0])->get('cart')->result_array();
                        if (!empty($user_cart_data)) {
                            $product[$i]['variants'][$k]['cart_count'] = $user_cart_data[0]['cart_count'];
                        } else {
                            $product[$i]['variants'][$k]['cart_count'] = "0";
                        }
                        $is_purchased = $t->db->where(['oi.product_variant_id' => $product[$i]['variants'][$k]['id'], 'oi.user_id' => $user_id, 'oi.active_status' => "delivered"])->order_by('oi.id', 'DESC')->limit(1)->get('order_items oi')->result_array();
    
                        if (!empty($is_purchased) && strtolower($is_purchased[0]['active_status']) == 'delivered') {
                            array_push($is_purchased_count, 1);
                            $product[$i]['variants'][$k]['is_purchased'] = 1;
                        } else {
                            array_push($is_purchased_count, 0);
                            $product[$i]['variants'][$k]['is_purchased'] = 0;
                        }
    
                        $user_rating = $t->db->select('rating,comment')->where(['user_id' => $user_id, 'product_id' => $product[$i]['id']])->get('product_rating')->result_array();
                        if (!empty($user_rating)) {
                            $product[$i]['user']['user_rating'] =   (isset($product[$i]['user']['user_rating']) && (!empty($product[$i]['user']['user_rating']))) ? $user_rating[0]['rating'] : '';
                            $product[$i]['user']['user_comment'] =   (isset($product[$i]['user']['user_comment']) && (!empty($product[$i]['user']['user_comment']))) ? $user_rating[0]['user_comment'] : '';
                        }
                    } else {
                        $product[$i]['variants'][$k]['cart_count'] = "0";
                    }
                }
            }
    
            $is_purchased_count = array_count_values($is_purchased_count);
            $is_purchased_count = array_keys($is_purchased_count);
            $product[$i]['is_purchased'] = (isset($is_purchased) && array_sum($is_purchased_count) == 1) ? true : false;
    
            $product[$i]['discount_amount'] = 0;
            $product[$i]['discount_percentage'] = 0;
            
            $variant_discount_amount = 0;
            $variant_discount_percentage = 0;
            foreach($product[$i]['variants'] as $variant){
                if($variant['special_price'] == 0) continue;
                $_curAmount = abs($variant['special_price'] - $variant['price']);
                $_curPercentage = find_discount_in_percentage($variant['special_price'], $variant['price']);
                if($_curAmount > $variant_discount_amount) $variant_discount_amount = $_curAmount;
                if($_curPercentage > $variant_discount_percentage) $variant_discount_percentage = $_curPercentage;
            }
            if($variant_discount_amount != 0) $product[$i]['discount_amount'] = $variant_discount_amount;
            if($variant_discount_percentage != 0) $product[$i]['discount_percentage'] = $variant_discount_percentage;
    
            if (($product[$i]['stock_type'] != null && !empty($product[$i]['stock_type']))) {
    
    
                //Case 2 & 3 : Product level(variable product) ||  Variant level(variable product)
                if ($product[$i]['stock_type'] == 1 || $product[$i]['stock_type'] == 2) {
                    $counts = array_count_values($count_stock);
                    $counts = array_keys($counts);
                    if (isset($counts)) {
                        $product[$i]['availability'] = array_sum($counts);
                    }
                }
            }
    
            if (isset($user_id) && $user_id != null) {
                $fav = $t->db->where(['product_id' => $product[$i]['id'], 'user_id' => $user_id])->get('favorites')->num_rows();
                $product[$i]['is_favorite'] = $fav;
            } else {
                $product[$i]['is_favorite'] = '0';
            }
    
            $product[$i]['image_md'] = get_image_url($product[$i]['image'], 'thumb', 'md');
            $product[$i]['image_sm'] = get_image_url($product[$i]['image'], 'thumb', 'sm');
            $product[$i]['image'] = get_image_url($product[$i]['image']);
            $other_images = $other_images_sm =  $other_images_md = json_decode($product[$i]['other_images'], 1);
    
            if (!empty($other_images)) {
    
                $k = 0;
                foreach ($other_images_md as $row) {
                    $other_images_md[$k] = get_image_url($row, 'thumb', 'md');
                    $k++;
                }
                $other_images_md = (array) $other_images_md;
                $other_images_md = array_values($other_images_md);
                $product[$i]['other_images_md'] = $other_images_md;
    
                $k = 0;
                foreach ($other_images_sm as $row) {
                    $other_images_sm[$k] = get_image_url($row, 'thumb', 'sm');
                    $k++;
                }
                $other_images_sm = (array) $other_images_sm;
                $other_images_sm = array_values($other_images_sm);
                $product[$i]['other_images_sm'] = $other_images_sm;
    
                $k = 0;
                foreach ($other_images as $row) {
                    $other_images[$k] = get_image_url($row);
                    $k++;
                }
                $other_images = (array) $other_images;
                $other_images = array_values($other_images);
                $product[$i]['other_images'] = $other_images;
            } else {
                $product[$i]['other_images'] = array();
                $product[$i]['other_images_sm'] = array();
                $product[$i]['other_images_md'] = array();
            }
            $tags_to_strip = array("table", "<th>", "<td>");
            $replace_with = array("", "h3", "p");
            $n = 0;
            foreach ($tags_to_strip as $tag) {
                // $product[$i]['description'] = output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$product[$i]['description']));
                $product[$i]['description'] = !empty($product[$i]['description']) ? output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$product[$i]['description'])) : "";
                $product[$i]['extra_description'] = !empty($product[$i]['extra_description']) && $product[$i]['extra_description'] != null ? output_escaping(str_replace('\r\n', '&#13;&#10;', (string)$product[$i]['extra_description'])) : "";
                $n++;
            }
            $variant_attributes = [];
            $attributes_array = explode(',', $product[$i]['variants'][0]['attr_name']);
    
            foreach ($attributes_array as $attribute) {
                $attribute = trim($attribute);
                $key = array_search($attribute, array_column($product[$i]['attributes'], 'name'), false);
                if (($key === 0 || !empty($key)) && isset($product[0]['attributes'][$key])) {
                    $variant_attributes[$key]['ids'] = $product[0]['attributes'][$key]['ids'];
                    $variant_attributes[$key]['values'] = $product[0]['attributes'][$key]['value'];
                    $variant_attributes[$key]['swatche_type'] = $product[0]['attributes'][$key]['swatche_type'];
                    $variant_attributes[$key]['swatche_value'] = $product[0]['attributes'][$key]['swatche_value'];
                    $variant_attributes[$key]['attr_name'] = $attribute;
                }
            }
            $product[$i]['variant_attributes'] = $variant_attributes;
        }
    
        if (isset($count_res[0]['cal_discount_percentage'])) {
            $dicounted_total = array_values(array_filter(explode(',', $count_res[0]['cal_discount_percentage'])));
        } else {
            $dicounted_total = 0;
        }
        $response['total'] = (isset($filter) && !empty($filter['discount'])) ? count($dicounted_total) : $count_res[0]['total'];
    
        array_push($attribute_values_ids, $count_res[0]['attr_value_ids']);
        $attribute_values_ids = implode(",", $attribute_values_ids);
        $attr_value_ids = array_filter(array_unique(explode(',', $attribute_values_ids)));
    }
    
    // 以下簡單回傳主要結果示範:
    $response = [];
    $response['min_price'] = $computed_min_price; // 不包含價格篩選時的最小價
    $response['max_price'] = $computed_max_price; // 不包含價格篩選時的最大價
    $response['product'] = $products;
    

    // exit(print_r($response));

    return $response;
    

    }

    我發現目前版本中回傳的tags並不是以Array回傳,而是文字。另外其他本應是Array的數據亦如此 請確保數據按原有類型回傳,如是Array應回傳Array

  6. Assistant

    感謝提供詳細程式碼。

    你目前問題是:
    tags、其他原本應為陣列的欄位,卻被當成字串或其他型態回傳,例如 tags 字串沒有被 explode 成陣列,其他圖片陣列沒解 json 等。


    以下我幫你示範如何確保這些欄位維持正確資料型態,並且附上重點修改說明:


    1. tags 要回傳陣列

    // $product[$i]['tags'] 目前可能是字串,改為
    $product[$i]['tags'] = (!empty($product[$i]['tags'])) ? explode(",", $product[$i]['tags']) : [];
    

    2. 其他原本是陣列的欄位,應該 json_decode

    例如:

    • other_images
    • variants裏面的 images
    • 你有用到的 other_images_sm, other_images_md
    • other_images_relative_path

    改用

    $product[$i]['other_images'] = !empty($product[$i]['other_images']) ? json_decode($product[$i]['other_images'], true) : [];
    // 另外你有 $other_images_md, $other_images_sm 是從 $other_images clone 後經加工
    // 這些都要確保是陣列
    

    建議在處理圖片時,一開始先判斷是否 json_decode 成陣列,再繼續處理。


    3. variants 的 images 欄位同理,也應該是陣列

    $variant_other_images = !empty($product[$i]['variants'][$k]['images']) ? json_decode($product[$i]['variants'][$k]['images'], true) : [];
    // 之後再多次轉換圖片URL時,直接用這個變數處理
    

    4. video 也要判斷轉換路徑(文字或空字串都要處理)

    $product[$i]['video'] = (isset($product[$i]['video_type']) && !empty($product[$i]['video_type']))
        ? (($product[$i]['video_type'] == 'youtube' || $product[$i]['video_type'] == 'vimeo') ? $product[$i]['video'] : base_url($product[$i]['video']))
        : "";
    

    5. 統整後你這部分修改範例(該段精簡示範)

    // tags 回傳陣列
    $product[$i]['tags'] = (!empty($product[$i]['tags'])) ? explode(",", $product[$i]['tags']) : [];
    
    // other_images 解 json
    $other_images = !empty($product[$i]['other_images']) ? json_decode($product[$i]['other_images'], true) : [];
    $product[$i]['other_images'] = is_array($other_images) ? $other_images : [];
    // 其他 other_images_sm, md 你可用類似方式初始化,並且加工 get_image_url
    
    // video 路徑處理
    $product[$i]['video'] = (isset($product[$i]['video_type']) && !empty($product[$i]['video_type']))
        ? (($product[$i]['video_type'] == 'youtube' || $product[$i]['video_type'] == 'vimeo') ? $product[$i]['video'] : base_url($product[$i]['video']))
        : "";
    
    // variants 裏 images
    if (!empty($product[$i]['variants'])) {
        for ($k = 0; $k < count($product[$i]['variants']); $k++) {
            $product[$i]['variants'][$k]['images'] = !empty($product[$i]['variants'][$k]['images']) ? json_decode($product[$i]['variants'][$k]['images'], true) : [];
            if (!is_array($product[$i]['variants'][$k]['images'])) {
                $product[$i]['variants'][$k]['images'] = [];
            }
            // 依此繼續處理 images_md, images_sm 等加工
        }
    }
    

    建議:

    你若想要整個函式安全回傳型態正確欄位,建議在資料擷取完成後($product = $t->db->get()->result_array()之後),專門跑一段函式,把上述需要『強制轉成陣列的欄位』做成統一處理,一次處理,不用每次都寫重複代碼。


    示範建立一個簡單的結構化整理函式:

    function format_product_result($products)
    {
        foreach ($products as &$product) {
            $product['tags'] = !empty($product['tags']) ? explode(',', $product['tags']) : [];
    
            $product['other_images'] = !empty($product['other_images']) ? json_decode($product['other_images'], true) : [];
            if (!is_array($product['other_images'])) {
                $product['other_images'] = [];
            }
    
            // video
            $product['video'] = (isset($product['video_type']) && !empty($product['video_type']))
                ? (($product['video_type'] == 'youtube' || $product['video_type'] == 'vimeo') ? $product['video'] : base_url($product['video']))
                : "";
    
            // variants
            if (!empty($product['variants'])) {
                for ($k = 0; $k < count($product['variants']); $k++) {
                    $product['variants'][$k]['images'] = !empty($product['variants'][$k]['images']) ? json_decode($product['variants'][$k]['images'], true) : [];
                    if (!is_array($product['variants'][$k]['images'])) {
                        $product['variants'][$k]['images'] = [];
                    }
    
                    // images_md, images_sm 若有加工邏輯,這裏可以補充
                }
            }
        }
        unset($product);
    
        return $products;
    }
    

    然後呼叫:

    $products = format_product_result($products);
    

    總結

    • 你的錯誤來源都是資料庫取出來的 JSON 字串沒經過 json_decode。
    • tags 沒 explode 為陣列。
    • 整批統一格式化後回傳,給前端完整且一致的資料型態。

    如果你需要,我可以幫你整合這個改動套入你的函式完整版本,只要告訴我。