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
2
votes
3answers
55 views

How to write a Conditional Selection SQL QUERY in Magento2?

$collection->getSelect()->where($getMainTableAlias.'.entity_id IN (SELECT catText.entity_id FROM '.$catalogCategoryEntityTextTable.' as catText where catText.attribute_id="'.$attributeId.'" AND ...
2
votes
2answers
34 views

using sql queries is good practice in magento 2

using SQL queries is good practice in Magento 2 over ORM which is best one to while customizing 1)Security 2)Speed ORM vs SQL queries which one giving Security and speed while ...
0
votes
1answer
25 views

Integrity constraint violation: 1062 Duplicate entry '3-2-9' for key 'PRIMARY', query was: ALTER TABLE `catalog_category_product_index_tmp`

Integrity constraint violation: 1062 Duplicate entry '3-2-9' for key 'PRIMARY', query was: ALTER TABLE catalog_category_product_index_tmp ADD CONSTRAINT PRIMARY KEY (category_id,product_id,store_id), ...
0
votes
0answers
9 views

Magento 2 Categories SQL Import statement just placing meta data

I am trying to place meta data on already existing categories. I received the names of categories and the new meta data that is all. I was wondering if there is fast sql statement that will find ...
0
votes
1answer
80 views

Magento 2.2.2 Products Not Showing Category and List Page

I have a real hard time solving this issue. The issue is products Not showing on front end Category Pages and Product Listing Pages after import via CSV - Just shows We can't find products ...
2
votes
1answer
31 views

Magento 2 Error while running Cronjob : Integrity constraint violation

I have 2 cronjobs that serve data from suppliers to a Magento store (Magento 2.2 (Community)). This has been running for a few weeks without any issue, but this morning I got a whole list of error ...
0
votes
1answer
23 views

Understand EAV structure and related tables to get any attribute

For products I have created new attribute brix_level which is a text field. Now I need products data using SQL. I am good at quries, but the EAV structure is beyond my level :D Can anyone explain ...
1
vote
1answer
17 views

Pull data of orders having non-zero subtotal for each customer

I want the count of the orders which each customer has placed. Only those order which has non-zero subtotal should be counted. How can I get that data? Please let me know if we can use any SQL query ...
0
votes
0answers
16 views

Magento SQL Query Tuning

My Magento Store that I have taken over my another dev is currently displaying a high Time to First Byte (TTFB) of around 1s. I'm aware that lots more goes into this number that SQL queries alone, ...
0
votes
0answers
19 views

Fetch list of customer address having unique firstname and lastname from some specific customer group

I need to get list of address form specifc customer group, below is condition; 1) firstname & lastname should be unique in customer address group. I have tried below but it is not working: ...
1
vote
1answer
47 views

row_id vs entity_id in magento 2.3 EE

in magneto 2.3 EE , entity_id is missing in EAV tables , instead row_id is available please explain the difference between row_id and entity_id and the removal of enity_id in EAV tables
0
votes
0answers
50 views

Magento2 Database Lock wait timeout exceeded

In my magento admin panel i am getting below error. SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: UPDATE flag SET state = ?, last_update = '...
2
votes
1answer
33 views

Join from custom table to customer_entity table and get all data customer in Magento 2

I used this code but it not working. Please help me! $customerEntity = $locationList->getTable('customer_entity'); $locationList = $this->mpSellerMapLocatorModel->getCollection(); $...
3
votes
1answer
56 views

