Update database query Magento 1.9

Question

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

Answer 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

• Melvin: this returns the total count ?
• Ricky Odin Matthews: it would be qty for each cart line item

Answer 2

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');