« Posts tagged Work

Magento – Products showing in the wrong category

I came across something odd a while back, and until recently not had time to investigate the issue.

Products were showing up in categories that they were not explicitly in.

In summary, when you reindex catalog_category_product Magento goes and looks at all the products and the categories that they are in. For the categories that the product are in, it gets the category tree of that category.

So consider this category hierarchy:

  1. Category A
    1. Sub Category A 1
      1. Bottom Category 1
      2. Bottom Category 2
    2. Sub Category A 2
      1. Bottom Category 3
      2. Bottom Category 4
    3. Sub Category A 3
      1. Bottom Category 5
      2. Bottom Category 6
  2. Category B
    1. Sub Category B 1
      1. Bottom Category 7
      2. Bottom Category 8
    2. Sub Category B 2
      1. Bottom Category 9
      2. Bottom Category 10
    3. Sub Category B 3
      1. Bottom Category 11
      2. Bottom Category 12

Now you create a product and put it in Bottom Category 9 and Bottom Category 11. When you reindex it’s put into the list to show up in the category and the parents on that tree, so Category B and Category B 2, and so on for Bottom Category 11.

Now over time we add more products and more categories, and several months later we decide to rearrange our category tree, as we’ve gotten too wide and our needs have changed, or some other reason. Say to something like this, a strikethru indicates a disabled category (as in is_active is 0 (it’s a boolean integer))

  1. Category A
    1. Sub Category A 1
      1. Bottom Category 1
      2. Bottom Category 2
      3. Bottom Category 3
      4. Bottom Category 4
    2. Sub Category A 2
    3. Sub Category A 3
      1. Bottom Category 5
      2. Bottom Category 6
  2. Category B
    1. Sub Category B 1
      1. Bottom Category 7
      2. Bottom Category 8
    2. Sub Category B 2
      1. Bottom Category 9
      2. Bottom Category 10
    3. Sub Category B 3
      1. Bottom Category 11
      2. Bottom Category 12

Now of course we reindex, and the problems start.

The Problem

We have a problem, the Product correctly shows in Bottom Category 11 and it’s parents, but it is still showing up in Sub Category A 1 and it’s parents in error.

It seems that the reindexer takes categories that are disabled (as in is_active is false), and their tree, into account when building the list of category pages that a product can be displayed on.

So in order to fix this I went deep into Magento core to go and see which query was at fault. Turns out that it’s not exactly broken, it’s just Magento doesn’t check for it a give category is active or not when it builds the list.

The query at fault is:

INSERT INTO `catalog_category_anc_products_index_idx` (`category_id`, `product_id`, `position`) SELECT STRAIGHT_JOIN DISTINCT `ca`.`category_id`, `cp`.`product_id`, MIN(IF(ca.category_id = ce.entity_id, `cp`.`position`, (`ce`.`position` + 1) * (`ce`.`level` + 1 * 10000) + `cp`.`position`)) AS `position` FROM `catalog_category_anc_categs_index_idx` AS `ca`
 INNER JOIN `catalog_category_entity` AS `ce` ON `ce`.`path` LIKE `ca`.`path` OR ce.entity_id = ca.category_id
 INNER JOIN `catalog_category_product` AS `cp` ON cp.category_id = ce.entity_id
 INNER JOIN `catalog_category_product_index_enbl_idx` AS `pv` ON pv.product_id = cp.product_id GROUP BY `ca`.`category_id`,
	`cp`.`product_id`

As you can see it just collects from catalog_category_entity, which has a table structure of:

mysql> describe catalog_category_entity;
+------------------+----------------------+------+-----+---------+----------------+
| Field            | Type                 | Null | Key | Default | Extra          |
+------------------+----------------------+------+-----+---------+----------------+
| entity_id        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| entity_type_id   | smallint(5) unsigned | NO   |     | 0       |                |
| attribute_set_id | smallint(5) unsigned | NO   |     | 0       |                |
| parent_id        | int(10) unsigned     | NO   |     | 0       |                |
| created_at       | timestamp            | YES  |     | NULL    |                |
| updated_at       | timestamp            | YES  |     | NULL    |                |
| path             | varchar(255)         | NO   | MUL | NULL    |                |
| position         | int(11)              | NO   |     | 0       |                |
| level            | int(11)              | NO   | MUL | 0       |                |
| children_count   | int(11)              | NO   |     | 0       |                |
+------------------+----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

