Skip to main content
28 votes

Magento 1 SQL Queries

There is a more proper way to do this to avoid SQL Injections. $resource = Mage::getSingleton('core/resource'); $write = $resource->getConnection('core_write'); $table = $resource->getTableName(...
Shadoweb's user avatar
  • 523
23 votes
Accepted

Magento 2 : How To Create UNIQUE KEY Field in Custom Tabel

You can use Index Type as Old Way: Using setup script \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE To add unique key use code, ->addIndex( $installer->getIdxName( ...
Jaimin Sutariya's user avatar
22 votes

Magento 2: How to delete all categories at once?

This one worked for me in Magento ver. 2.1.0 SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE catalog_category_entity; TRUNCATE TABLE catalog_category_entity_datetime; TRUNCATE TABLE ...
hanish singla's user avatar
18 votes
Accepted

Magento 2: How to get product name by sql query using product ID

Magento uses an EAV table structure for products. Names are stored in catalog_product_entity_varchar. If you have the product ID X, you can query this table directly: SELECT entity_id, value, ...
Fabian Schmengler's user avatar
14 votes
Accepted

magento2 search_tmp join leads to empty results in category listing

To debug this, temporarily change createTemporaryTable to createTable in \Magento\Framework\Search\Adapter\Mysql\TemporaryStorage::createTemporaryTable $table->setOption('type', 'memory'); ...
Alex's user avatar
  • 13.9k
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: How can I remove all products URL rewrites?

To remove all URL rewrites related to the products, you can use that script SQL below. DELETE FROM url_rewrite WHERE entity_type='product'; If you want just to remove the URL rewrites that were ...
Rafael Corrêa Gomes's user avatar
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.6k
11 votes
Accepted

row_id vs entity_id in magento 2.3 EE

Time Dimension row_id is used to facilitate the Time Dimension for entities that support it. By having this additional identifier, it allows an entity (product, category, sales rule, etc..) to be ...
sdaugherty's user avatar
  • 1,231
10 votes

Delete All products

I recently have written a Codeblog about this https://codeblog.experius.nl/magento-2-remove-sample-data-test-data/ If you really want to delete all products and their related data you could use ...
Mr. Lewis's user avatar
  • 1,941
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
8 votes
Accepted

Lock wait timeout exceeded

The error is because the transaction is reaching its timeout period. If you did not set this in your my.cnf the default is 50 seconds I would highly recommend you look at your sever settings to ensure ...
SR_Magento's user avatar
  • 5,219
8 votes
Accepted

Need help with collection joinField() - Magento 2

The function describes itself if you check comments added before the function. /** * Join regular table field and use an attribute as fk * * Examples: * ('country_name', 'directory_country_name', ...
Yogesh Trivedi's user avatar
7 votes
Accepted

Magento 2 - SQL Injection

The most correct answer is "don't directly query the database" - you should be using the ORM which would protect you in these situations. Especially when grabbing data out of the EAV tables. The ...
philwinkle's user avatar
  • 35.8k
7 votes

Magento 2: how to change the Order ID's?

go to your database, enter below query for table sequence_order_1. sequence_order_1 is used for order id management. ALTER TABLE sequence_order_1 AUTO_INCREMENT=200000399; Next order id is start ...
Rakesh Jesadiya's user avatar
7 votes
Accepted

Nested/subquery select in a join query

Can you try like below. It will surely work. i have checked in my local :). $subquery = new \Zend_Db_Expr('(SELECT DISTINCT value from catalog_product_entity_int where row_id in (select row_id from ...
Rama Chandran M's user avatar
7 votes
Accepted

Magento 2: How many options do I have to use with addFieldToFilter?

The method has two parameters. public function addFieldToFilter($field, $condition) If $condition integer or string - exact value will be filtered ('eq' condition) If $condition is an array - one ...
Rafael Corrêa Gomes's user avatar
7 votes

I am getting this error "pdo_mysql extension is not installed"

@SHPatel is right. There is an answer on askubuntu that gives instructions HERE I am going to assume that you have PHP 7 installed, otherwise you would never have gotten a Magento 2 instance running....
mtr.web's user avatar
  • 910
6 votes

Get full list of categories using sql

SELECT DISTINCT cc.entity_id as category_id, cc.value as name, cc1.value as url_path ,cce.parent_id as parent_id ,0 as top,level as `column`,position as sort_order,1 as status,created_at as ...
arun-r's user avatar
  • 231
6 votes
Accepted

Select product image using SQL in Magento 2

You can use the below query. select gal.value from catalog_product_entity as ent left join catalog_product_entity_media_gallery_value as val on (ent.row_id= val.row_id) left join ...
Oshan's user avatar
  • 1,952
6 votes

Magento 2 cron_schedule table increasing and CPU 100%

Maybe our questions are related edit/update: clearing your cron_schedule table should temporarily fix your problem: TRUNCATE cron_schedule; to permanently fix your problem you have to find out why ...
bayer's user avatar
  • 342
6 votes
Accepted

using sql queries is good practice in magento 2

SQL queries are not recommended in Magento official Documentaion. You can get a result in fastly while executing SQL queries, but it affects security. Avoid raw SQL queries Raw SQL queries can lead ...
Arunprabakaran M's user avatar
5 votes

Magento 2: How to delete all categories at once?

This one will remove all categories in Magento EE 2.1 SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE catalog_category_entity; TRUNCATE TABLE catalog_category_entity_datetime; TRUNCATE TABLE ...
Bogdan Le's user avatar
  • 591
5 votes
Accepted

Is there any way to get where condition from the collection?

You can get the select part in this way: $where = $collection->getSelect()->getPart(Zend_Db_Select::WHERE); There is also a setPart() method that can be used to set where condition. Other ...
sv3n's user avatar
  • 11.7k
5 votes
Accepted

Magento 2 - Add Custom Attribute to Product Collection without Flat Catalog EAV?

Adding the catalog_product_entity_varchar table as a second paramater to the addAttributeToSelect method call seemed to fix my issue: ->addAttributeToSelect('unit_of_sale', '...
harri's user avatar
  • 5,495
5 votes

Magento 2: SQL Query to get product SKU's based on the Category Id

Your Sql query will be like this: `SELECT * FROM catalog_product_entity where entity_id in (SELECT product_id FROM catalog_category_product where category_id = {YourCategoryId})`.
Amit Bera's user avatar
  • 77.6k
4 votes

how to get product specific attribute codes & values using raw sql query?

For Magento version 2.2, where a new redirection has been added in their EAV model, below query is working for my project. There the entity_type_id needed to be retrieved from catalog_product_entity. ...
zaki.i's user avatar
  • 57
4 votes

Magento 2: how to use sql queries on controller?

You should not do it in controller. ResourceModel Layer is resposible for data persistence
KAndy's user avatar
  • 20.9k
4 votes
Accepted

->order('v.entity_id IS NULL') gets translated incorrectly

Please try with: ->order(new Zend_Db_Expr('v.entity_id IS NULL'));
michael's user avatar
  • 2,770
4 votes

magento2 search_tmp join leads to empty results in category listing

This was caused improperly build index catalogsearch_fulltext, indexer_id in indexer_state table was in working state, this should be changed to invalid and reindexed. TIP FOR THOSE WHO STRUGGLING ...
Ilja's user avatar
  • 1,268

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