Questions tagged [sql]
Questions about using raw SQL queries. For questions about the database in general, use the database tag instead, for questions about the Magento database access layer use the appropriate tag, like: eav, model, resource-model, collections, install-script
665
questions
0
votes
2
answers
27
views
How can I remove a condition from a collection
I tried to use two methods to reset or clear the collection but it didn't work.
I am working with the category collection on which I want to remove this part of the condition : (e.created_in <= '...
- 77
1
vote
1
answer
34
views
How to Solve 404 error on frontend?
Getting 404 error on frontend page backend Admin panel is perfectly working frontend page only through error what is the reason behind that
0
votes
2
answers
33
views
Unexplained 'EXPLAIN' SQL Queries in Magento 2 Production Database
Greetings forum members,
I hope this message finds you well. I am currently facing an issue with my Magento 2 production database and would greatly appreciate any insights or assistance from the ...
- 5
0
votes
1
answer
21
views
Custom sql to get Magento2 EE reward point data
I am trying to write a custom sql that will provide to me data related to the Magento2 EE reward points. My issue is that my queries seem to be showing 0 as the result which can not be because when I ...
- 707
0
votes
0
answers
24
views
Check if database address value is empty in $collection?
How can I check if the field "company" contains a value or not, and set another value ("not a company" for instance) if empty?
class OrdersGrid
{
public function afterGetReport($...
- 292
0
votes
1
answer
36
views
Magento 2 - Query to know the user who update the price of the product
I am very new to magento. What is the query to to know who was the user who updated the price of a product?
I just want to know the query, not the code.
Thanks in advance.
- 15
0
votes
2
answers
66
views
Convert SQL Select Query to Magento Collection
I have a complex sql select query which actually returns sales report against specified categories. I want to load data as a collection to display but I'm not able to convert the query into Magento's ...
- 1
0
votes
1
answer
73
views
Category name from store view, only when it exist else default value - Magento2.4
We use the following code, to get the category name of a specific product.
This works fine, but we face the issue with the store views. It only works well if the specific store view does have a unique ...
- 1,446
0
votes
1
answer
22
views
Get random products error
I know that there is a bunch of questions on this topic but no one resolve my problem
//@var Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $this->_productCollectionFactory
$items = $...
1
vote
0
answers
51
views
Export products and additional attributes - SQL query for Magento 2
What would be the QUERY SQL to export the SKU columns and ADDITIONAL_ATTRIBUTES as shown in the product export module n Magento 2?
- 11
0
votes
0
answers
17
views
New customer accounts are pulling sales data from other customers' from months ago
We recently migrated to a brand new magento 2 build and migrated all previous order data over from the old server to the new server, but now we have a major issue with random and old customer data i.e....
- 1
1
vote
3
answers
248
views
Query to get the product that has special price with from and to date
create a SQL query for getting product's sku which does have special price from and to date
- 11
0
votes
2
answers
139
views
Magento 2 - Is it bad practice to directly use SQL in php?
We are using a third party module. They have their own database tables and read out data from it by using direct SQL statements
app/code/Company/Foo/Model/Calculator.php
...
public function foo($...
- 2,993
0
votes
0
answers
80
views
Get all products + product attributes under a category using MYSQL query
Is it possible to formulate an SQL that gets all products along with its product attributes (instead of just name, description, etc as what I have mostly seen on the internet) of a certain category?
- 44
0
votes
0
answers
10
views
Load data in Admin Product Listing except some entities included in some other database table!
I want to show all the products in product listing except some entities which are included in some other db table (these entities are also included in catalog_product_entity table), I just want to ...
- 1
1
vote
1
answer
120
views
Magento 2 Extremely high load on MySQL due to select query
I've seen similar questions asked on this site, but haven't seen a solution or fix to resolve the problem. Our site is spawning a large number of select queries to the point where mysql is overloaded ...
- 11
0
votes
0
answers
34
views
Magento2 SQL question. I am trying to extract invoices and attached information from database. Is that syntax correct?
SELECT
si.order_id AS 'ORDER NUMBER',
so.created_at 'ORDER DATE',
si.created_at AS 'INVOICE DATE',
so.customer_email,
so.grand_total,
IF(
_payment.method = 'checkmo',
'BANK CHECK',
IF(
...
- 73
0
votes
0
answers
99
views
How to select all products that have the same manufacturer
I am trying to do a select query where the results will be on column the manufacturers and the other the total price of items sold by that specific manufacturer.
I have managed to create two separate ...
0
votes
0
answers
134
views
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; ( Can't Create Configurable Products)
I am facing this issue, while creating the configurable products.
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to ...
- 483
0
votes
1
answer
30
views
how do i make an admin grid based on sales_order table (some columns but not all) using best practice?
Let's say I need an admin grid
where not all columns of the sales_order table will be,
but only some of them. For example:
customer_id
customer_name
nickname
How best to solve such a problem?
- 1
0
votes
1
answer
49
views
db_schema can't set foreign key - Table [prefix]_sales_order do not have column with name "name"
I must add column1 to sales_order_grid and create foreign key to
table sales_grid.column1
When i try use bin/magento setup:upgrade i get next message
Table [myPrefix]_sales_order do not have column ...
- 1
1
vote
1
answer
773
views
Select specific columns from a JOIN
I am using this class
$resource = \Magento\Framework\App\ResourceConnection;
connection = $resource->getConnection();
to get data from some custom tables using a JOIN
$select = $block->...
0
votes
2
answers
77
views
magento2 run sql query display Single and double quotation
Direct SQL Query in Magento 2
$sql = "UPDATE ".$table." SET `value` = '".$newMetaTitleValue."' WHERE value_id = ".$id;
$connection->query($sql);
dispaly result
...
- 95
1
vote
0
answers
114
views
Slow query in slow query log
SELECT 1 AS status, e.entity_id, e.attribute_set_id, e.type_id, e.created_at, e.updated_at, e.sku, e.name, e.description, e.short_description, e.price, e.special_price, e.special_from_date, e....
- 11
0
votes
1
answer
94
views
Customer Query - SQL (Magento 2)
I want to build a sql query for customer. Information needed:
customer-email
customer-type (individual user, company admin,
company user)
Can you help me to build such a query?
BR
- 1
0
votes
0
answers
42
views
SQL Queries not executing
As the title says, I am running a few queries on a Magento 2 DB, but they never execute. When I run the queries on the server, the DB disk gets full and the queries don't stop loading, but when I run ...
0
votes
2
answers
391
views
Magento 2 - Customer Type Table SQL
I need to create a query that will list me as in the admin panel all registred customers, of course, I can do it through the tables customer_entity. However, it does not contain the customer type ...
- 1
0
votes
1
answer
92
views
Will the same php and mySQL versions work for both Magento 2.4.2-p1 and Magento 2.4.4?
I have a Cloud VPS account which currently runs Magento 2.4.2-p1.
I would like to install Magento 2.4.4 in a separate account in that Cloud VPS but I'm told we will have to upgrade php and mySQL for ...
- 164
0
votes
1
answer
15
views
JSON functions in MBI SQL builder
According to the docs, all functions that are not mutations should be included in the SQL syntax. I have a MySQL integration in MBI, but when I try to test some basic SQL functions such as ...
- 101
1
vote
1
answer
809
views
Magento 2 - How to find all products with Salable qty as 0 via SQL query?
Is there a simple SQL query I can run to find any ENABLED product in Magento that has a salable quantity which is 0?
We have thousands of products and in the back end product grid we can sort and ...
- 23
0
votes
1
answer
67
views
Database loading Product Collection slow (Minutes)
I've got some third party code here that loads a larger product collection. It takes around 9 minutes to load on decent hardware. We have some 150000 SKU in the database.
Collection:
SQL is:
SELECT ...
1
vote
2
answers
3k
views
Magento 2: SQL Query to get product SKU's based on the Category Id
I need a sql query to get the all sku based on the category id
Following are the tables which stores category and product data:
catalog_product_entity
catalog_category_product
Thanks
- 1,630
0
votes
2
answers
40
views
Magento 1.9: Sql setup Stripe module not execute
I'm installing throught composer the Stripe module, the problem is that when I installed the site, where the module work, crash because can't find the table in Database.
I don't know why the setup sql ...
- 1
0
votes
1
answer
284
views
Is there any way to get enabled and disabled simple product of configurable product in Magenoto 2?
Is there any way to get enabled and disabled simple product of configurable product in Magento 2.
I need to get all id of enable and disabled product by using SQL query or object manager approach.
Can ...
- 626
0
votes
0
answers
97
views
SQL error on Database Import, while setting up staging site
I have been trying to import a database to clone a production site that is working and I am receiving this error when trying to import the database.
Error
Static analysis:
2 errors were found during ...
- 103
0
votes
2
answers
39
views
Magento: Reduce product weight by 1000x for all products
We migrated from Magento 1 to Magento 2. All weight values were added in grams in Magento 1.
In Magento 2 the weight setting is in KG. So all product weights are 1000x too much.
What SQL query can I ...
- 1
1
vote
1
answer
128
views
Assign orders to customers where the emails are matching with a SQL query
I want to reassign existing orders to customers after deleting all customers and importing them into Magento 2 from a csv. The sales_order.customer_id values are not matching with the correct customer ...
2
votes
1
answer
374
views
Magento 2 : Get product name using mysql query
I am using Magento ver. 2.4.2
Is their any way we can get product attributes by id
using mysql query ?
I also need some custom attributes for product in same query
THE BELOW SQL QUERY WORKS BUT GET ...
- 467
0
votes
1
answer
78
views
SQL Syntax error when getting collection on listing
I've created my own module for managing leaflets in home page and i wanted to have a listing with existing leaflets. I've created UI_Component for listing (by Magicento) but when I go to listing page ...
- 27
1
vote
1
answer
84
views
Why does it matter in which order I add attributes to a filter?
Compare this code
$productIdQuery = $this->productCollectionFactory->create()
->addAttributeToSelect('customer_group')
->addAttributeToFilter([
['attribute' ...
- 13.7k
0
votes
1
answer
3k
views
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: After Updating a Module
On My site i am running old version of Advanced Product Options Suite Extension for Magento 2 by mageworx. I just updated the Advanced Product Options Suite Extension into latest version. I tired ...
- 101
0
votes
1
answer
157
views
How to import 3 tables dump into database using script?
I want to include 3 tables SQL dump into that database, how to add it by using a custom module or script or any best practice.
- 145
1
vote
2
answers
2k
views
How to get all products info from database by SQL Query in Magento 2.3?
I want to get the below data of all products by SQL Query.
SKU
Product name
Website
Attribute set
Quantity per source
Sellable quantity
visibility
Price
Status
I have used this extension:
https://...
- 524
0
votes
1
answer
181
views
SQL is_in_stock field not updating to Website
I am updating is_in_stock field in cataloginventory_stock_item as follows:
UPDATE cataloginventory_stock_item ci SET ci.is_in_stock = CASE WHEN ci.qty = 0 THEN 0 WHEN ci.qty > 0 THEN 1 END WHERE ...
- 11
0
votes
1
answer
102
views
Re-indexed procedure in Magento 2
If i update 10K products and re-indexed it first time it will re-indexed all.
But if i update 10 more product so it will require re-index so my question is is it re-index all the products from ...
- 626
0
votes
1
answer
188
views
How to Make Indexing process Async. in Magento 2
I am using Indexing process in Magento 2.
I have 60 Million products and when i run indexer it just struck in processing from past 4 days.
When i check indexer status on sow processlist it will run ...
- 626
1
vote
4
answers
2k
views
Where is URL Key information stored in database?
I'm using a mass import extension to import product URL Keys for a Magento 2.3.0 site which recently went live. The URL key information in the backend product detail is correctly updated but Magento ...
- 111
0
votes
3
answers
275
views
I want to fetch Product Names on behalf of comma separated SKU's
I want to fetch Product Names on behalf of comma separated SKU's i.e. sku1,sku2,sku3 and the expected result is Name1,Name2,Name3
- 185
0
votes
1
answer
329
views
How to export products with tier prices directly via database using SQL?
I want to export these data from my Magento 2.4:
Status
Product Name
SKU
Price
Advanced Pricing
Quantity
How could I do it via SQL?
- 12.6k
1
vote
1
answer
477
views
Magento 2 - How to get data from table customer_log?
I need to get the value for last_login_at for the current customer, but this seems to be impossible without the use of SQL.
Current Solution (SQL):
/**
* @var \Magento\Framework\App\...
- 2,993