As you can see that table structure is somewhat limited. Since this is the bare bones basics of a category. And thus the query to collect categories of a product is in, considers inactive categories as if doesn’t have this information.

The Solution

The fix here is simple. Find where the query is defined and take into account the is_active category attribute, and we’ll do it the Magento way. Rather than hardcoding entity_id’s.

A quick note about how I found the query. I opened up public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php and changed the variable $_debug from false to true, it’s around about line 103. This will output all the SQL queries to public_html/var/debug/pdo_mysql.log.

After that I just grep’ed the source catalog_category_anc_products_index_idx which told me where the table was defined in a xml config, and then grep’ed again for the internal reference name, (there was much much grep’ing for bit’s and pieces).

The query runs under the reindexAll function of Mage_Catalog_Model_Resource_Category_Indexer_Product. So basically take the ENTIRE contents of this function and setup a Model Resource override, and fix the query.

Module Basics

Creating our Module in the directory local/Company/Catalog

Normally I wouldn’t cover this, but we are overriding a resource which is a little different from just overriding a model.

etc/config.xml
<?xml version="1.0"?>
<config>
    <modules>
        <Company_Catalog>
            <version>0.0.1</version>
        </Company_Catalog>
    </modules>

    <global>
        <models>
            <companycatalog>
                <class>Company_Catalog_Model</class>
            </companycatalog>

            <catalog_resource_eav_mysql4>
                <rewrite>
                    <category_indexer_product>Company_Catalog_Model_Resource_Category_Indexer_Product</category_indexer_product>
                </rewrite>
            </catalog_resource_eav_mysql4>
        </models>
    </global>
</config>
Model/Resource/Category/Indexer/Product.php

Literally just a copy of the contents of reindexAll with some modifications. Starting at // Query Correction and ending at // end Query Correction.

<?php

