Hot answers tagged

57 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 ...
Andrei's user avatar
  • 787
18 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();
Prince Patel's user avatar
  • 22.7k
16 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);
Raphael at Digital Pianism's user avatar
16 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', ...
Rakesh Jesadiya's user avatar
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. ...
Raphael at Digital Pianism's user avatar
12 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->...
MGento's user avatar
  • 1,519
11 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]...
Amit Bera's user avatar
  • 77.4k
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 ...
Ivan Chepurnyi's user avatar
8 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')) ...
Bilal Usean's user avatar
  • 9,947
7 votes
Accepted

Customer Repository Interface, Limit Results

Figured it out. http://devdocs.magento.com/guides/v2.2/extension-dev-guide/searching-with-repositories.html $searchCriteria = $this->searchCriteria ->setFilterGroups([$...
Ethan Yehuda's user avatar
6 votes

How to print collection mysql query in magento 2?

Or, simply use echo $collection->getSelect()
Khoa TruongDinh's user avatar
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 ...
Fabian Schmengler's user avatar
6 votes
Accepted

Magento 2 How to create graphql query

I have cerate custom module for get customer details by customer email. Step 1 :- You Create registration.php file <?php /** * @author Mohit Patel * @copyright Copyright (c) 2021 * @package ...
Mohit Patel's user avatar
  • 3,750
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\...
David Verholen's 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 \...
Siarhey Uchukhlebau's user avatar
5 votes

Retrieve collection using limit and offset in magento 2

You can set limit and offset like this, $collectionLimit = 600; $collectionOffset = $offset; $collections = $model->getCollection() ->addFieldToFilter('test_id',array('eq'=>$test_id))...
Rakesh Jesadiya's user avatar
5 votes
Accepted

Retrieve collection using limit and offset in magento 2

Use the $collection->setPageSize(600) and $collection->setCurPage($offset) methods.
Aaron Allen's user avatar
  • 9,009
4 votes
Accepted

How to get attribute_id from “eav_attribute” table in Magento

you can get id from attribute code like that $attribute_code = "sb_store_id"; $attribute_details = Mage::getSingleton("eav/config")->getAttribute('catalog_product', $attribute_code); echo $...
Qaisar Satti's user avatar
  • 32.5k
3 votes

How to use Distinct in magento1?

Tested on Magento ver 1.9.4.3. $collection = Mage::getResourceModel('sales/order_collection') ->addFieldToSelect('customer_id') ->distinct(true) ->addFieldToFilter('entity_id', ['...
kiatng's user avatar
  • 694
3 votes
Accepted

how to fetch items from varien_db_select to collection

you don't need to run the query manually. You can do this: $col = Mage::getModel('Module/contact_request')->getCollection() ->addFieldToSelect(array('entity_id', 'update', 'status'))...
Marius's user avatar
  • 198k
3 votes

How to run direct SQL query on magento and bind WHERE IN params?

Why don't you just use the following: Mage::getSingleton('catalog/product')->getIdBySku($productSku); You can also get multiple products from a collection: $collection = Mage::getSingleton('...
Phoenix128_RiccardoT's user avatar
3 votes

How to get attribute_id from “eav_attribute” table in Magento

Here is how you should do it. You need to know the entity type to be able to get it (product,category...). Example for product: $attribute = Mage::getSingleton('eav/config')->getAttribute(...
Raphael at Digital Pianism's user avatar
3 votes
Accepted

Magento 2: Update Query Using Standard Way

Try using: $this->getConnection()->update( 'posts', [ 'viewed' => new \Zend_Db_Expr('viewed + 1'), ], ["id = ?" => 1] );
Atish Goswami's user avatar
3 votes

Magento 2: How many tables required to change Order Status Programmatically?

What's difference between Order Status & Order State? They both are different. State is used by magento to tell if the order is new, processing, complete, holded, closed, canceled, etc. ...
Prashant Valanda's user avatar
3 votes

Error while trying to save a product

I don't have a solution but I have a place you can start debugging. The query looks very similar to the one generated by the method Mage_Rule_Model_Resource_Abstract::getProductFlatSelect() public ...
Marius's user avatar
  • 198k
3 votes
Accepted

Writting SQL query to remove product description from database table

[1] Find attribute_id in eav_attribute table 75 - Description 76 - Short Description [2] Go to catalog_product_entity_text Table entity_id = Product Id DELETE FROM catalog_product_entity_text ...
Jackson's user avatar
  • 9,889
3 votes
Accepted

Magento 2: Collection Query addFieldToFilter Loop Issue

Give it a try - foreach ($items as $item) { $products = clone $customModel; $products->addFieldToFilter('product_id', $item->getProductId()); echo $products->getSelect()->...
Piyush's user avatar
  • 5,893
3 votes
Accepted

Magento 2: What's best way to convert MySql to Magento

Try this: $items->getSelect() ->join( array('l' => 'customer_entity_varchar'), 'l.entity_id = l.entity_id', array('profile' => 'l.value') ) ->join( array('m' => '...
Stepan Furman's user avatar
3 votes

Cardinality violation: 1241 Operand should contain 1 column(s)

You are doing wrong at (item.product_id = '36', '37') you can not use = with multiple value. You need to use IN instead.
Sandipan S's user avatar
3 votes

How to Update a Column in Magento 1.9

Try following way $productId = 9; $saveData = array('custom_col' => 1); $write = Mage::getSingleton('core/resource')->getConnection('core_write'); $where = $write->quoteInto('product_id = ?', $...
Sohel Rana's user avatar
  • 35.7k

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