{“0”:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name'

Any Help Highly Admired, i'm using Magento 2.2.8 did all commands,upgrade, reindex, static file , etc still not worked {"0":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name' in '...
1
vote
1answer
20 views

Magento 2 Custom table collection Conditions

I'm facing an issue with the dynamic parameters for the filter $collection = $this->_collectionFactory->create()->addFieldToSelect('field_name'); If I have below code it works fine if the ...
0
votes
0answers
34 views

Unique constraint violation found

when edition the product for the backend, I faced the unique constraint violation found in the Magento-2.3 . I have checked the exception log this shows SQLSTATE[23000]: Integrity constraint ...
0
votes
1answer
12 views

Bulk adding & updating product attribute

[M1] I have over 2000 products in my Magento store. I want to add a new attribute called 'Warranty Terms' to display it on the frontend. This is going to be the same for all products. I know how to ...
1
vote
2answers
46 views

How to check if column exist before trying to add it to the table?

We are using the Extension FollowUp E-Mail from AheadWorks. The Extension has bugs in the sql install scripts... they are trying to add a column to a table but don't check if the column does already ...
0
votes
1answer
29 views

Index sql error

When I run indexer:reindex I get the following SQL error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your ...
0
votes
0answers
13 views

Magento 2 Data Migration SQL error 'Column not found' Column in m2 DB but not in m1 DB

im having this error: Its the first data migration step, the integrity step. Anyone knows what to do with that? Thanks!
0
votes
0answers
20 views

magento2 Sort is not working on PLP due to entity_id

We are working on Magento 2.2.7. On PLP, sort by price is not working. This is because in the query it first sorts the items by entity_id then only using the price field. Example query is given below ...
0
votes
0answers
18 views

Get the list of most seller combined products with its orders

I want to get the most selled product combinations along with number of orders( and Orders if possible). I have customized the Magento tables for my warehouse activities and the sample code will be ...
1
vote
0answers
25 views

How can I change multiple entries in DB?

In DB I have Rows: ID NAME PHONE Example: 1 Steave 33333333 2 Joe 77777 I can add and Edit any row, but I want when I click "save everyone" I rewrote all phones on DB example: 1 Steave 33333333 ...
0
votes
1answer
52 views

Insert, Update values in existing custom table and Select values from that table. Magento2

How can i Insert, Update values in existing custom table and Select values with Magento methods not sql requests ?
0
votes
1answer
55 views

Magento 2.3 introduced a view table which causes issue with import of mysql dump. What's it for?

If you mysql dump a magento 2.3 store and try to import it elsewhere you get an issue due the the format of the view import query. The problem part is this: CREATE ALGORITHM=UNDEFINED DEFINER=`...
2
votes
2answers
22 views

How get products URL in magento 2.2.5 sql database

I am using magento 2 enterprise edition i am trying to get products URL from sql database for this i have seen catalog_products_entity_varchar but in value attribute column products imgages are given ...
1
vote
1answer
27 views

How get products name magento 2 sql database

I am trying to get products name(value) and when i see my db table catalog_product_entity_varchar but in which in the column of value all base images are given so please tell accurate table or any ...
0
votes
1answer
19 views

Hiding products with £0.00

Magento 2.3, how can I hide products automatically via cron for products that have a £0.00 price? I use a 3rd party to calculate prices across my store and when some products cannot be matched in ...
0
votes
1answer
13 views

Magento 2 - Hide Products with no Images via PHP SQL Query

I have an SQL query that i run on the database manually which checks the catalogue for products that do not have an image and hides them, i want to run this via cron so have created a php with the ...
3
votes
2answers
168 views

Column not found: 1054 Unknown column 'search_result.score' in 'order clause'

I'm searching on the internet for an answer but no luck so far. I have 2 Magento 2 webshops. One is version 2.2.7 and one is 2.3.1. Both have the latest Porto theme installed and both work with the ...
0
votes
0answers
38 views

Magento2: Cardinality violation: 1241 Operand should contain 1 column(s)

When i delete the item from cart then got the SQLError below: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s), query was: SELECT `catalog_category_entity`.* FROM `...
0
votes
0answers
23 views

Huge slow query for products, categories, filters

I run a Magento 1.9 based store with around 40 thousand active products. Using an FPC I am able to keep the server stable sometimes, and other unstable. While checking for highest hardware consumption ...
1
vote
1answer
57 views

Event Observer for import with magmi

I created an event observer to report the creation of new magento products, but the observer doesn't work when the products are created by importing Magmi. My magento version is 1.9.3.9 This is the ...
1
vote
3answers
81 views

2.3 to 2.3.1 Upgrade showing sales_order_grid already exists

So far the upgrade went pretty smooth but now I have a few outdated modules and it's asking me to run the setup:upgrade on CLI but I keep getting an SQLSTATE general error: 1813 Tablespace for table '...
1
vote
1answer
310 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
49 views

Magento 1: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous

I am getting the Error: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous SELECT `main_table`.*, CONCAT(soa.firstname,' ', soa.lastname,'', soa.street,'', soa....
2
votes
2answers
215 views

Custom report from SQL query

I want to get order total of the month January 2019 for complete orders I need to know tables related to this query, or if someone can share query that would be great.
2
votes
1answer
64 views

Error when importing configurable product programmatically

I am programming a custom import system with Magento libraries, but when I import configurable products I get an SQL error. This is my configurable product function (only a part of my code): ...
2
votes
1answer
74 views

Magento 2 delete search term through sql query

I want to delete search terms with 0 search results from magento 2 database by writing sql query in file. How can I do this?
1
vote
1answer
21 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. ...
0
votes
0answers
34 views

Deadlock when updating products

I'm trying to update a few products in Magento with this code: Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); $products = Mage::getModel('catalog/product') ->...
0
votes
0answers
27 views

Difference between Sales order and database

In Magento 1.9 when generating a Sales order report (Report -> Sales -> Orders) for February I expect the same value total in invoiced column compared to running below: select sum(base_total_invoiced)...
2
votes
2answers
47 views

how to calculate sum of selected entries of a column in Magento 2

I want to calculate sum of all total_purchase values whose ref_by value is 1. Table name is table_name. Can someone please help me. I want sql query which will return total (200 + 97 + 140)
2
votes
0answers
36 views

Magento Installation Error: SQL max_allowed_packet value

I am trying to install Magento 2.3.0 CE with Sample data, but the installer fails because my hosting have the max_allowed_packet set to 16M. In this post of someone with the same problem the issue is ...
2
votes
2answers
65 views

SQL Error when I try to reindex Magento 2

I have an issue in Magento 2.2.7 where my site has the message "One or more indexers are invalid." and looking at the indexers I see the following: However when I try to run php bin/magento indexer:...
0
votes
1answer
235 views

Clearing database logs in Magento 2

We've recently migrated from Magento 1 to Magento 2 and it seems that there is a large amount of information in our database files. We'd like to try to mitigate this by clearing the log files but ...
1
vote
1answer
27 views

SQL Query Newsletter subscription status with custom tables

I currently have the following script that someone else wrote for me that exports custom Magento data to a CSV file. It takes data from the two tables 'perfume_fragrance' & '...
1
vote
0answers
28 views

magento 1.9 CE error when duplicating a product

When I use the method to duplicate an object sometimes I get this error message, the product is created: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-1-4-24884-0' for ...
1
vote
1answer
28 views

Magento 2.2.5 Delete Url Rewrites older than 1 Year

Is it possible to delete Url_rewrites older than one year? We have migrated from Magento1 to Magento 2 and kept all URLs for SEO reasons. BUT the table in Mage1 and Mage2 is really huge. Now we want ...
2
votes
1answer
54 views

Magento 1.9 - Change Tax in created invoice

Unfortunately somebody changed the tax class for many of our products. We needed 2 weeks to recognize that. Now I got 150 orders with wrong tax. I need to fix that and resend all invoices to the ...