class Company_Catalog_Model_Resource_Category_Indexer_Product extends Mage_Catalog_Model_Resource_Category_Indexer_Product
{
    /**
     * Rebuild all index data
     *
     * @return Mage_Catalog_Model_Resource_Category_Indexer_Product
     */
    public function reindexAll()
    {
        $this->useIdxTable(true);
        $this->beginTransaction();
        try {
            $this->clearTemporaryIndexTable();
            $idxTable = $this->getIdxTable();
            $idxAdapter = $this->_getIndexAdapter();
            $stores = $this->_getStoresInfo();
            /**
             * Build index for each store
             */
            foreach ($stores as $storeData) {
                $storeId    = $storeData['store_id'];
                $websiteId  = $storeData['website_id'];
                $rootPath   = $storeData['root_path'];
                $rootId     = $storeData['root_id'];
                /**
                 * Prepare visibility for all enabled store products
                 */
                $enabledTable = $this->_prepareEnabledProductsVisibility($websiteId, $storeId);
                /**
                 * Select information about anchor categories
                 */
                $anchorTable = $this->_prepareAnchorCategories($storeId, $rootPath);
                /**
                 * Add relations between not anchor categories and products
                 */
                $select = $idxAdapter->select();
                /** @var $select Varien_Db_Select */
                $select->from(
                    array('cp' => $this->_categoryProductTable),
                    array('category_id', 'product_id', 'position', 'is_parent' => new Zend_Db_Expr('1'),
                        'store_id' => new Zend_Db_Expr($storeId))
                )
                ->joinInner(array('pv' => $enabledTable), 'pv.product_id=cp.product_id', array('visibility'))
                ->joinLeft(array('ac' => $anchorTable), 'ac.category_id=cp.category_id', array())
                ->where('ac.category_id IS NULL');

                $query = $select->insertFromSelect(
                    $idxTable,
                    array('category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'),
                    false
                );
                $idxAdapter->query($query);

                /**
                 * Assign products not associated to any category to root category in index
                 */

                $select = $idxAdapter->select();
                $select->from(
                    array('pv' => $enabledTable),
                    array(new Zend_Db_Expr($rootId), 'product_id', new Zend_Db_Expr('0'), new Zend_Db_Expr('1'),
                        new Zend_Db_Expr($storeId), 'visibility')
                )
                ->joinLeft(array('cp' => $this->_categoryProductTable), 'pv.product_id=cp.product_id', array())
                ->where('cp.product_id IS NULL');

                $query = $select->insertFromSelect(
                    $idxTable,
                    array('category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'),
                    false
                );
                $idxAdapter->query($query);

                /**
                 * Prepare anchor categories products
                 */
                $anchorProductsTable = $this->_getAnchorCategoriesProductsTemporaryTable();
                $idxAdapter->delete($anchorProductsTable);

                $position = 'MIN('.
                    $idxAdapter->getCheckSql(
                        'ca.category_id = ce.entity_id',
                        $idxAdapter->quoteIdentifier('cp.position'),
                        '('.$idxAdapter->quoteIdentifier('ce.position').' + 1) * '
                        .'('.$idxAdapter->quoteIdentifier('ce.level').' + 1 * 10000)'
                        .' + '.$idxAdapter->quoteIdentifier('cp.position')
                    )
                .')';

                // Query Correction

                // load the is_active category attribute
                $attributeModel = Mage::getModel('eav/entity_attribute')->loadByCode(Mage_Catalog_Model_Category::ENTITY, 'is_active');
                $attributeTable = 'catalog_category_entity_' . $attributeModel->getBackendType();

                $select = $idxAdapter->select()
                ->useStraightJoin(true)
                ->distinct(true)
                ->from(array('ca' => $anchorTable), array('category_id'))
                ->joinInner(
                    array('ce' => $this->_categoryTable),
                    $idxAdapter->quoteIdentifier('ce.path') . ' LIKE ' .
                    $idxAdapter->quoteIdentifier('ca.path') . ' OR ce.entity_id = ca.category_id',
                    array()
                )

                ->joinInner(
                    array('cp' => $this->_categoryProductTable),
                    'cp.category_id = ce.entity_id',
                    array('product_id')
                )

                // left join the attribute
                ->joinInner(
                    array('ccei' => $attributeTable),
                    'ccei.entity_id = cp.category_id',
                    array()//select nothing
                )

                ->joinInner(
                    array('pv' => $enabledTable),
                    'pv.product_id = cp.product_id',
                    array('position' => $position)
                )

                ->where('ccei.attribute_id=' . $attributeModel->getAttributeId())//is_active
                ->where('ccei.value=1')

                ->group(array('ca.category_id', 'cp.product_id'));
                $query = $select->insertFromSelect($anchorProductsTable,
                    array('category_id', 'product_id', 'position'), false);
                $idxAdapter->query($query);

                // end Query Correction

                /**
                 * Add anchor categories products to index
                 */
                $select = $idxAdapter->select()
                ->from(
                    array('ap' => $anchorProductsTable),
                    array('category_id', 'product_id',
                        'position', // => new Zend_Db_Expr('MIN('. $idxAdapter->quoteIdentifier('ap.position').')'),
                        'is_parent' => $idxAdapter->getCheckSql('cp.product_id > 0', 1, 0),
                        'store_id' => new Zend_Db_Expr($storeId))
                )
                ->joinLeft(
                    array('cp' => $this->_categoryProductTable),
                    'cp.category_id=ap.category_id AND cp.product_id=ap.product_id',
                    array()
                )
                ->joinInner(array('pv' => $enabledTable), 'pv.product_id = ap.product_id', array('visibility'));

                $query = $select->insertFromSelect(
                    $idxTable,
                    array('category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'),
                    false
                );
                $idxAdapter->query($query);

                $select = $idxAdapter->select()
                    ->from(array('e' => $this->getTable('catalog/product')), null)
                    ->join(
                        array('ei' => $enabledTable),
                        'ei.product_id = e.entity_id',
                        array())
                    ->joinLeft(
                        array('i' => $idxTable),
                        'i.product_id = e.entity_id AND i.category_id = :category_id AND i.store_id = :store_id',
                        array())
                    ->where('i.product_id IS NULL')
                    ->columns(array(
                        'category_id'   => new Zend_Db_Expr($rootId),
                        'product_id'    => 'e.entity_id',
                        'position'      => new Zend_Db_Expr('0'),
                        'is_parent'     => new Zend_Db_Expr('1'),
                        'store_id'      => new Zend_Db_Expr($storeId),
                        'visibility'    => 'ei.visibility'
                    ));

                $query = $select->insertFromSelect(
                    $idxTable,
                    array('category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'),
                    false
                );

                $idxAdapter->query($query, array('store_id' => $storeId, 'category_id' => $rootId));
            }

            $this->syncData();

            /**
             * Clean up temporary tables
             */
            $this->clearTemporaryIndexTable();
            $idxAdapter->delete($enabledTable);
            $idxAdapter->delete($anchorTable);
            $idxAdapter->delete($anchorProductsTable);
            $this->commit();
        } catch (Exception $e) {
            $this->rollBack();
            throw $e;
        }
        return $this;
    }
}

