How to get null attribute product value in magento 1 addAttributeToFilter Filter Query | Where condition

$collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect(['entity_id'])
        ->addAttributeToFilter(
        array(
            array('attribute'=> 'pricegroup','null' => true),
            array('attribute'=> 'pricegroup','eq' => ''),
            array('attribute'=> 'pricegroup','eq' => 'NO FIELD')
        ),
        '',
        'left')
        ->addAttributeToFilter('status', ['eq' => 1]);
echo $collection->getSelect();
echo '<pre>';print_r($collection->getData());exit;

Join Query with LIKE in PHP Mysql

Join Query with %LIKE% function in MYSQL PHP

SELECT `customers`.*, `admin`.`firstname` AS `salespersonfname`, `admin`.`lastname` AS `salespersonlname`
FROM `customers`
LEFT JOIN `admin` ON customers.tags LIKE CONCAT('%', `admin`.firstname, ' ', `admin`.lastname, '%')
ORDER BY `id` DESC

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'having clause', query was: SELECT COUNT(*) in Magento 1

Step 1. Open Collection.php of that model : root/app/code/local/Jaydip/Kansagra/Model/Resource/Rebatecash/Collection.php

Step 2.  Create getSelectCountSql() function in Collection.php file
public function getSelectCountSql() {
        $this->_renderFilters();
        $select = clone $this->getSelect();
        $select->reset(Zend_Db_Select::ORDER);
        $select->reset(Zend_Db_Select::LIMIT_COUNT);
        $select->reset(Zend_Db_Select::LIMIT_OFFSET);       
        $countSelect = clone $this->getSelect();
        $countSelect->reset();
        $countSelect->from(array('a' => $select), 'COUNT(*)');
        return $countSelect;
    }

Forloop query not workin in Magento 2 | Get same Ordered Qty of all products in Magento 2

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');
$soldModel = $objectManager->get('Magento\Reports\Model\ResourceModel\Product\Sold\Collection');
$collections = $productCollection->addAttributeToSelect('*')->setOrder('entity_id', 'desc')->load();
$productdata = array();
foreach ($collections as $product){
         
            $soldproduct = clone $soldModel;
            $soldproduct = $soldproduct->addOrderedQty()->addAttributeToFilter('product_id', $product->getId())->setOrder('ordered_qty', 'desc')->getFirstItem();
            $sold = (int)$soldproduct->getOrderedQty();
         
            $product->setSold($sold);
            $productdata['products'][] = $product->getData();
}
echo '<pre>';print_r($productdata);

How to get the final sql / mysql query from the grid includes filters and pagination in Magento

Open Following file

Open MagentoRoot/lib/Varien/Db/Adapter/Pdo/Mysql.php

Update false to true

protected $_debug = true
protected $_logAllQueries  = true

Get Address of customer from database Query in Magento PHP Mysql

SELECT
`entity_id` as `entity_id`,
`firstname`.`value` as `First_Name`,
`surname`.`value` as `Surname`,
`telephone`.`value` as `Telephone`,
`country`.`value` as `country`,
`region`.`value` as `region`,
`statecounty`.`value` as `statecounty`,
`city`.`value` as `city`,
`company`.`value` as `company`,
`street`.`value` as `street1`,
`customer_entity`.`created_at`,
`customer_entity`.`updated_at`
FROM
`customer_address_entity_varchar` as `country`
INNER JOIN
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `surname` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `telephone` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `region` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `statecounty` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `city` USING (`entity_id`)
INNER JOIN
`customer_address_entity_varchar` as  `company` USING (`entity_id`)
INNER JOIN
`customer_address_entity_text` as  `street` USING (`entity_id`)
INNER JOIN
`customer_entity` USING (`entity_id`)
WHERE
`firstname`.`attribute_id` = 20  &&
`surname`.`attribute_id` = 22  &&
`country`.`attribute_id` = 27 &&
`region`.`attribute_id` = 28 &&
`statecounty`.`attribute_id` = 144 &&
`city`.`attribute_id` = 26 &&
`company`.`attribute_id` = 24 &&
`street`.`attribute_id` = 25 &&
`telephone`.`attribute_id` = 31

How to get the latest record in each group using GROUP BY

In PHP MySQL

SELECT `main_table`.`rebate_amount`, `main_table`.`companyname`, (SELECT target FROM batecash WHERE id = MAX(main_table.id)) AS `maxtarget`, SUM(rebate_amount) AS `rebate_amount`, SUM(invoiceamount) AS `purchase_amount`, MAX(id) AS `max_id`, `main_table`.`rebatetarget`, YEAR(created_at) yr, QUARTER(created_at) qt FROM `batecash` AS `main_table` WHERE (customerid = '5') GROUP BY YEAR(created_at), QUARTER(created_at) ORDER BY `created_at` DESC

 In Magento

$rebatecount = Mage::getModel('rebatereward/batecash')->getCollection()
                    ->addFieldToSelect('rebate_amount')
                    ->addFieldToSelect('companyname')
                    ->addFieldToFilter('orderstatus', array('eq' => 'completed'))
                    ->addFieldToFilter('addressid', array('eq' => $address->getId()))
                    ->addFieldToFilter('customerid', array('eq' => $this->getCustomer()->getId()));

            $rebatecount->getSelect()->columns("(SELECT rebatetarget FROM batecash WHERE id = MAX(main_table.id)) as maxtarget");
            $rebatecount->getSelect()
                    ->columns('SUM(rebate_amount) as rebate_amount')
                    ->columns('SUM(invoiceamount) as purchase_amount')
                    ->columns('MAX(id) as max_id')
                    ->columns('rebatetarget')
                    ->columns('YEAR(created_at) yr, QUARTER(created_at) qt')
                    ->group('YEAR(created_at), QUARTER(created_at)')
                    ->order(array('created_at DESC'));