Determining Grouped product/s from Child Product

[thenbrent]

We've just come across the changes to the Grouped product relationship in 3.0. Especially this part mentioned in the release blog post:

Grouped products are linked from the parent rather than the children. Children can be in more than one group.

While this change to the relationship is a good idea, the implementation makes it cumbersome to determine the grouped product ID/s from the child product.

In WC 2.6, it was possible to simply look at a child product's post_parent to determine the Grouped product's ID.

Because the relationship is now recorded as a serialized array against the parent in post meta, it appears necessary to do a query searching for serialized data containing the child product's ID. e.g. for a product with ID 83, you'd need to run a query like:

SELECT post_id FROM wp_postmeta WHERE meta_key = '_children' AND meta_value LIKE '%i:83;%'

(You can't just search for the ID, because you might get results where the ID is a subset of another ID, e.g. results for a Grouped product with child ID 183 when searching for child product ID 18).

Is there a better way to find the parents from a child in 3.0?

FWIW, there's also no mention of this change in the 2.6.x to 3.0.0 Developer Migration Notes

UPDATED: clarified many-to-many relationships between parent/child weren't an issue, but still needed a way to find the parent or parents of the child.

[mikejolley]

While this change to the relationship is a good idea, the implementation makes it cumbersome to determine a grouped product ID/s from the child product.

The whole idea is to make this a many to 1 relationship. There is not way to determine the parent, because there can be multiple parents.

[mikejolley]

@claudiosanches We need to move this relationship information out of meta into a custom table (product_relationships?) and give it more thought in a future release. Having a separate table would mean we could have a get_parents method in the data store without horrible serialised data queries and would make Brents life easier.

Since this is likely breaking (if using meta directly) this should be a 4.0 item.

[claudiosanches]

@mikejolley I like the idea of product_relationships table.

[manospsyx]

Product Bundles moved from serialized meta to dedicated tables since v5.0 for the same reason @thenbrent is mentioning.

Developers needed an easy way to find which "bundles" a product belonged to for a variety of reasons, and we needed a performant way to query/sync availability data between bundles & bundled products.

We need to move this relationship information out of meta into a custom table (product_relationships?) and give it more thought in a future release.

See https://docs.woocommerce.com/document/bundles/bundles-data-structures-storage/

It's probably too late for PB to move its data to a WC core table like this, but there are quite a few extensions that could benefit for this structure. Mix and Match is one that comes to mind.

[WillBrubaker]

On a related note, does the change to grouped products cause this to be obsolete: https://github.com/woocommerce/woocommerce/blob/3.0.3/includes/admin/class-wc-admin-post-types.php#L346-L348

[mikejolley]

Yes and no; there may be cases where parents are used.. but this change will stop it appearing after future updates #14521

[WillBrubaker]

I guess what I'm saying is that the parent is always 0 so no arrow is displayed currently (at least not in my testing) as well as ticket: 561135-zd-woothemes

[mikejolley]

@WillBrubaker Thats correct, it's obsolete for grouped products. I'm just saying there may be other use cases (not grouped products) where that could be used. We can remove it in 3.1.

[mikejolley]

Note, once this table is in place we'll be able to tackle #14903 too.

[ascottmccauley]

So just catching up, is there a way to get any/all of the parent product IDs from just a child product? As an example of necessity I am using setting children products to visibility->search and only showing the parent products in the catalog, which prevents the product_permalink from showing up. I am working on setting up a filter on woocommerce_cart_item_permalink to link to the parent product, but am settling for the individual product.

[sftsk]

If anybody needs a workaround in the meantime:

/**
 * Get the first parent of simple product, WIP until better solution: https://github.com/woocommerce/woocommerce/issues/14217
 * @param  int $prod_id id of simple product
 * @param  bool $plain_id return id or post object
 * @return post or id of grouped product
 */
function wc_get_first_parent($prod_id, $plain_id = true) {
  $group_args = array(
    'post_type' => 'product',
    'meta_query' => array(
      array(
        'key' => '_children',
        'value' => 'i:' . $prod_id . ';',
        'compare' => 'LIKE',
      )
    )
   );
  $parents = get_posts( $group_args );
  $ret_prod = count($parents) > 0 ? array_shift($parents) : false;
  if ($ret_prod && $plain_id) {
    $ret_prod = $ret_prod->ID;
  }
  return $ret_prod;
}

It looks up the first parent it can find from the database, obviously not great from a performance point of view.

Use at your own risk and let's hope we don't have to wait for a proper solution for too long.

[Ciantic]

Any performance tips on @sftsk's implementation are welcome. I rely on reverse lookups like this.

One tip: If you only need ID the get_posts should have fields, e.g.

    private static function wc_get_first_parent($prod_id) {
     $group_args = array(
       'post_type' => 'product',
       'meta_query' => array(
         array(
           'key' => '_children',
           'value' => 'i:' . $prod_id . ';',
           'compare' => 'LIKE',
         )
         ),
         'fields' => 'ids' // THIS LINE FILTERS THE SELECT SQL
      );
     $parents = get_posts( $group_args );
     return count($parents) > 0 ? array_shift($parents) : false;
   }

In my page all child products redirect to parent product, and all child products are listed in the parent product page. So I need to do this everytime I redirect the user, not a big deal perhaps but still.

I also need to filter child products away from the main listings, so there needs to be some really performant way to do it.

Having own mysql table would be the best way to do this in future, it should support fast queries forward from parent to child, and in reverse lookups.

[cinnabarstudio]

Tried the temp fix posted by Ciantic with a result of no change and still no grouped product titles in my order emails and or order listing in the dashboard. Not sure what I am doing wrong. Ciantic, can you assist? Really need this fix.

[mikejolley]

Just to close the loop here, the workarounds posted above are the only way to do this right now.

We are pushing ahead with a new 'relationships' table as we move meta data to custom tables. We're working on this as a feature plugin here https://github.com/woocommerce/woocommerce-product-tables-feature-plugin (currently private at time of writing, we'll open it up soon with an announcement on the dev blog).

