58
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 ...
- 797
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();
- 22.9k
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',
...
- 42.4k
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);
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->...
- 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]...
- 77.7k
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'))
...
- 10.1k
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([$...
- 685
7
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 ...
- 3,968
6
votes
How to print collection mysql query in magento 2?
Or, simply use
echo $collection->getSelect()
- 32.3k
6
votes
Accepted
Retrieve collection using limit and offset in magento 2
Use the $collection->setPageSize(600) and $collection->setCurPage($offset) methods.
- 9,029
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 ...
- 66.1k
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))...
- 42.4k
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 \...
- 16.2k
4
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.
- 939
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 ...
- 9,969
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 ...
- 199k
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.
...
- 12.7k
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', ['...
- 694
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()->...
- 5,914
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' => '...
- 841
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 = ?', $...
- 36.1k
3
votes
How to resolve the "Direct object instantiation (object of \Zend_Db_Expr) is discouraged in Magento 2"?
Not sure about this, but I think you can use factories for the zend classes also.
Try this:
Have this member in your class:
private $zendExprFactory;
And add this in your constructor:
public ...
- 199k
3
votes
Accepted
Use of sub query within collection in Magento
$collection->getSelect()->where('e.entity_id NOT IN(?)', new \Zend_Db_Expr('(SELECT b.parent_id FROM cataloginventory_stock_item AS a JOIN catalog_product_relation AS b ON a.product_id = b....
3
votes
Accepted
Base images as first image, change positions in bulk - Magento 2.3.5
the snippet below is the steps to follow to resolve your issue
$mainImage = $this->imageQueryResource->getProductMainImage($productId);
$firstImage = $this->imageQueryResource->...
- 7,753
3
votes
Accepted
Magento 2 : row id and entity id not same in catalog_product_entity table
The row_id only available in the enterprise edition(Adobe Commerce).There is no rule for row_id and entity_id should be the same or not the same. Most entries have the same row_id and entity_id and ...
- 10.1k
3
votes
Accepted
Magento 2 How to add custom product attribute into existing product graphql query
Step 1 :- You Create registration.php file
<?php
/**
* @author Mohit Patel
* @copyright Copyright (c) 2021
* @package Mag_ExtendQuery
*/
\Magento\Framework\Component\ComponentRegistrar::...
- 3,968
2
votes
Magento 2: How to pass IN/OR Condition in Query
FOR OR condition you can use :
->where(new Zend_Db_Expr("(CONDITION_1 OR (CONDITION_2 AND (CONDITION_3 OR CONDITION_4)))"));
OR you can use
->orWhere instead of ->where
- 6,284
2
votes
How to list all disabled products with sql?
@Amit Bera answer is the best but SQL request doesn't works if you have more than one attribute code named "status" (in my case I have total of 5 lines of "status") and MySQL will returns you: #1242 - ...
- 121
2
votes
SQL Query for gettin all products with custom options
With Price
SELECT e.sku
,t.title as 'name'
,cv.value as 'Custom option name'
,GROUP_CONCAT(ot.title) as 'Custom option values'
,GROUP_CONCAT(price.price) as 'price'
FROM ...
Only top scored, non community-wiki answers of a minimum length are eligible