The fix in depth

The first thing done here is to Magento Safe load the Category Attribute details for is_active.

                // load the is_active category attribute
                $attributeModel = Mage::getModel('eav/entity_attribute')->loadByCode(Mage_Catalog_Model_Category::ENTITY, 'is_active');
                $attributeTable = 'catalog_category_entity_' . $attributeModel->getBackendType();

After this join,

                ->joinInner(
                    array('cp' => $this->_categoryProductTable),
                    'cp.category_id = ce.entity_id',
                    array('product_id')
                )

we need to add another join, in this case we are joining to the table that contains the is_active information, it’s usually catalog_category_entity_int but we Magento Safe loaded it above.

                // left join the attribute
                ->joinInner(
                    array('ccei' => $attributeTable),
                    'ccei.entity_id = cp.category_id',
                    array()//select nothing
                )

Finally we need to adjust to add a where to clean up the join and select active categories only, after the last join and before the group function call:

                ->where('ccei.attribute_id=' . $attributeModel->getAttributeId())//is_active
                ->where('ccei.value=1')

This changes the query to:

INSERT INTO `catalog_category_anc_products_index_idx` (`category_id`, `product_id`, `position`) SELECT STRAIGHT_JOIN DISTINCT `ca`.`category_id`, `cp`.`product_id`, MIN(IF(ca.category_id = ce.entity_id, `cp`.`position`, (`ce`.`position` + 1) * (`ce`.`level` + 1 * 10000) + `cp`.`position`)) AS `position` FROM `catalog_category_anc_categs_index_idx` AS `ca`
 INNER JOIN `catalog_category_entity` AS `ce` ON `ce`.`path` LIKE `ca`.`path` OR ce.entity_id = ca.category_id
 INNER JOIN `catalog_category_product` AS `cp` ON cp.category_id = ce.entity_id
 INNER JOIN `catalog_category_entity_int` AS `ccei` ON ccei.entity_id = cp.category_id
 INNER JOIN `catalog_category_product_index_enbl_idx` AS `pv` ON pv.product_id = cp.product_id WHERE (ccei.attribute_id=119) AND (ccei.value=1) GROUP BY `ca`.`category_id`,
	`cp`.`product_id`

And Finally

Now with this module in place (remember to activate it in etc/modules) clear your cache and reindex. And hey presto, the product is no longer showing up in a category that is a parent of a category that the product is in that is now disabled. (Bit of a mouthful that)

In Summary

Much nicer, and not too much of a performance hit really.

Overall it means your products only show in the right active categories.

Generally it is only a issue when you disable a category and don’t delete it and then rearrange things leaving remnants of the old category tree floating about, so basically anything a common user would do.

I don’t tend to delete stuff myself, in case things get reactivated, and I’ll reuse the records.

Hopefully this is of help, rather than deleting records!

A Week of Commuting

So today ends my first week of my Manchester based job at Fred Aldous. Currently this means I’ve been commuting to and from Manchester every day.

Currently my journey goes

  • My House
  • Burley Park
  • Leeds train station
  • Manchester Piccadilly
  • Starbucks
  • Short walk

And of course the reverse!

So far it’s been difficult to get a seat but I don’t mind standing since it is some form of exercise 😛

Anyway, what really annoys me is when a train arrives at a station and people gravitate towards the doors.

