Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

All Questions

Tagged with
1
vote
1answer
23 views

Update sales order immediately after inserted

I want to update the sales order right after it is inserted. I want to reflect the change on the sales order conformation email also which is going to the customer. So i have prepared the below code ...
0
votes
0answers
32 views

Magento 2.3: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

I'm very new to magento 2.3, I'm facing some error maybe from DB side.... Here is my code: /app/code/EC/Customimport/Controller/Adminhtml/Index/Import.php <?php namespace EC\Customimport\...
1
vote
1answer
160 views

SQL Query for custom attribute

I have create custom attribute in product upc I want custom query (sql) to get SKUs and UPCs Can anyone tell me the related tables from where I can get data, or if anyone can share query that would ...
0
votes
2answers
46 views

Pulling credit memo from database using mysql quesry

I'm trying to pull a credit memo from the directly from the database but i keep getting the below error ERROR: You have an error in your SQL syntax; check the manual that corresponds to your ...
0
votes
1answer
34 views

how to enable disabled products via query?

Hi I want to enable all products I have in my Magento 2 store, is there any query to enable all products we have using a single sql query ? Thanks
0
votes
1answer
27 views

Fetch all the students on the basis of ascending marks

This query fetches all the student name and there ids on basis of topics but i want to get these result on basis of ascending marks ... i.e select student_name and ids where student_topic = topic but ...
1
vote
1answer
27 views

Calculating average price of specific products over time

I'm trying to create a graph that will show the average price of specific products on my site over a period of time. What I'm struggling with is coming up with the most efficient query to use, in ...
0
votes
0answers
36 views

InnoDB temporary tablespace size explodes after reindex

Within a Magento 2.2.6 installation we currently have a peculiar problem, where MySQL's Temporary Tablespace of InnoDB (ibtmp1) suddenly explodes in size, growing seemingly indefinitely until the disk ...
3
votes
2answers
338 views

SQL query kills entire server

I caught the following query via mysql's SHOW FULL PROCESSLIST; which was eating the whole CPU / RAM on my server (32GB Ram, i7 hexacore CPU) rendering the entire server unusable for more than 3 hours....
-1
votes
2answers
122 views

Magento 1 Update/append product categories in database

I want mysql query for append or add new category ID to exsting product categories.
2
votes
2answers
274 views

Magento2 Query Param Binding

->having( 'COUNT(*) ' . '<= 3 ' ) Hardcoded value 3 need to come From: This is the query to return most viewed products biggest views number: SELECT COUNT(report_event.object_id)...
1
vote
2answers
84 views

How i can call SQL statement directly with magento 2

how to call SQL statements directly to database with Magento 2, This is not recommended, but in some situation we need to do this, for some very special tasks
0
votes
0answers
62 views

Magento 1.9 SQL Query to random show invoices product - costumer name and country , product name, image, invoice time

I need help with Magento 1.9 mySQL Query. I would like to get the Mysql Query for random show buy-ed product (last 10day), customer name, customer country, product name, image url, invoice create time,...
1
vote
0answers
80 views

Magento 2 query builder conditional order by

