Migration not populating virtuemart_product_categories 'ordering'

[AH]

The migration tool is working pretty well.

However product_categories  'ordering' field is not being populated

vm_product_category_xref 'product_list'

Is not being moved to

virtuemart_product_categories 'ordering'

Does anyone have any ideas how to solve?


I can see that product_list is not extracted in migrator.php

Code Select Expand

$q = 'SELECT `category_id` FROM #__vm_product_category_xref WHERE #__vm_product_category_xref.product_id = "'.$product['product_id'].'" ';
$this->_db->setQuery($q);
$productCats = $this->_db->loadResultArray();

$productcategories = array();
if(!empty($productCats)){
foreach($productCats as $cat){
//product has category_id and categories?
if(!empty($oldToNewCats[$cat])){
// $product['virtuemart_category_id'] = $oldToNewCats[$cat];
//This should be an array, or is it not in vm1? not cleared, may need extra foreach
$productcategories[] = $oldToNewCats[$cat];
} else {
vmInfo('Coulndt find category for product, maybe just not in a category');
}
}
}
// if(!empty($alreadyKnownIds[$product['product_id']])){
// $product_parent_id = $alreadyKnownIds[$product['product_id']];
// }
// Converting Attributes from parent product to customfields Cart variant
// $q = 'SELECT * FROM `#__vm_product_attribute` WHERE `#__vm_product_attribute`.`product_id` ="'.$product['product_id'].'" ';
// $this->_db->setQuery($q);
// if(!empty($productAttributes = $this->_db->loadAssocList()) {

// foreach($productAttributes as $attrib){
// //custom select or create it
// $q = 'SELECT `virtuemart_custom_id` FROM `#__virtuemart_customs` as c WHERE c.field_type ="V" and c.`custom_title` ="'.$attrib['attribute_name'].'" ';
// $this->_db->setQuery($q);
// if (!$virtuemart_custom_id = $this->_db->loadResult()) {
// $customModel = VmModel::getModel('Custom');
// $attrib['custom_title'] = $attrib['attribute_name'];
// $attrib['custom_value'] = $attrib['attribute_value'];
// $attrib['is_cart_attribute'] = '1';

// $customModel->store($attrib);
// }
// }
// }

// Attributes End
$product['categories'] = $productcategories;

[AH]

OK so I have looked a bit more into this and I can see that the function loadResultArray will not give me an array of values from the orginal tables

So by adding the additional select command and using the loadAssocList  I get the array

Code Select Expand


//                    $q = 'SELECT `category_id` FROM #__vm_product_category_xref WHERE #__vm_product_category_xref.product_id = "'.$product['product_id'].'" ';
$q = 'SELECT `category_id`, `product_list` as `ordering` FROM #__vm_product_category_xref WHERE #__vm_product_category_xref.product_id = "'.$product['product_id'].'" ';
$this->_db->setQuery($q);
                    $productCats = $this->_db->loadAssocList();
//                    $productCats = $this->_db->loadResultArray();

$productcategories = array();
if(!empty($productCats)){
foreach($productCats as $cat){
                            $oldcat = $cat['category_id'];
                            //product has category_id and categories?
if(!empty($oldToNewCats[$oldcat])){
// $product['virtuemart_category_id'] = $oldToNewCats[$cat];
//This should be an array, or is it not in vm1? not cleared, may need extra foreach
                                $productcategories[] = $oldToNewCats[$oldcat];
//need to get the ordering field into the array         = $cat['ordering']                     
} else {
vmInfo('Coulndt find category for product, maybe just not in a category');
}
}
}

                    $product['categories'] = $productcategories;

//need to find out how this writes to the dbase


But I cannot work out how to get the migrator.php to write anything [ordering] to the product_categories

heeellllpppppp, please.




Anyone?

[AH]

Not much point in progressing with a code fix so this is what I will be doing

This is only of use to those people who have set the category ordering of items in old VM1 and want to keep this sequence after migration (i.e. none of the "default" options work for you)

