0

I have following DB Query which is coming as expected. This Query came from the plugin Abandon Carts and I echoed the select Query. I have to edit this query to get the qty of each product in the cart. how can I do that?

SELECT `e`.`entity_id` AS `product_id`, `e`.`sku`, `catalog_name`.`value` AS `product_name`, `catalog_price`.`value` AS `product_price`, `quote_table`.`entity_id` AS `cart_id`, `quote_table`.`updated_at` AS `cart_updated_at`, `quote_table`.`abandoned_notified` AS `has_been_notified`, `quote_table`.`customer_email`, `quote_table`.`customer_firstname`, `quote_table`.`customer_lastname`, `quote_table`.`customer_group_id` AS `customer_group` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_varchar` AS `catalog_name` ON catalog_name.entity_id = e.entity_id AND catalog_name.attribute_id = 71
 INNER JOIN `catalog_product_entity_decimal` AS `catalog_price` ON catalog_price.entity_id = e.entity_id AND catalog_price.attribute_id = 75
 INNER JOIN `sales_flat_quote_item` AS `quote_items` ON quote_items.product_id = e.entity_id AND quote_items.price > 0.00
 INNER JOIN `sales_flat_quote` AS `quote_table` ON quote_items.quote_id = quote_table.entity_id AND quote_table.items_count > 0 AND quote_table.is_active = 1 AND quote_table.customer_email IS NOT NULL AND quote_table.abandoned_notified = 0 AND quote_table.updated_at < "2018-05-17 03:46:04" AND quote_table.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `catalog_enabled` ON catalog_enabled.entity_id = e.entity_id AND catalog_enabled.attribute_id = 96 AND catalog_enabled.value = 1
 INNER JOIN `cataloginventory_stock_status` AS `inventory` ON inventory.product_id = e.entity_id AND inventory.stock_status = 1 AND website_id = 1 ORDER BY `quote_table`.`updated_at` DESC

Can anyone help to get the qty also of each item in this query?

PHP Code : 

$collection->getSelect()
                                ->reset(Zend_Db_Select::COLUMNS)
                                ->columns(array('e.entity_id AS product_id',
                                                'e.sku',
                                                'catalog_name.value as product_name',
                                                'catalog_price.value as product_price',
                                                'quote_table.entity_id as cart_id',
                                                'quote_table.updated_at as cart_updated_at',
                                                'quote_table.abandoned_notified as has_been_notified',
                                                'quote_table.customer_email as customer_email',
                                                'quote_table.customer_firstname as customer_firstname',
                                                'quote_table.customer_lastname as customer_lastname',
                                                'quote_table.customer_group_id as customer_group'
                                                )
                                            )
                                // Name
                                ->joinInner(
                                    array('catalog_name'    =>  Mage::getSingleton("core/resource")->getTableName('catalog_product_entity_varchar')),
                                    "catalog_name.entity_id = e.entity_id AND catalog_name.attribute_id = $nameId",
                                    null)
                                // Price
                                ->joinInner(
                                    array('catalog_price'   =>  Mage::getSingleton("core/resource")->getTableName('catalog_product_entity_decimal')),
                                    "catalog_price.entity_id = e.entity_id AND catalog_price.attribute_id = $priceId",
                                    null)
                                ->joinInner(
                                    array('quote_items' => Mage::getSingleton("core/resource")->getTableName('sales_flat_quote_item')),
                                    'quote_items.product_id = e.entity_id AND quote_items.price > 0.00',
                                    null)
                                ->joinInner(
                                    array('quote_table' => Mage::getSingleton("core/resource")->getTableName('sales_flat_quote')),
                                    'quote_items.quote_id = quote_table.entity_id AND quote_table.items_count > 0 AND quote_table.is_active = 1 AND quote_table.customer_email IS NOT NULL AND quote_table.abandoned_notified = 0 AND quote_table.updated_at < "'.$delay.'" AND quote_table.store_id = '.$storeId,
                                    null)
                                ->joinInner(
                                    array('catalog_enabled' =>  Mage::getSingleton("core/resource")->getTableName('catalog_product_entity_int')),
                                    'catalog_enabled.entity_id = e.entity_id AND catalog_enabled.attribute_id = '.$statusId.' AND catalog_enabled.value = 1',
                                    null)
                                ->joinInner(
                                    array('inventory' => Mage::getSingleton("core/resource")->getTableName('cataloginventory_stock_status')),
                                    'inventory.product_id = e.entity_id AND inventory.stock_status = 1 AND website_id = '.$websiteId,
                                    null)
                                ->order('quote_table.updated_at DESC');

Result :

enter image description here

Improve this question
0

2 Answers 2

Reset to default
1

This should work;

SELECT `e`.`entity_id` AS `product_id`, `e`.`sku`, `catalog_name`.`value` AS `product_name`, `catalog_price`.`value` AS `product_price`, `quote_table`.`entity_id` AS `cart_id`, `quote_table`.`updated_at` AS `cart_updated_at`, `quote_items`.`qty`  AS `cart_qty`, `quote_table`.`abandoned_notified` AS `has_been_notified`, `quote_table`.`customer_email`, `quote_table`.`customer_firstname`, `quote_table`.`customer_lastname`, `quote_table`.`customer_group_id` AS `customer_group` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_varchar` AS `catalog_name` ON catalog_name.entity_id = e.entity_id AND catalog_name.attribute_id = 71
 INNER JOIN `catalog_product_entity_decimal` AS `catalog_price` ON catalog_price.entity_id = e.entity_id AND catalog_price.attribute_id = 75
 INNER JOIN `sales_flat_quote_item` AS `quote_items` ON quote_items.product_id = e.entity_id AND quote_items.price > 0.00
 INNER JOIN `sales_flat_quote` AS `quote_table` ON quote_items.quote_id = quote_table.entity_id AND quote_table.items_count > 0 AND quote_table.is_active = 1 AND quote_table.customer_email IS NOT NULL AND quote_table.abandoned_notified = 0 AND quote_table.updated_at < "2018-05-17 03:46:04" AND quote_table.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `catalog_enabled` ON catalog_enabled.entity_id = e.entity_id AND catalog_enabled.attribute_id = 96 AND catalog_enabled.value = 1
 INNER JOIN `cataloginventory_stock_status` AS `inventory` ON inventory.product_id = e.entity_id AND inventory.stock_status = 1 AND website_id = 1 ORDER BY `quote_table`.`updated_at` DESC