I have this query builder, which works fine. $collection->getSelect()->joinLeft( 'sales_order_item', 'e.entity_id = sales_order_item.product_id', array('qty_ordered'=>'SUM(...
0
votes
1answer
34 views

How to get data after adding attributes

So, I created adapter: $adapter = $this->_getWriteAdapter(); After that I added some attributes for select: $select = $adapter->select(); $select->from(array('o' => $this->getT... $...
0
votes
1answer
28 views

Configurable Product MSRP

I'm working on a query where I need to grab the MSRP from a simple product for orders placed to run some finance calculations (margin...). The sales_flat_order_item table includes the configurable ...
0
votes
1answer
182 views

Magento 2 bind param to query in order by clause

I'm trying to bind params to order by with expressions clause in Magento 2, my insecure code looks like: $order = new \Zend_Db_Expr("POW((main_table.long - $this->_coordinates['lat']), 2) + POW((...
0
votes
0answers
33 views

How to join tables in Magento 2 script

Table 1: Contains 3 records Column names are att_id and name Vinson Yadhav Vinay Table 2: Contains 3 records Column names are att_id and **age* 15 20 55 Table 3:Contains one record, here ...
1
vote
2answers
859 views

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

I am getting error when I run the below query in my database Cardinality violation: 1241 Operand should contain 1 column(s) SELECT `main_table`.*, COUNT( item1.product_id ) AS `i_count` FROM `...
0
votes
1answer
60 views

Convert a sql query to magento

I have two queries, 1. { $select = $this->_getReadAdapter()->select()->from(array('main_table' => $this->getMainStoreTable($category->getStoreId())), array('main_table....
0
votes
1answer
288 views

Magento 2 : How to Write Conditional Query in Collection Method

How to write this query in magento2 collection method ? SELECT o.increment_id, o.base_grand_total, h.status,o.created_at , h.created_at FROM sales_order AS o RIGHT JOIN sales_order_status_history AS ...
0
votes
2answers
89 views

Get Product id in magento2

Get product_Id using following query, public function getProductsIdBySku($skus) { $select = $this->getReadConnection() ->select() ->from($this->...
0
votes
1answer
81 views

How copy all Categories attributes for store view A to default store view

I would like to copy categories attributes (e.g title, search engine optimization text like url_key, contents, description etc) from a store view navigation to the default navigation. In other words,...
0
votes
1answer
75 views

Magento 1.9 database query help

I found a query so I can mass change something in my products description. With this script: <?php require_once('app/Mage.php'); Mage::app('default'); $collection = Mage::getModel('catalog/product'...
1
vote
2answers
120 views

Direct Query delete with error: SQLSTATE[21000]: Cardinality violation

I'm trying to delete some data from database with direct sql query, but I get a Cardinality violation. $conn = Mage::getSingleton('core/resource')->getConnection('core_write'); $sql = "DELETE * ...
0
votes
1answer
388 views

Group Concat with JOIN with data in first table is NULL in second

I have a query which joins two table. Invoice table and comments table. But there are instances that an invoice has no equivalent row in Comments table. My query returns those who has both, Example ...
0
votes
1answer
286 views

SQL: Calculate the sum of a column based on 2 other columns in where clause (Magento 1.9.3.6)

I've got a bit of an interesting problem: I have a database where questions and answers are saved, each question/answer combo is saved as a separate row, sharing a participation id between each entry. ...
1
vote
1answer
1k views

Do you enable query_cache for Magento?

All tutorials for optimizing Magento performance I read suggest that we should enable query cache in mysql -> my.cnf However, I read a lot of other articles and even the mysqltuner script also ...
5
votes
0answers
386 views

MySQL log_visitor insert [closed]

I seem to be having loads of problems with this table in my Magento EE 1.14 build. I'm curious to know about what the stack community things about turning it off and the repercussions associated with ...
1
vote
2answers
3k views

When to truncate the core_session table?

I have roughly 35,500 rows of session data in this table. Is that a lot? Would this cause problems? How much is generally too much? Should i truncate it? It seems to be a problem so I was thinking of ...
2
votes
0answers
235 views

Everyday at the same time my website goes down for a few minutes

It gets put into maintenance mode. It has to be some type of job or module conflict or huge mysql query. I looked and there were no cron jobs running at that time. My new relic instance is saying that ...
0
votes
0answers
322 views

Magento sales_order_grid + join concatenated results from other query

We have some queries / extensions adding columns to the sales_order_grid as a 1:N relation. Normally 1:1 relations work fine. 1:N relation need a group by statement on increment_id and use ...
2
votes
1answer
390 views

Slow SQL DB after running query

After running a specific query in my DB, the entire DB is very very slow. I can see a huge decrease in performance on my 1.9.3.3 store. I did run the following query, which did not complete: SET ...
0
votes
2answers
1k views

SQLSTATE[HY000] : General error: 2013 Lost connection to MySQL server during query in Magento

My website was running perfectly and then I started to receive this error continuously. I tried to find solution almost everywhere I could, but found nothing that could help. I even talked to the ...
0
votes
0answers
175 views

Indexer query error empty values

When I run the indexer I get the following error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; This is caused by the following: LEFT JOIN ...
1
vote
0answers
266 views

Very Slow Magento MySQL Query - MySQL eav_entity_store select

Hi guys I have showing in new relic a VERY slow query on /checkout/onepage/success/ on multiple store views on my Magento installation. It is taking 57 seconds! SELECT `eav_entity_store`.* FROM `...
0
votes
1answer
41 views

Question about SQL query on collection - Magento 1.7

I'm displaying data in a grid using a prepareColumns function. I have two tables I want to join table "admin_user" to: table "customer_group" where both have 'customer_group_id' and I want to ...
0
votes
1answer
76 views

Resolved - Reading Database Table Info in Magento Way

I'm using this simple PHP code below to read data from my module's table, but can't do the same thing using Magento codes. Tried to do that using different articles but was either getting errors or ...
2
votes
2answers
2k views

How to print sql query for order collection factory in magento 2

I am trying to print sql query. But it is not printing. Please help public function __construct( \Magento\Framework\View\Element\Template\Context $context, \Magento\Customer\Model\...
0
votes
2answers
353 views

Getting all customer addresses which have empty required fields

I'm looking for a proper way to get all customer addresses that have empty required fields (eg. postcode, street,...) so i can later delete those. (This is due to a previous version in which empty ...
1
vote
0answers
513 views

How to get Exact products by query in Magento 2

When we search the products in search bar Magento 2 will search and display the Relevance products followed by http://domain.com/catalogsearch/result/?cat=&q=sample+product. I can see the method ...
-3
votes
1answer
2k views

How to Fetch Data And Update data using Direct Sql query in phtml file In Magento 2.1.3? [closed]

How to Fetch Update data using Direct Sql query in phtml file In Magento 2.1.3..
1
vote
1answer
333 views

Magento 2: How to convert query in Magento format

I have below query. How to convert it in to Magento format? SELECT GROUP_CONCAT(id) as ids FROM table WHERE YEAR(created_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(created_at) = MONTH(...
2
votes
1answer
1k views

Writting SQL query to remove product description from database table

I have magento 2.1.3 with following scenario: NL Main Website | NL Main Website Store | NL Store view EN Main Website | EN Main Website Store | EN Store view DE Main Website | DE Main Website ...
2
votes
0answers
392 views

Magento 2 - How to CAST & CONVERT Datatype VARCHAR to INT in query?

I have custom extension in magento 2. In that, saleslist table to save order & product list with seller. saleslist table mageproduct_id | order_id | seller_id | magequntity(varchar) ...
3
votes
3answers
4k views

Magento 2 Update record/table with where clause

How can I update record with multiple where clause? I know below method to update record with single where condition. I tried to used addFieldToFilter() but not succeeded. $objectManager = \...
0
votes
1answer
605 views

How to find and replace product title using sql query

I am having a magento store with English and Arabic language store view. I am having more than 5000 products(both simple & configuable products). All the products starts from Style now i want to ...
1
vote
2answers
3k views

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

I have below query $customers = array(1,2); $select = $connection->select() ->from( ['ce' => 'customer_entity'], ['entity_id', 'firstname', 'lastname', 'email'] // Select Only ...
1
vote
2answers
356 views

Error while trying to save a product

Whenever i am selecting any attribute or make any change in a product and try to save it in my magento store backend i am getting following error. SQLSTATE[42000]: Syntax error or access violation: ...
2
votes
2answers
1k views

Magento 2: How many products sold Query

For Product A have 100 qty., For Product B i have 200 qty. I would like to check how many products ordered for a particular product. Which table i need to look up & what will be the query to get ...