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

Filter by
Sorted by
Tagged with
0 votes
0 answers
14 views

SQL for getting promos applied to an order

I require an SQL query to retrieve information about the promotions applied to an order from the sales order table. The field "applied_rule_ids" is utilized to obtain all rule IDs. However, ...
Shiffin Thomas's user avatar
0 votes
1 answer
126 views

List of log tables to safely truncate in Magento 2?

For Magento 1, here is a list of log/cache tables which can be safely truncated. Is there a similar list of tables for Magento 2, which can be truncated/deleted without deleting base data? Please note ...
amitshree's user avatar
  • 6,986
0 votes
1 answer
267 views

Magento 2 Website Id 0 Not Found Even though it IS in the Database SQL Tables

Running php bin/magento setup:upgrade returns this error 'The website with id 0 that was requested wasn't found. Verify the website and try again.' Running php bin/magento setup:db-data:upgrade will ...
Reckless Games's user avatar
0 votes
1 answer
52 views

Setup Production Database on Staging server in Magento 2.4

I downloaded the production database. Now I want to setup the database on the staging server. So what sanitize process I have to apply on the database before uploading to the staging server.
Kushal Dani's user avatar
  • 2,114
0 votes
3 answers
417 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 <= '...
Alok Tiwari's user avatar
1 vote
1 answer
636 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
user avatar
0 votes
2 answers
125 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 ...
Magenteiro's user avatar
0 votes
1 answer
49 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 ...
Jayreis's user avatar
  • 705
0 votes
0 answers
43 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($...
Aknot's user avatar
  • 288
0 votes
1 answer
77 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.
jsnewbie21's user avatar
0 votes
2 answers
318 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 ...
jack's user avatar
  • 1
0 votes
1 answer
95 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 ...
JGeer's user avatar
  • 1,416
0 votes
1 answer
24 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 = $...
C0casio45's user avatar
1 vote
0 answers
89 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?
heroecall's user avatar
1 vote
3 answers
537 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
Reshika's user avatar
  • 11
0 votes
2 answers
303 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($...
Black's user avatar
  • 3,280
2 votes
1 answer
283 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 ...
Mike C's user avatar
  • 21
0 votes
1 answer
31 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?
esca7a's user avatar
  • 21
0 votes
1 answer
110 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 ...
esca7a's user avatar
  • 21
1 vote
1 answer
1k 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->...
Camilo Torres's user avatar
0 votes
2 answers
191 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 ...
sq y's user avatar
  • 127
1 vote
0 answers
139 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....
aamtiaz's user avatar
  • 11
0 votes
1 answer
132 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
Bartek's user avatar
  • 1
0 votes
0 answers
59 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 ...
LittleDTLe's user avatar
0 votes
2 answers
678 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 ...
Bartek's user avatar
  • 1
0 votes
1 answer
135 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 ...
Allysin's user avatar
  • 250
0 votes
1 answer
31 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 ...
the_darkside's user avatar
1 vote
1 answer
1k 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 ...
jvb8's user avatar
  • 23
0 votes
1 answer
102 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 ...
Alexander Gran's user avatar
1 vote
2 answers
4k 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
Rahul Singh's user avatar
  • 1,650
0 votes
2 answers
56 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 ...
Luca's user avatar
  • 1
0 votes
1 answer
388 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 ...
Prits's user avatar
  • 626
0 votes
0 answers
142 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 ...
Jonathan's user avatar
  • 113
0 votes
2 answers
51 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 ...
JDee's user avatar
  • 1
1 vote
1 answer
162 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 ...
cookie monster 24534's user avatar
2 votes
1 answer
488 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 ...
devhs's user avatar
  • 497
0 votes
1 answer
190 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 ...
LeTeeL's user avatar
  • 42
1 vote
1 answer
85 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' ...
Alex's user avatar
  • 13.8k
0 votes
1 answer
5k 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 ...
developerme's user avatar
0 votes
1 answer
190 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.
zus's user avatar
  • 155
1 vote
2 answers
3k 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://...
Amit Saini's user avatar
0 votes
1 answer
231 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 ...
Skater's user avatar
  • 11
0 votes
1 answer
162 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 ...
Prits's user avatar
  • 626
0 votes
1 answer
297 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 ...
Prits's user avatar
  • 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 ...
Sparko's user avatar
  • 121
0 votes
3 answers
340 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
Abdul Rafay's user avatar
0 votes
1 answer
413 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?
Rafael Corrêa Gomes's user avatar
1 vote
1 answer
615 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\...
Black's user avatar
  • 3,280
0 votes
4 answers
959 views

how to use date() in magento query?

I want to filter some orders based on the date range so in SQL i use this query SELECT * FROM `sales_order` WHERE date(created_at) >= '2020-12-02' AND (date( created_at) <= '2020-12-...
Friska Sianturi's user avatar
0 votes
1 answer
73 views

How do I change not using ObjectManager directly? and use Dependency injection?

How do I change not using ObjectManager directly? and use Dependency injection? what about my SQL query? My code is work, but seems not in good Magento style, anyone can help to correct me? I am ...
Paul Fan's user avatar
  • 654

1
2 3 4 5
14