0

I'v created function to get all products of a category, and remove product with same meta_value (CIP). All works fine, but if category have more than 100 products my query is very very slow... for 2300 products times to view result are 35" or more...

What is wrong ?

function get_all_product_in_selected_category($atts){
// Get the category
$catsearched = $atts['category_id'];

// Get all product of categoryselected 
$product_args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'fields' => 'ids',  // Only return product IDs
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $catsearched,
            'operator' => 'IN',
        ))
);

$products = get_posts($product_args);

if(count($products) > 0)
{
    // Search CIP by product ID on category
    foreach ( $products as $id )
    {
        $cip = $product_obj['product_cip']=get_post_meta($id,'product_cip');
        //echo 'Cip = '.$cip[0].', ';
        $arrayCip[] = $cip[0];
    }
    //echo '<b>TotalNumberOfCIP = '.count($arrayCip).'</b>';

    // Remove same cip from category
    $result = array_unique($arrayCip);

    //print_r($result);
    //echo '<b>TotalNumberOfUniqueCIP = '.count($result).'</b>';

    // Get productID by CIP
    foreach ($result as $v)
    {
        $product = array
        (
            'post_type' => 'product',
            'posts_per_page'=> -1,
            'meta_query' => array
            (
                array(
                    'key' => 'product_cip',
                    'value' => $v,
                    'compare'=> '='
                )
            ),
            'fields' => 'ids'
        );
        $product_post = get_posts($product);
        //echo 'Uniques CIP = '.$product_post[0].', ';

        $a[] = $product_post[0];
        //echo count($product_post);
    }

    // If products exist, show products
    if(count($a) > 0)
    {
        $product = array
        (
            'post_type' => 'product',
            'posts_per_page'=> -1,
            'meta_key' => 'product_cip',
            'post__in' => $a
        );

        $allproduct = get_posts($product);
        foreach($allproduct as $allp) {
            $arrayall[] = $allp->ID;
        }

        $result = implode(",", $arrayall);

        return $result;
    }
    else
    {
        return 0;
    }
}}

If I only use this :

 // Get all product of categoryselected
$product_args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'fields' => 'ids',  // Only return product IDs
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $catsearched,
            'operator' => 'IN',
        ))
);

$products = get_posts($product_args);

$result = implode(",", $products);

return $result;

Time is very fast ! but I need to filter product by meta_value (CIP) too

Thanks,

EDIT:

I'v modified my function like this:

// Get the category
$catsearched = $atts['category_id'];

// Get all product of categoryselected
$product_args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'fields' => 'ids',  // Only return product IDs
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $catsearched,
            'operator' => 'IN',
        ))
);

$products = get_posts($product_args);

foreach ( $products as $id )
{
    $cip = $product_obj['product_cip']=get_post_meta($id,'product_cip');
    //echo 'Cip = '.$cip[0].', ';
    $arrayCip[] = $cip[0];
}
//echo '<b>TotalNumberOfCIP = '.count($arrayCip).'</b><br>';

$test = array_unique($arrayCip);

//echo '<b>TotalNumberOfUniqueCIP = '.count($test).'</b><br>';

foreach($test as $id)
{
    $array[] = get_post_id_by_meta_key_and_value('product_cip', $id);
}

echo '<b>TotalNumberOfUniqueID = '.count($array).'</b><br>';

$array = implode(",", $products);

return $array;

and added this function:

function get_post_id_by_meta_key_and_value($key, $value) {
global $wpdb;
$meta = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM ".$wpdb->postmeta." WHERE meta_key=%s AND meta_value=%s", $key, $value ) );
if (is_array($meta) && !empty($meta) && isset($meta[0])) {
    $meta = $meta[0];
}
if (is_object($meta)) {
    return $meta->post_id;
}
else {
    return false;
}

}

Problem are same, the foreach($test as $id) { $array[] = get_post_id_by_meta_key_and_value('product_cip', $id); } are very slow !

1 Answer 1

0

So you want only the "first" products having a unique product_cip? I think a direct MySQL Query may be quicker:

function get_all_product_in_selected_category($atts){
// Get the category
if(!(isset($atts['category_id']) || !is_numeric($atts['category_id'])){
   return 0;
}
$catsearched = $atts['category_id'];
global $wpdb;
$productssql = "SELECT min($wpdb->posts.ID), meta_value 
                FROM $wpdb->posts 
                INNER JOIN $wpdb->term_relationships 
                           ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) 
                INNER JOIN $wpdb->term_taxonomy 
                           ON ($wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id) 
                INNER JOIN $wpdb->postmeta 
                           ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) 
                WHERE 
                           ($wpdb->postmeta.meta_key = 'product_cip')
                AND ($wpdb->posts.post_type = 'product') 
                AND ($wpdb->post_status = 'publish') 
                AND ($wpdb->term_taxonomy.taxonomy = 'product_cat')
                AND ($wpdb->term_taxonomy.term_id = %d)
                GROUP BY $wpdb->postmeta.meta_value";
$results = $wpdb->get_results($wpdb->prepare($productssql,$catsearched));
$a = array();
if($results){    
     foreach($results as $result){
         $a[] = $result->ID;
     }
     return implode(',',$a);
}
return 0;
}

Obviously didn't test this ;)

5
  • Thanks for help, actualy I try to get all product of category, and if a product have same CIP, keep only one, delete other product with same CIP. Actualy your Sql return Null
    – ilanb
    Apr 10, 2018 at 8:20
  • So you need to completely delete the duplicate product? How do you decide, which of the duplicate products shall be deleted? Are these exactly the same? Apr 10, 2018 at 8:21
  • In my code, I only keep the first product of same CIP, all other are same and need to be deleted
    – ilanb
    Apr 10, 2018 at 8:24
  • I have modified my function but no effect, foreach is very slow
    – ilanb
    Apr 11, 2018 at 22:58
  • Any one ? tried to add category to but null result...
    – ilanb
    Apr 14, 2018 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.