The query tag has no usage guidance.
0
votes
2answers
20 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
27 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
19 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
22 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
18 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
164 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
62 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
37 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
31 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
11 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....
0
votes
2answers
82 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
52 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
42 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
61 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://...
1
vote
1answer
54 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
37 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
0answers
24 views
SQL Query to remove Images for Select SKUs (Image, Small Image, Thumbnail, & Gallery)
I have thirty some products that need to have new images added as the old ones were missing some touchups and other info. I could do this one by one, or with the Amasty Mass Actions, but not all the ...
0
votes
0answers
89 views
Getting 'Coupon with the same code already exists' in magento 2.2.1
In Magento 2.2.1, if I delete Shop Cart Price Rule, it gets deleted but for some reason, the coupon code which was associated with that rule is still left in the database.
So, if I try to create a ...
0
votes
2answers
44 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
25 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
0answers
70 views
Join sales order and productCollection
I have to join tables which is sales order table and product collection table i have the query for magento1 but i do know how to perform this same action in magento2?
$itemsCollection= Mage::...
1
vote
0answers
309 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
72 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
39 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
23 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 ...
1
vote
1answer
112 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
40 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
68 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
91 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
175 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
173 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. ...
0
votes
0answers
74 views
Magento 2 Request Interface Example
I'm looking for an example or several that shows how a
Magento\Framework\Search\RequestInterface
is build, with no luck. Can anyone point me in the right direction?
Context:
I'm using Magento2 ...
4
votes
1answer
463 views
Magento 2:SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'entity_id' in where clause is ambiguous
I have added a new column in customer grid using the following:
app/code/Company/Module/etc/di.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:...
0
votes
0answers
33 views
Column SubSelect in Magento Collection
Here's my original query in Collection:
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->getSelect()->join( ...
1
vote
0answers
82 views
Magento 1.9.3.6 sql query returns empty set
I have a function getProductDatat($params = '*') in which it use the default read connection and I build the following query (simplified):
$select = $conn->select()->from('product', $params)
...
0
votes
0answers
112 views
Can a custom grid be populated with custom data in Magento 2?
I've created a Magento 2 extension containing two custom grids. The first reads directly from a custom table in the database. I want the second to display data retrieved with a specific query using ...
1
vote
1answer
616 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 ...
1
vote
1answer
51 views
Magento 2 : Load/Get Orders by Direct Queries
I have multiple Order Id in foreach loop, and I want to load or get orders by these order_ids.
I have used this code
if ($requestName == 'sales_order_grid_data_source') {
if ($result instanceof $...
0
votes
0answers
25 views
Change price_index.website_id from 0 to 1
I used this code to get all products on sales.
$_productCollection = Mage::getModel('catalog/product')->getCollection();
$_productCollection->addAttributeToSelect(array(
'...
5
votes
0answers
225 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
1answer
984 views
Magento 2: Get Product URL and SKU using SQL Query
How can I get all SKUs and Product URLs using only SQL Query?
1
vote
2answers
1k 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
152 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
175 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
237 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
591 views
Magento 2: Collection Query addFieldToFilter Loop Issue
I have below code
$_objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$orderRepository = $objectManager->get('\Magento\Sales\Api\OrderRepositoryInterface');
$customModel = $...
5
votes
1answer
196 views
characters like æ ø å ö ä ü for display in magento
When I display Danish/Norwegian characters like "æ ø å", the current character and the rest of the text in that field is gone in magento (Magento ver. 1.9.3.2).
Example for ::
Input filed:
"FJORD [...
4
votes
1answer
107 views
getModel attributeToFilter overridden by server default status?
I'm trying to get all the products where the qty is above 0 and is_in_stock on 1.
But when i'm trying to use the following code it overrides the addAttributeToFilter.
$collection = Mage::getModel('...
0
votes
2answers
676 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
30 views
Magento 2: How to Enable Query Logging? [duplicate]
We can do db logging via
<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File" />
But it's Module Wise.
How can we do whole Magento wise (Fronend/...