This will only work if the following is true for your migration:-
Old category ids and migrated category ids are the same (see http://forum.virtuemart.net/index.php?action=post;msg=415846;topic=121824.0)
You have Migrated products and kept the old product_ids (see http://forum.virtuemart.net/index.php?topic=121624.msg415847#msg415847 reply#10)

Applying the sql below will then update the "ordering" field to the original VM1 value

NOTE that this will only work if you have managed to keep your old product_id and category_id!!

Code Select Expand


update yourtable_virtuemart_product_categories x,  yourtable_vm_product_category_xref y
set x.ordering = y.product_list
where x.virtuemart_product_id = y.product_id AND x.virtuemart_category_id = y.category_id



Ye-ha!  Now I dont have to try and re-sequence the thousands of products I spent sequencing in the old VM

[mbarry]

I had a look at this one for you. The code simply hasn't been written to port across the `product_list` as the `ordering` field.
There are two files you will need to modify to get this to work and you were on the right track


administrator/components/com_virtuemart/helpers/migrator.php

function portProducts()

Code Select Expand


          // MJB added `product_list` to select to allow Migration of ordering field from VM1
$q = 'SELECT `category_id`, `product_list` FROM #__vm_product_category_xref WHERE #__vm_product_category_xref.product_id = "'.$product['product_id'].'" ';
$this->_db->setQuery($q);
$productCats = $this->_db->loadAssocList(); // MJB Change this from $this->_db->loadResultArray() to $this->_db->loadAssocList(); to account for array of select items

$productcategories = array();
$prodCatOrderings = array(); //MJB array of orderings mapping one-one with array of productcategories
if(!empty($productCats)){
foreach($productCats as $cat){  //MJB $cat will be an array of category_id, product_list
//product has category_id and categories?
if(!empty($oldToNewCats[$cat['category_id']])){  //MJB added 'category_id' index
// $product['virtuemart_category_id'] = $oldToNewCats[$cat]; //MJB commented this line out
//This should be an array, or is it not in vm1? not cleared, may need extra foreach
$productcategories[] = $oldToNewCats[$cat['category_id']]; //MJB added 'category_id' index since $cat is now an array
$prodCatOrderings[] = $cat['product_list']; //MJB populate the asociated ordering since they are populated in same loop as the productcategories they will be in sync
} else {
vmInfo('Couldnt find category for product, maybe just not in a category'.' : Product ID '.$product['product_id'].' Category checked : ID '.$cat);
}
}
}


$product['categories'] = $productcategories;
$product['ordering'] = $prodCatOrderings; //MJB added 'ordering' field

$product['published'] = $product['product_publish'] == 'Y' ? 1 : 0;




administrator/components/com_virtuemart/helpers/vmxarray.php

Code Select Expand


    public function store($updateNulls = false) {

    $returnCode = true;
      $this->setLoggableFieldsForStore();
      $db = JFactory::getDBO();

        $pkey = $this->_pkey;
        $skey = $this->_skey;
        $oKey = $this->_orderingKey; // MJB added ordering key
        $tblkey = $this->_tbl_key;

        // We select all database rows based on our _pkey
        $q  = 'SELECT * FROM `'.$this->_tbl.'` WHERE `'.$this->_pkey.'` = "'. $this->_pvalue.'" ';
        $this->_db->setQuery($q);
        $objList = $this->_db->loadObjectList();

        // We convert the database object list that we got in a more friendly array
        $oldArray = null;
        if($objList) {
            foreach($objList as $obj){
                $oldArray[] = array($pkey=>$obj->$pkey, $skey=>$obj->$skey);
            }
        }

        // We make another database object list with the values that we want to insert into the database
        $newArray = array();
        $newArrayOrder = array();
if(!empty($this->_svalue)){
            if(!is_array($this->_svalue)) $this->_svalue = array($this->_svalue);
            $i=0;                                                                                    //MJB ordering will be a matching array if _svalue is an array
            foreach($this->_svalue as $value) {
            $newArray[] = array($pkey=>$this->_pvalue, $skey=>$value, $oKey=>$this->ordering[$i]); //MJB create array with ordering values to insert or update
            $i++;
            }
}

        // Inserts and Updates
        if(count($newArray)>0){
            $myOrdering = 1;

            foreach ($newArray as $newValue) {
                // We search in the existing (old) rows to find one of the new rows we want to insert
                $newRow = array($pkey=>$newValue[$pkey], $skey=>$newValue[$skey]);                    //MJB find row based on Primary and Secondary keys only
                $result = $this->array_msearch($oldArray, $newRow);

                // We start creating the row we will insert or update
                $obj = new stdClass;
                $obj->$pkey = $newValue[$pkey];
                $obj->$skey = $newValue[$skey];
               
                if($this->_autoOrdering){
                    $obj->$oKey = $myOrdering++;
                }
                else {
                $obj->$oKey = $newValue[$oKey];   //MJB add supplied 'ordering' to the cross reference table
               }


[AH]

MJB

This works perfectly for migration and thank you!!!!!!!!!!

However

Post migration, the code causes the product ordering to become "messed" up when editing a product.  All category ordering xrefs for the product are set to a value not equal to their original values.

A fix to the productedit admin template "fix" has been applied in 2.5.1 that may sort this out with no changes to the above code required