They do it in such a way so that it makes it difficult for people to get off. Which aside from being a pain in the arse do them, the moment there is a gap people start piling on, and of course at peak times there is always a person or two stuck in the aisle trying to get off whilst people are piling on the train.

Inevitably this causes delays as the piling on lot have to stop and clear a route for those getting off to get off.

/Begin Rant/
Why can’t people just take a step or to back and not be in such a rush! If you let people get off and just give everyone some space. I’m sure it will be better for everyone involved and potentially help keep the trains on time at these peak times!
/End Rant/

Anyway my first week hasn’t been to bad. But more on my new job in another post. Currently standing on a train! Trying to publish this blog post in the little windows of coverage I get! And we appear to be following a slow moving train!

My CV and some other Updates

Just a quick post to say hello.

I’ve updated my CV and tidied it up a little.
So any feedback on it would be useful! (Drop Me a Line rather than a Comment please)
Link in the SideBar on the right!

I should really write on my blog some more, but not a lot to write about of late.
JetPack Extras is doing OK, but JetPack core decided to implement the main feature I added, that being Pinterest but, Extras has evolved, it still of course has the ability to control button placement and has the extra Twitter Via/Related options, and the ability to share the WP.me shortened URL, makes a nice Twitter Card, embedding a short Preview of the Post with the Tweet, (check this Meta Tweet for an example) works with any wp.me url you share on Twitter by the way!

Most useful, mainly updated to keep up with JetPack core and use the new shiny hooks they have.
You can check it out on extend. Feedback/request are always Welcome!

Made a couple of tweaks and updates to the Blog in the background, added a better 404 page and updated the .htaccess rules, I have all subdomains pointing to the here, (unless their is a separate to show), so its a good idea to redirect non sites to here, rather than duplicating the content.
Also tweaked the Root .htaccess to have a Error Document (404) since it was just standard Apache Error, bit nasty, but now better, LOLCATS TO THE RESCUE! 😀

Heres a extract of the rules, if you find it useful/handy:

RewriteEngine on

RewriteCond %{HTTP_HOST} !^barrycarlyon.co.uk  
RewriteRule ^(.*)$ http://barrycarlyon.co.uk/%{REQUEST_URI} [R=301,L]
ErrorDocument 404 http://barrycarlyon.co.uk/wordpress/404-2/
ErrorDocument 403 http://barrycarlyon.co.uk/wordpress/404-2/

Next thing to do, is probably update and replace the MineCraft Server Site probably using Twitter Bootstrap as a new basis. It’s now a whitelisted server for Armchair Heroes or on Facebook, but of late I’ve been getting my MineCraft fix playing over at PhantomCraft, like a lot of open Servers I tend to get robbed/cleaned out and griefed a lot, but then again I am a little close to spawn. It’s a fun server to play on and the regulars are quite nice.

Hopefully moving further away when I get a chance to play some more, thinks are pretty busy at the moment between Magento fun at Day Job and Freshers Fun at the Union.

