Magento: which catalog_product_flat I need to select from? and what is catalog_product_entity_datetime table?

Question

Hi everyone,

To know who are the customers needed to be sent the notification mail and know which are the products remain 2 hours before the deal expired, I need to select from wishlist, wishlist_item, customer_entity and catalog_product_enity table which need to join with catelog_product_flat_X But there are many catelog_product_flat_X tables (X has from 1 to 64) How to know which table I need to join with?

Here is the structure of the catalog_product_flat table which has fields "special_to_date" and "visibility" I need

CREATE TABLE `catalog_product_flat_1` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`type_id` varchar(32) NOT NULL DEFAULT 'simple',
`cost` decimal(12,4) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`enable_googlecheckout` tinyint(1) DEFAULT NULL,
`has_options` smallint(6) NOT NULL DEFAULT '0',
`image_label` varchar(255) DEFAULT NULL,
`links_exist` int(11) DEFAULT NULL,
`links_purchased_separately` int(11) DEFAULT NULL,
`links_title` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`news_from_date` datetime DEFAULT NULL,
`news_to_date` datetime DEFAULT NULL,
`price` decimal(12,4) DEFAULT NULL,
`price_type` int(11) DEFAULT NULL,
`price_view` int(11) DEFAULT NULL,
`required_options` tinyint(3) unsigned NOT NULL DEFAULT '0',
`shipment_type` int(11) DEFAULT NULL,
`short_description` text,
`sku` varchar(64) DEFAULT NULL,
`sku_type` int(11) DEFAULT NULL,
`small_image` varchar(255) DEFAULT NULL,
`small_image_label` varchar(255) DEFAULT NULL,
`special_from_date` datetime DEFAULT NULL,
`special_price` decimal(12,4) DEFAULT NULL,
`special_to_date` datetime DEFAULT NULL,
`tax_class_id` int(11) DEFAULT NULL,
`thumbnail` varchar(255) DEFAULT NULL,
`thumbnail_label` varchar(255) DEFAULT NULL,
`updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`url_key` varchar(255) DEFAULT NULL,
`url_path` varchar(255) DEFAULT NULL,
`visibility` tinyint(3) unsigned DEFAULT NULL,
`weight` decimal(12,4) DEFAULT NULL,
`weight_type` int(11) DEFAULT NULL,
PRIMARY KEY (`entity_id`),
KEY `IDX_TYPE_ID` (`type_id`),
KEY `IDX_ATRRIBUTE_SET` (`attribute_set_id`),
KEY `IDX_NAME` (`name`),
KEY `IDX_PRICE` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the catalog_product_entity which I can know the id of the product (entity_id)

CREATE TABLE `catalog_product_entity` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`entity_type_id` smallint(8) unsigned NOT NULL DEFAULT '0',
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`type_id` varchar(32) NOT NULL DEFAULT 'simple',
`sku` varchar(64) DEFAULT NULL,
`has_options` smallint(1) NOT NULL DEFAULT '0',
`required_options` tinyint(1) unsigned NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`entity_id`),
KEY `FK_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE` (`entity_type_id`),
KEY `FK_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
KEY `sku` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COMMENT='Product Entities';

and what is catalog_product_entity_datetime table? I see it also store the same value to the special_to_date's value in the value field

CREATE TABLE `catalog_product_entity_datetime` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`entity_id` int(10) unsigned NOT NULL DEFAULT '0',
`value` datetime DEFAULT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `IDX_ATTRIBUTE_VALUE` (`entity_id`,`attribute_id`,`store_id`),
KEY `FK_CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE` (`attribute_id`),
KEY `FK_CATALOG_PRODUCT_ENTITY_DATETIME_STORE` (`store_id`),
KEY `FK_CATALOG_PRODUCT_ENTITY_DATETIME_PRODUCT_ENTITY` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11873 DEFAULT CHARSET=utf8;

Please help me how to do it.

Thanks in advanced, Rithy

Answer 1

Magento's catalog product data structure implements the Entity-Attribute-Value model. You need to read up on EAV (ref Wikipedia), then consult the Magento database diagrams.

Answer 2

You should use magento catalog/product methods for this, not a raw sql query. so start at the Mage::getModel('catalog/product')->getCollection() and add joins until you get the data you want; the catalog_flat tables and any other table are only meant to use internally by magento. the flat tables are created by magento from the EAV schema performance purposes.