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

Questions tagged [query]

Denotes the question related to database SQL query( in context of magento ).

1
vote
0answers
28 views

Magento 2 - Query Builder

I need to create a query with searchCriteriaBuilder with this condition: WHERE visibility = [value] AND status = [value] AND ((category_id = [value] AND designer = [value]) OR (category_id = [value]))...
1
vote
1answer
15 views

Magento 2 add query string to url when redirect to 404 page

Magento 2 custom module i am trying to add query param https://test.com/index?id=L_FRR3039-ODKLWG-GP4QPY if i give above format getting 404 page not found https://test.com/index?id=L_FRR3039 it ...
1
vote
1answer
46 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 ...
1
vote
1answer
20 views

magento 1 query generated back tics are wrongly placed

I have an issue in Magento 1, where the SQL query that is getting generated has the back tics wrongly placed. This is a piece of the code that helps to provide the collection to the grid layout. ...
2
votes
2answers
91 views

Magento 2 query url rewrite

If I have URL like this - mainpage.com/products?test_category=99 and want it to be rewritten to this mainpage.com/testproducts Since magento2 URL rewrite won't take query how can this be ...
0
votes
2answers
32 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
24 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
0answers
19 views

Magento 2 Query in custom model file not working

I am working on the Magento 2 custom model file, my table name is custom_log, then I have created the following model file in my module directory. Test/Customlog/Model/Log.php namespace Test\...
0
votes
1answer
24 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 ...
0
votes
0answers
36 views

How to get Product Rewrite url in magento 2 by Query

I need Rewrites URL in Magento 2 by query. I tried below code. <?php namespace Dev\Core\Helper; use Magento\Framework\App\Helper\Context; use Magento\Framework\App\ResourceConnection; class Cart ...
1
vote
1answer
24 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
20 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 ...
2
votes
1answer
32 views

How to get the orders above the order id and How to use the limit in magento 2

I need to write code to get next 10 orders above the given order id. <?php namespace Learning\GreetingMessage\Controller\Index; use Magento\Framework\Controller\ResultFactory; use Magento\...
3
votes
2answers
271 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
93 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
1answer
123 views

Magento 2.2.5: how to query in observer best practice

I'm looking for best practice on how to query to database in observer. Ok so this is the idea, i use observer to catch event catalog_product_save_after (which will be fired after product is saved in ...
2
votes
2answers
186 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
1answer
65 views

The PurposeOfShipmentType is null, empty or invalid. please give me ans how to solve this?

2018-09-12T11:10:10+00:00 DEBUG (7): Array ( [request] => Array ( [WebAuthenticationDetail] => Array ( [UserCredential] => Array ...
0
votes
1answer
17 views

is reference block inside reference block is a good thing or bad?

Before anything, I already searched this online and didn't find anything satisfactory. I was working on a project where I had to add an extra column in order items section of every place. Everything ...
1
vote
2answers
43 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
50 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
68 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
2answers
133 views

Magento 2. What is the role of the search_query table

Magento 2. What is the role of the search_query table? Where is it used? Should I ever clear it? What would be the impact of clearing it?
0
votes
1answer
33 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... $...
1
vote
1answer
55 views

Magento 2 update query with AND

I want to run a update query but it is not working with multiple conditions. $where = ['parent_id' => (int)$shipmentId, 'order_item_id' => (int)$itemId, ]; ...
0
votes
1answer
45 views

Magento 2 set updated_time through query

I want to update column "updated_time" through query but it is not working. Where I am wrong? $where = ['entity_id' => (int)$customerData, ]; $connection->update( ...
0
votes
1answer
27 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 ...
4
votes
1answer
381 views

One to many relationship in M2

I am working on a module where I have to implement one to many relationship. I have one table for series with id and name field and one table series_product where I have id, series_id, product_id and ...
3
votes
2answers
542 views

How to Update a Column in Magento 1.9

How to make an "Update" query in Magento 1.9? I have no Idea how to do it. I added "filtered" column in catalog_product_entity table. I have the product I.D and here's my Query. $productId = '9'; $...
0
votes
1answer
142 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 ...
0
votes
0answers
18 views

Magento2: Points balance date filter on query

I have the following query for viewing the points balance, but I want to modify it to add a date filter, example, I want to know the points balance between febraury 2016 and july 2017. SELECT ce....
1
vote
2answers
670 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
57 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
55 views

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

I've below MySql query, I would like to change use it in Magento format. How can i do that ? SELECT customer_entity_varchar.value, customer_entity_text.value from customer_entity INNER JOIN ...
1
vote
1answer
145 views

SQL query: getting customer attributes (M1) - This type of clause was previously parsed

I'm trying to output customer information: total sales number of orders email dob phone firstname lastname country state zip code I am using this to create custom reports using this plugin: https://...
2
votes
1answer
749 views

Customer Repository Interface, Limit Results

I'm trying to grab the oldest customer, using the customer repository from the database. Here's my code so far: // if custom is active $isActivefilter = $this->filter ->...
0
votes
1answer
245 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
71 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
70 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,...
1
vote
1answer
788 views

Magento 2 direct sql query for insert not working

I was trying to insert values into mlx_cot_custom_option_type_value because module files are encrypted.I want to add something after product save i am using catalog_product_save_after event Here is ...
1
vote
0answers
115 views

select not null column collection query syntax in Magento2.2.0

This is my query, I want to execute in Magento2 function I have read some collection syntax of Magento2 but get confuse, can any one please write it in collection query syntax? Thanks!!! select * ...
0
votes
1answer
71 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'...
0
votes
0answers
27 views

How to change recurring profile page query (which gets all data)?

I want to order the recurring profiles table data by productname and order state. The table is currently ordered by order date. The only option I have is change the query in the back-hand. How do I do ...
2
votes
1answer
247 views

Magento 2 - Last inserted customer

Using models how would I get the last inserted customer, even if that customer was inserted 6 days ago? Would the best bet to get customers and order by date created and limit it to 1 result?
0
votes
1answer
48 views

Where condition in the collection issue in M1

This is my collection: $blacklist = Mage::getModel('mynamespace_mymodule/customers')->getCollection() ->addFieldToFilter('store_id', $order->getStoreId()) ->...
1
vote
2answers
108 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 * ...
1
vote
1answer
219 views

Query for counts of items on open-orders (1.9.x)

Does anybody have a query handy, or point me in the best direction for what tables to query, directly, to get a summary of. {magento product ID, my SKU, qty of that product} on orders with status {...
0
votes
1answer
360 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
259 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. ...