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
1
vote
0answers
20 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 ...
0
votes
0answers
26 views

Magento 2: Get used and unused attributes

I want to get list of attributes (not only products or categories) that are used and that are not use. I try this Delete unused attribute values one but it's only for product attribute, this is for ...
0
votes
1answer
21 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 ...
1
vote
1answer
83 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' ...
0
votes
1answer
224 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 ...
0
votes
0answers
24 views

setup:upgrade Magento Integrity constraint violation

I've enabled the module Magento_AdminNotification and ran php bin/magento setup:upgrade which threw the SQL error. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Stoßdämpfer-1' ...
0
votes
1answer
35 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.
0
votes
0answers
32 views

Hiding Thumbnail images from Gallery - SQL Query

A while ago I had a bad import in a store and hundreds of thousands of thumbnail images got set to show up on product page in Gallery. Most of the time, you don't want the gallery to show thumbnail ...
0
votes
0answers
11 views

disable configurable products if the associated products have quantity 0

Magento 1.9.x I have to enable the backorder. How can I disable all CONFIGURABLE products that have associated products with quantity 0? Is there a SQL query that allows me to do this? (via admin ...
0
votes
0answers
18 views

SQl Query to Get Product Magento2

I want to check how many product added and update daily on my catalog using sql query and i want following columns Sku Brand Category title product ulr image url price what sql i will write in mysql ...
1
vote
2answers
117 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://...
0
votes
0answers
20 views

magento 2 Cannot add foreign key constraint while importing admin_user table

I'm trying to export admin_user table from one database and importing it into another database. But it shows the below issue. #1215 - Cannot add foreign key constraint I have also disabled foreign ...
0
votes
1answer
16 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 ...
0
votes
0answers
7 views

Magento 2 getSelect with columns only showing one record in admin grid

I have a collection like below. protected function _initSelect() { $this->addFilterToMap('entity_id', 'main_table.entity_id'); $this->addFilterToMap('increment_id', 'main_table....
0
votes
1answer
24 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 ...
0
votes
1answer
24 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 ...
1
vote
4answers
157 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 ...
1
vote
3answers
45 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
0
votes
1answer
27 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?
1
vote
1answer
65 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\...
0
votes
4answers
50 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-...
0
votes
1answer
33 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 ...
0
votes
1answer
83 views

SQL query to get products enabled, in stock without images?

I'm running a Magento 2.3 and I would like to run a query in my database to export some products data that I don't have natively via the admin panel, the attributes are: Status Stock Images How ...
0
votes
0answers
9 views

Magento 2.3.6 - How to Bulk change existing & new Product image file name for SEO

How can I change the filename of existing & new product images as per SEO instead of just sku type names. I noticed some similar threads here, but those are for Magento 1. Can anyone pl help me ...
0
votes
1answer
25 views

Create a custom magento cron job

Is it possible to create a custom magento cron job. The task I want it can be done by run sql query using php to process some logic of the sql result send out email to customer service. cron job ...
0
votes
1answer
94 views

How to change product status from sql using SKU

I need to disable and enable a huge quantity of products filtering by sku and the easiest solution seem to be through Sql query but i have some issues to find the right code For example i need to ...
0
votes
1answer
20 views

Can not display all column from a table

I have a table name my_cars. There are 4 column in my table. I create collection, model, recourse model,controller and block. And i can display data from my table. But all column of data not ...
0
votes
1answer
56 views

Invalid security or form key. Please refresh the page. UI Form magento 2

I`m using this plugin , but i have another table structure. I can add new item to database bur if i edit item i get err Invalid security or form key. Please refresh the page. My table structure id, ...
0
votes
1answer
78 views

How to filter customer by custom attribute using SQL query?

The zd_user_id is our customer custom attribute. $customerCollection = $this->customerFactory->create(); $customers = $customerCollection->addAttributeToFilter( [ ['attribute' =&...
0
votes
1answer
57 views

Notice: Array to string conversion in shipping delivery on magento 2

I upgraded from magento 2.2.2 to 2.2.11 since I have an error when selecting a shipping address. I get the error: "Notice: Array to string conversion in mywebsite/vendor/magento/framework/DB/...
0
votes
1answer
665 views

How to get custom attribute value with product id of enable product with SQL query in Magento 2?

How to get custom attribute value with product id of enabled product with SQL query in Magento 2? I am using SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id = 150 But it ...
0
votes
0answers
15 views

Magento v1.9 cmd line sql query to find certain scenarios backorder out of stock

I have a real odd issue Magento v1.9.4.5 I have a ton like thousands of products that are set to back order allowed However they also say out of stock I need help I not sql expert I need a sql query ...
0
votes
1answer
61 views

Magento 2 delete Meta Name Meta Title Meta Description ALL products

Magento 2 delete Meta Name Meta Title Meta Description ALL products Please can someone let me know if its possible to delete the Meta Values for all products in my store These were added during import ...
0
votes
1answer
118 views

How to get SQL query for product insert in Magento 2?

How to get SQL query for product insert in Magento 2 ? I have multiple products around 10 Lakhs so i want to insert it using SQL query. But how will i get the insert query of the product ? Any Help is ...
0
votes
0answers
21 views

Error Code appears when editing product and category links no longer work on website

my website went down a few days ago, I use fast hosts, they reconnected my VPS but since then my category pages no longer work, products do not show on home page and whenever I save a product I get ...
0
votes
1answer
49 views

Move house number from street 1 into street 0 for existing address in db - Magento 2.3.5

At the moment we use the street 1 field for the house number in our Magento 2.3.5 store. But we want to revert this and only use 1 street field for both the street and house number. So we need to move ...
0
votes
1answer
119 views

How to get customer and company data in a CSV file from MySQL?

I'm running a Magento Commerce Cloud B2B, and I need to get a list of these data below. Customer ID Customer Name Company Name Company User Agreement As I can't export a file with these pieces of ...
0
votes
1answer
236 views

Magento2 - php bin/magento setup:upgrade - Access denied for user 'user'@'localhost' (using password: NO)

Fairly new to Magento, I am trying to develop a custom module and have run the CLI command php bin/magento setup:upgrade. However I am getting the following error; SQLSTATE[HY000] [1045] Access denied ...
0
votes
0answers
8 views

Magento 2 insert in concurrent series

I have a multiseller store. I'm creating a seperate seller invoice within series. My table data is like below id seller_id invoice_id invoice_series order_id For example if a seller name is John, I ...
0
votes
1answer
340 views

Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'

I am getting a SQL error on a few category pages after I upgraded to Magento 2.4.0. I do not have any errors during re-indexing. Flat tables are also disabled. Also, there is no custom code for the ...
0
votes
2answers
69 views

Magento 2 - Delete all products with ID 140.000 and higher

We have a lot of duplicate products in our Magento 2 store, that we need to remove. Now I am looking for a way or a sql query to remove all products from id 140.000 or higher. So all products from 1 ...
1
vote
1answer
760 views

Magento 2.3 - How to bulk import attributes values?

What is the best practice to bulk import attributes values. How to do it by hit tables?
1
vote
1answer
26 views

I need to set the 'OR' condtion for last where clause but it is setting as AND how can change in magento 2.3

SQL need to change AND condtion to OR in below code public function getOrders() { $po_number = "%"."A"."%"; if (!($customerId = $this->...
0
votes
3answers
60 views

Script to round all product prices in database to 2 decimal places

What is the best approach to go through all products PRICES (price, final price, promo price, group price ...) and round them to 2 decimal places. for example: 28,1200 -> 28,1200 28,1255 -> 28,...
1
vote
0answers
22 views

Magento 2.3 - Changed attribute media_gallery to scope “Store View”, but want to revert it to “Global” error

We changed the scope of the attribute media_gallery to scope "Store View", but we want to revert it back to "Global", like the default setting. But when we revert it, we get the ...
2
votes
1answer
266 views

How to print raw sql query by applying filter on searchCriteriaBuilder in Magento 2

How to print raw SQL query by applying filters on searchCriteriaBuilder ? I am using the following method, but it's giving error: "Uncaught Error: Call to undefined method Magento\Framework\Api\...
0
votes
0answers
36 views

How to customize SQL query

I have 1 module with model, resource model and collection. Now I can get data with SQL query: SELECT main_table.* FROM table_abc AS main_table; I want to customize this query by adding WHERE clause (...
0
votes
1answer
26 views

How to remove all special prices for a specific customer group

I accidentally uploaded special prices for an erroneous group of customers. How to remove all special prices for a group of customers, not login (customer_group_id = 0) for all products and items from ...
0
votes
1answer
26 views

convert sql query to magento addFieldToFilter combine OR and AND

You can convert me this sql I did not arrive at combined OR and AND at the same time SELECT * FROM sales_order AS main_table, sales_order_payment AS sop WHERE ((status = 'processing') Or ((status = '...
0
votes
1answer
130 views

Error while retrieving wishlist data

i am trying to retrieving wishlist data and this error appears [2020-06-22 08:41:10] report.CRITICAL: Report ID: webapi-5ef06ea694149; Message: SQLSTATE[23000]: Integrity constraint violation: 1452 ...

1
2 3 4 5
13