Then I really should get the Portfolio page on the Blog fixed up to showcase what I’ve done before, since I am a Freelance Web Developer (you got any work going or want a quote for? (Drop me a line)

So a couple more weeks and we’ll be back to the usual grind.
Hopefully I will have more to write about soon!

Thoughts on working on a different computer when your normal one is not available

So currently my Mac Book Pro is in with Clockwork Leeds getting its Hard Drive replaced as it failed/has failed/is failing.

So currently at work, I am sans my normal Development machine and find myself on the Mac Mini server that sits and does our File Server and System Monitoring.

Its quite odd having to get used to a system that for starts is the Server build of Mac OSX as well as having none of my personal preferences.
Its bringing to light how slightly useless some of the things I have setup on my Personal machine are that I thought were useful.

For example, I’m stuck with a single screen, so no dual screen lovely-ness. So I’m very restricted to the screen real estate I have available.
So not having new Terminal Windows opening to fill half the screen like on my mac is quite useful. Whereas before having the secondary screen with two terminal windows full height, half width was the norm, as well as using a little app I’ve forgotten the name of which allows me to press ctrl + ~ to get a window appear from the top, is a bit of a pain, but resolved now with having three default sized terminals and chrome open.

So far its seemed to work just about fine like that.

I also find myself using Spaces/Expose a lot less, I’m doing a lot less jumping between spaces all the time. But I also don’t have the “App only opens in space x” setup so I get a lot less forced jumping.

Finally tho I miss my music collection, so I’ve switched to plugging my headphones into my iPad and listening to Spotify. Tho I could just plug in my external drive thats in the office and have most of my older music on hand….

I’m also on a different desk which is nice.
I’m not by the door and have the window behind me and I’m in the corner.
I quite like it over here. Seems a bit more separated from everyone else. Which is a good thing and a bad thing in its own right.

I love working with the team, *can’t think of right words to express feelings will just skip to post summary*.

Summary

My Mac is in the repair shop getting fixed.
The hard drive was failing and is pending spare parts from Apple.
Clockwork are amazing for Apple Repairs.
I’m in a corner at work
I have no home machine to really play or work on. (I made my home windoze laptop blue screen just by looking at it….)

I watched last nights Torchwood on my iPad using the BBC iPlayer Application and it worked perfectly.

Finally my local dev environment is shafted as that was my Mac Book Pro. Luckily everything is in offsite repo’s and I have a Server in a Server Room down the corridor to talk to 😀

-End-

SOLD and All Change!

So I left my job at Vanguard SEO as the start of this year for two reasons, one got a better job offer, and two the company went bankrupt. Not very lolworthy.


Anywho, I have been contracting for NewMedias, as a PHP Developer, since September Freelance style, and more 9-5 contracting during January and then NewMedias overlord Apeei, sold the company for mere bananas to CodingFutures, and then they offered me a job, so there I am now as a PHP Developer! And now we are nearly at the end of week two of my new job.


This week we had some nice photos done heres me and the rest of the team!

So these days I find myself working on YourMembers a WordPress plugin for Subscription and post control among other things. As well as the plugins for it. I built from the ground up YSS – Your Secure Stream, which can create links with expiry for Amazon S3 Hosted content, so people who use WordPres can provide Audio and Video, whilst protecting that content too! It plugs in to FlowPlayer and Longtails JWPlayer, some of the more commonly used players.

This week I’ve been working on the Mail/AutoResponder Plugin. Its been going well and should hopefully be finished soon 😀

Thru my work I’ve been getting to know WordPress more and the most useful link I’ve found today, is how to integrate TinyMCE, the Visual/HTML editor for WordPress, into your own Plugins. I’ve expanded on some of the code from the second link for the Visual/HTML editor switch, in order to handle multiple instances of TinyMCE on a page, as well as the core code. Mainly since the page I am using TinyMCE on, is generated thru a Form Generation Class I didn’t write and should at some point redo, its a nice class but has some interesting caveats and limitations. Let along the fact I havn’t managed to get WordPress using my usual jQuery DateTime Picker I like, since WordPress uses a old version of jQuery and jQuery UI, and if you load latest, its gonna break someone else’s plugin, installed on a client site, and to top it all WordPress loads the jQueryUI CSS for the latest version for Google CDN.

On a side note using Google CDN’s jQuery is very good and better, check this link for reasoning, and this one for implementation (in general not just WordPress).

I think the key point for using Google Code hosted jQuery, is that pages will load quicker, since more domains mean more open HTTP connections, as browsers limit the connections open to a single domain at once. About 6 according to the reasoning link, so more domains means more open HTTP connections which means pages can load more quickly. Let alone the fact that if everyone loads from Google Code, then its cached on the users machine, which means from site a to site b, on site b, user doesn’t have to load jQuery as its already in the Cache. Thus making page loads quicker!

Still need to find a link explaining how to use the WordPress collapse a box, that all the WP-Admin grey bars and boxes do. I have built my own function in jQuery to do it, but it doesn’t (yet) have the cookie elements that WordPress’es own has to remember which are open/closed when the page reloads. Since seem there is no easy documented way on how to use the in built functionality.

Either that or I’ve missed it.

Also lately been working with the MailChimp API, had to laugh at the URL: http://apidocs.mailchimp.com/rtfm/.

On that bomb shell, catch you later, I’ve felt like I’ve rambled on a lot when this post was looking quite short…. Must blog more. Keep an eye on my Tumblr for more real time snippets of stuff, I’m assuming you already follow me on Twitter…..