Improve this answer
2
  • this returns the total count ?
    – Melvin
    May 10, 2018 at 13:01
  • 1
    it would be qty for each cart line item
    – Ricky Odin Matthews
    May 10, 2018 at 13:40
0

TRy below sql query in PMA:

SELECT `e`.`entity_id` AS `product_id`, `e`.`sku`, `catalog_name`.`value` AS `product_name`, `catalog_price`.`value` AS `product_price`, `quote_table`.`entity_id` AS `cart_id`, `quote_table`.`items_count` AS `Qty`, `quote_table`.`updated_at` AS `cart_updated_at`, `quote_table`.`abandoned_notified` AS `has_been_notified`, `quote_table`.`customer_email`, `quote_table`.`customer_firstname`, `quote_table`.`customer_lastname`, `quote_table`.`customer_group_id` AS `customer_group` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_varchar` AS `catalog_name` ON catalog_name.entity_id = e.entity_id AND catalog_name.attribute_id = 71
 INNER JOIN `catalog_product_entity_decimal` AS `catalog_price` ON catalog_price.entity_id = e.entity_id AND catalog_price.attribute_id = 75
 INNER JOIN `sales_flat_quote_item` AS `quote_items` ON quote_items.product_id = e.entity_id AND quote_items.price > 0.00
 INNER JOIN `sales_flat_quote` AS `quote_table` ON quote_items.quote_id = quote_table.entity_id AND quote_table.items_count > 0 AND quote_table.is_active = 1 AND quote_table.customer_email IS NOT NULL AND quote_table.abandoned_notified = 0 AND quote_table.updated_at < "2018-05-17 03:46:04" AND quote_table.store_id = 1
 INNER JOIN `catalog_product_entity_int` AS `catalog_enabled` ON catalog_enabled.entity_id = e.entity_id AND catalog_enabled.attribute_id = 96 AND catalog_enabled.value = 1
 INNER JOIN `cataloginventory_stock_status` AS `inventory` ON inventory.product_id = e.entity_id AND inventory.stock_status = 1 AND website_id = 1 ORDER BY `quote_table`.`updated_at` DESC

PHP Code:

$collection->getSelect()
                                ->reset(Zend_Db_Select::COLUMNS)
                                ->columns(array('e.entity_id AS product_id',
                                                'e.sku',
                                                'catalog_name.value as product_name',
                                                'catalog_price.value as product_price',
                                                'quote_table.entity_id as cart_id',
                                                'quote_table.items_count as qty',
                                                'quote_table.updated_at as cart_updated_at',
                                                'quote_table.abandoned_notified as has_been_notified',
                                                'quote_table.customer_email as customer_email',
                                                'quote_table.customer_firstname as customer_firstname',
                                                'quote_table.customer_lastname as customer_lastname',
                                                'quote_table.customer_group_id as customer_group'
                                                )
                                            )
                                // Name
                                ->joinInner(
                                    array('catalog_name'    =>  Mage::getSingleton("core/resource")->getTableName('catalog_product_entity_varchar')),
                                    "catalog_name.entity_id = e.entity_id AND catalog_name.attribute_id = $nameId",
                                    null)
                                // Price
                                ->joinInner(
                                    array('catalog_price'   =>  Mage::getSingleton("core/resource")->getTableName('catalog_product_entity_decimal')),
                                    "catalog_price.entity_id = e.entity_id AND catalog_price.attribute_id = $priceId",
                                    null)
                                ->joinInner(
                                    array('quote_items' => Mage::getSingleton("core/resource")->getTableName('sales_flat_quote_item')),
                                    'quote_items.product_id = e.entity_id AND quote_items.price > 0.00',
                                    null)
                                ->joinInner(
                                    array('quote_table' => Mage::getSingleton("core/resource")->getTableName('sales_flat_quote')),
                                    'quote_items.quote_id = quote_table.entity_id AND quote_table.items_count > 0 AND quote_table.is_active = 1 AND quote_table.customer_email IS NOT NULL AND quote_table.abandoned_notified = 0 AND quote_table.updated_at < "'.$delay.'" AND quote_table.store_id = '.$storeId,
                                    null)
                                ->joinInner(
                                    array('catalog_enabled' =>  Mage::getSingleton("core/resource")->getTableName('catalog_product_entity_int')),
                                    'catalog_enabled.entity_id = e.entity_id AND catalog_enabled.attribute_id = '.$statusId.' AND catalog_enabled.value = 1',
                                    null)
                                ->joinInner(
                                    array('inventory' => Mage::getSingleton("core/resource")->getTableName('cataloginventory_stock_status')),
                                    'inventory.product_id = e.entity_id AND inventory.stock_status = 1 AND website_id = '.$websiteId,
                                    null)
                                ->order('quote_table.updated_at DESC');
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.