Hot answers tagged

51 votes
Accepted

How to print collection mysql query in magento 2?

The answers above are correct, but some collections only assemble the select in the _beforeLoad() method, as opposed to initialising it in the constructor. This means you'll get an empty string if you ...
user avatar
  • 727
33 votes
Accepted

How to list all disabled products with sql?

As magento product folow the EAV structure that You need to write a Query between eav_attribute and catalog_product_entity_int table Magento save product status on table ...
user avatar
  • 75.4k
20 votes
Accepted

How to use Distinct in magento?

Use group by instead of distinct. Add $collection->getSelect()->group('level'). Use group by instead of ->distinct(true). Code: $collection = Mage::getModel('school/student')->...
user avatar
  • 75.4k
15 votes

How to print collection mysql query in magento 2?

You can use same as magento 1 to print query in magento 2. $collection = $this->_collection->getSelect() ->joinLeft( 'sales_order_item', ...
user avatar
15 votes

How to print collection mysql query in magento 2?

You can use __toString() function to print query in Magento 2 echo $collection->getSelect()->__toString();
user avatar
  • 21.5k
14 votes
Accepted

Remove existing WHERE clauses from $collection->getSelect()

Simply add $collection->clear()->getSelect()->reset(\Zend_Db_Select::WHERE); to your code above your Query.
user avatar
  • 3,368
13 votes
Accepted

How to optimize the query: the product qty bought with each of the products

PHP improvements One big improvement you could in your PHP code would be to avoid using fetchAll to fetch large result sets as it will cause a heavy demand on system and possibly networks resources. ...
user avatar
12 votes
Accepted

Magento 2: How to pass IN/OR Condition in Query

You need to modify your where slightly. Replace: ->where('ce.entity_id IN? ',$customers); With: ->where('ce.entity_id IN (?)', $customers);
user avatar
11 votes
Accepted

Find value in comma separated string column through query

let's say that the country id you are looking for is $countryId. And the field name is country_id. You can add a filter like this: ->addFieldToFilter('country_id', array('finset' => $countryId))...
user avatar
  • 195k
10 votes
Accepted

Magento 1.8 Need database query change all products custom design template

First, Magento is manage custom layout using custom_design attribute which is varchar attribute and save attibute value at table catalog_product_entity_varchar First i have check custom_design ...
user avatar
  • 75.4k
10 votes
Accepted

Magento 2:SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'entity_id' in where clause is ambiguous

protected function _initSelect() { $this->addFilterToMap('entity_id', 'main_table.entity_id'); parent::_initSelect(); $this->getSelect()->joinLeft( ['secondTable' => $this->...
user avatar
  • 1,479
10 votes
Accepted

SQL Query for custom attribute

Magento uses the Eav structure to save the product attribute value. First, you have to find out your attribute type of upc.So, you have to open eav_attribute and run the below for getting backend_type]...
user avatar
  • 75.4k
8 votes

How to list all disabled products with sql?

Magento Query $productsCollection = Mage::getModel('catalog/product')->getCollection() ->addAttributeToFilter('status', 2); // added enabled Mysql Query SELECT `e`.*, IF(...
user avatar
8 votes
Accepted

SQL (sub)query performance

Problem It is a pity, but there is no simple solution for your query. There are multiple basic issues exists in your original queries, that require changing your approach in general: As more joins ...
user avatar
7 votes
Accepted

Magento 2 Update record/table with where clause

you can apply multiple where with AND logic e.g $collections = $this->_yourFactory->create()->getCollection() ->addFieldToFilter('order_id', array('eq' => '1')) ...
user avatar
  • 9,479
6 votes

How to filter collection by OR condition?

you can perform OR condition in this manner $_products = Mage::getModel('catalog/product')->getCollection() ->addAttributeToSelect('*') ->addAttributeToFilter( array( array('...
user avatar
6 votes
Accepted

Convert normal mysql query with math functions into magento model format

Give this a try: $collection = Mage::getModel('model/alias_here')->getCollection(); $collection->addExpressionFieldToSelect( 'distance', 'SQRT( POW(69.1 * ({{latitude}} - [startlat]...
user avatar
  • 195k
6 votes
Accepted

When to truncate the core_session table?

The only reason to truncate this table is if you change the session storage so that you don't need the table anymore. That is, after migrating sessions to redis, files or whatever. It's the ...
user avatar
5 votes
Accepted

Alternative method for executing update queries within Magento using PHP

Use $writeConnection = $resource->getConnection('core_write'); $writeConnection->query("Update....");
user avatar
  • 195k
5 votes
Accepted

Magento aggregate and between collections?

Assuming you have a model mapped to your table1 and one mapped to table2 you can try something like this. $date = '2014-07-08'; $collection = Mage::getModel('[module]/table1')->getCollection() ...
user avatar
  • 195k
5 votes
Accepted

Magento Collection addFieldToFilter complicated AND/OR query

You should not limit your start and end dates. Try this: $collection = Mage::getModel('mymodule_events/details')->getCollection(); $collection->addFieldToFilter( 'status', '1' ); $collection-&...
user avatar
  • 2,637
5 votes

Magento 1.8 Need database query change all products custom design template

Actually, you only want some particular products to use old theme, that's very easy and you don't need to touch any sql or query. Three Steps: Go product management Search the products you want to ...
user avatar
  • 429
5 votes
Accepted

How to run a query to deactivate quotes as Magento code?

The usual Magento way would be to load the collection first, then save the attribute for each item. However, this means there are at least n+1 queries for n quotes (1x SELECT and nx UPDATE) For your ...
user avatar
5 votes
Accepted

Magento join query with group by and having error

Maybe because this is a calculated field, did you try this for your having clause? : $collection->getSelect()->having('AVG(review.avg_rating) > ?', $min)
user avatar
  • 354
5 votes
Accepted

How to use join query in Magento?

The join Method should take the table as the first and the on statement as the second attribute. So something like this should work: $connection = $this->_objectManager->create('\Magento\...
user avatar
5 votes
Accepted

What model to use to query customer_entity_varchar table in Magento 2?

You can use the customer repository located at app/code/Magento/Customer/Model/ResourceModel/CustomerRepository.php (having API \Magento\Customer\Api\CustomerRepositoryInterface) like: /** * @var \...
user avatar
5 votes
Accepted

Retrieve collection using limit and offset in magento 2

Use the $collection->setPageSize(600) and $collection->setCurPage($offset) methods.
user avatar
  • 8,744
5 votes

How to print collection mysql query in magento 2?

Or, simply use echo $collection->getSelect()
user avatar
4 votes

Add WHERE with IN and OR to a product collection

How to use joinTable is described here: https://magento.stackexchange.com/a/13267/217 and in more details here: http://blog.fabian-blechschmidt.de/joining-a-flat-table-on-eav/ To use in in a filter ...
user avatar
4 votes
Accepted

Add WHERE with IN and OR to a product collection

Just try it this way // ... ->where('table1.attrib1 IN (?) OR table2.attrib2 IN (?) ', array(1,2,3)); Wrap the placeholders in parenthesis (?) and provide the values as an array array(1,2,3) ...
user avatar
  • 308

Only top scored, non community-wiki answers of a minimum length are eligible