Essentially the table will link products to other products and will be use for upsells, crossells, and parent/child relationships for both grouped and variable products.

The feature plugin will be released publicly before deciding on a core version (major) to include it in.

Stay tuned to the dev blog :)

[Ciantic]

Btw, I also created a plugin for this while back:

<?php 
/*
MIT LICENSE Copyright (C) 2017 Jari Otto Oskari Pennanen

*/

// This pluginish thing creates a {prefix}woocommerce_wcgr_group_relationships
// table which is populated automatically when post meta changes.
// 
// Table consist of only two columns: parent_id, child_id

// https://github.com/woocommerce/woocommerce/issues/14217

// TODO: https://codex.wordpress.org/Creating_Tables_with_Plugins

define("WCGR_VERSION", "0.4.1");


/**
 * Create table
 */
function _wcgr_create_table() {
    global $wpdb;
    $table_name = "{$wpdb->prefix}wcgr_relationship";
    $charset_collate = $wpdb->get_charset_collate();

    $drop_existing_sql = "DROP TABLE IF EXISTS `$table_name`;";
    
    $table_sql = "CREATE TABLE `$table_name` (
      parent_id bigint(20) unsigned NOT NULL COMMENT 'WooCommerce post parent id',
      child_id bigint(20) unsigned NOT NULL COMMENT 'WooCommerce post child id',
      PRIMARY KEY (parent_id, child_id),
      INDEX `{$table_name}_child_id_idx` (child_id)
    ) $charset_collate  COMMENT 'WCGR_VERSION=".WCGR_VERSION."';";

    $wpdb->query($drop_existing_sql);
    $wpdb->query($table_sql);
}

/**
 * Refresh the wcgr_relationship table from a WooCommerce
 */
function _wcgr_refresh_table() {
    global $wpdb;

    // Get relationship from old format (meta_value) WC 3.x
    $results = $wpdb->get_results(
        "select p.ID, m.meta_value from 
        `{$wpdb->prefix}posts` as p, wp_postmeta as m 
        where post_type = 'product' 
            and m.meta_key = '_children' 
            and p.id = m.post_id");

    // Insert to new table in batch
    $insert_rows_cleaned = array();
    foreach ($results as $row) {
        $children = unserialize($row->meta_value);
        if ($children && is_array($children)) {
            foreach ($children as $child_id) {              
                $insert_rows_cleaned[] = $wpdb->prepare("(%d, %d)", 
                    intval($row->ID), intval($child_id));
                // TOO SLOW:
                //
                // $wpdb->insert("{$wpdb->prefix}wcgr_relationship", array(
                //     "parent_id" => intval($row->ID),
                //     "child_id" => intval($child_id)
                // ));
            }
        }
    }

    // Truncate old results
    $wpdb->query("TRUNCATE TABLE `{$wpdb->prefix}wcgr_relationship`");
    
    // This may have issue with seriously big tables, since query size limit may
    // be encountered
    $wpdb->query("
        INSERT INTO {$wpdb->prefix}wcgr_relationship (parent_id, child_id) 
        VALUES " . implode (",\n", $insert_rows_cleaned));
}

/**
 * Triggers the refresh when children changes
 */
function _wcgr_post_meta_hook($meta_id, $object_id, $meta_key, $_meta_value) {
    if ($meta_key === "_children") {
        _wcgr_refresh_table();
        // TODO: Can we be more clever?
    }
}

/**
 * Initialize the database
 */
function _wcgr_init() {
    // Recreate the table if version has changed or it does not exist
    if (get_option("WCGR_VERSION") !== WCGR_VERSION) {
        _wcgr_create_table();
        _wcgr_refresh_table();

        // Make sure WCGR_VERSION is autoloaded (the true parameter) for performance
        update_option("WCGR_VERSION", WCGR_VERSION, true);
    }
}

add_action("after_setup_theme", '_wcgr_init');
add_action("updated_post_meta", '_wcgr_post_meta_hook', 10, 4);
add_action("added_post_meta", '_wcgr_post_meta_hook', 10, 4);