The product flat file (ie catalog_product_flat_1) is a great method to retrieve information about active items in your Magento store. Unfortunately it only includes item attributes that you’ve defined as being available on the frontend and doesn’t have any inventory information such as qty, is_in_stock etc.
Wouldn’t it be great if we could combine “catalog_product_flat_1” and “cataloginventory_stock_item” into one file that would have all the item information in one place to write simple queries against.
Actually, there’s a pretty straightforward way of combining these two files and it doesn’t require any modifications to Magento core files or creating new modules.
By using a MYSQL CREATE VIEW method, we can create a “virtual” table that contains all the product information we need.
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
A view always shows up-to-date data. The database engine recreates the data, using the view’s SQL statement, every time a user queries a view. A word of caution though – when using views, the SQL is run every time a query is initiated, so if you have extremely large product tables, response time may be an issue – use these selectively, such as when you want to create csv file or perform certain audits. They’re not designed for transactional activities.
So, to create a view that has everything from the product flat file and everything from the product inventory files, simply follow this procedure:
- Backup your database
- Go into phpmyadmin (or similar tool) and select your Magento database.
- Run the following SQL (if you only want selected fields, modify the select statement to only include those fields desired)
CREATE VIEW view_product_and_inventory AS SELECT catalog_product_flat_1 . * , cataloginventory_stock_item . *
JOIN `cataloginventory_stock_item` ON ( `catalog_product_flat_1`.`entity_id` = `cataloginventory_stock_item`.`product_id` )
When you browse your tables, the new “view_product_and_inventory” should show up in the listings. You’ll note that the only difference will be the type on the right hand side will be “view” instead of InnoDB :
Selecting the table and browsing it will display all current product data as if it was a separate table.
Now, when you need to create an ad-hoc query, simply use the combined “view” in you select statements! Note: You should never do any updates on this view or the catalog_product_flat_1 or cataloginventory_stock_item tables as they’ll only get over-written when they’re rebuilt.