Questions tagged [query]
Denotes the question related to database SQL query( in context of magento ).
278
questions
0
votes
1answer
7 views
MySQL Query of catalog_product_super_link filtered by attribute_set_id
I want to export all fields from the catalog_product_super_link table, but filtered to exclude any products that have have a certain attribute_set (31 in this case).
This post (MySQL Query filtered by ...
0
votes
0answers
30 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
1answer
28 views
How to set the conditions for displaying of the related-products
Overriding related-product collection. My aim is to set limit for related product display. Related-products are not displayed if there are less than 10 products, the maximum number of products that ...
0
votes
1answer
21 views
Cron job to delete catalog_product_entity_datetime table data, am I using the correct interface?
I have a query to select data from catalog_product_entity_datetime table.
can I use below interface to grab the data from above table? If can't what should I do to grab data from this table?
I don't ...
0
votes
2answers
58 views
how to optimize queries in magento 1.9 with lots of attributes (over 500)
I have a website running Magento 1.9 which is flooding mysql with slow queries like this
SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
INNER JOIN `...
1
vote
1answer
36 views
Magento2 - How do I write below query function into non-deprecated? without using getCollection()
How do I write below function into non-deprecated? In Magento2. without using getCollection()
public function getNewCustomer()
{
$customerData = $this->customerFactory->create()->...
0
votes
1answer
21 views
How do I change this sql query to Magento2 filter collection method?
I have below query
SELECT * FROM newsletter_subscriber WHERE change_status_at >= (NOW() - INTERVAL 10 DAY);
How do I make it works with below?
public function getSubscriberStatus()
{
...
0
votes
1answer
36 views
I have no idea why my Subscriber Collection Factory is not working
I have no idea why my Subscriber Collection Factory is not working.
Below is my code. Wish someone can help me to fix my problem. I believe something wrong with my setup of CollectionFactory
use ...
0
votes
3answers
47 views
I have below sql query, how do I turn this to Magento get data method to retrieve data?
I have below sql query, how do I turn this to Magento get data method (collection?) to retrieve data (not using ObjectManager)? Please help.
public function getNewCustomer()
{
$objectManager = \...
0
votes
4answers
48 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
0answers
64 views
How can I write custom query in magento in magento 2 format?
// SQL QUERY
SELECT*, ( ACOS( COS( RADIANS( $clat ) )* COS( RADIANS( lat ) )* COS(
RADIANS( lng ) - RADIANS( $clng ))
+SIN( RADIANS( $clat ) )* SIN( RADIANS( lat ) ))* 6371) AS distance_in_km FROM ...
0
votes
1answer
36 views
How to add media query for div alignment [closed]
My design,
I need mobile view like,
first block second block
third block fourth block
How to include media query?
Script :
<div class="container">
<div class="...
1
vote
2answers
72 views
Retrieve product url with category in the graphQL
I've enabled at backoffice the usage of the category inside the product url by setting this option:
When I access the magento frontend, I can now access the product with the url <category>/<...
0
votes
1answer
22 views
Change stock availability of products based on qty
I'm looking for a query to get all my products that have "qty > 2 and 'stock availability = 0'" and change the stock availability to 1.
A crash on my inventory update routine did a mess ...
0
votes
2answers
36 views
how do I change this sql query to magento get data method?
I have below sql query, how do I turn this to Magento get data method to retrieve data? Please help
SELECT sales_order.increment_id, sales_order_payment.additional_information FROM sales_order LEFT ...
0
votes
0answers
24 views
Warning: Illegal string offset 'attribute'
I want to get products collection and I need to join it to several tables so I made this function
public function getProducts()
{
$products = $this->_productCollectionFactory->create(...
0
votes
1answer
74 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
0answers
68 views
Magento 2 - How to get new and best sellers products data using product graphql?
I want new products and bestsellers product data in product graphql. and also product review and rating.
Does anyone have an idea?
0
votes
0answers
9 views
SQL query to report 1:N EAV attributes
I'm building a query to extract a report of products and relative authors.
For Each SKU I can select n authors, authors attribute ID is 151.
This query returns comma separated values for ed.value ...
0
votes
1answer
334 views
Using GraphQL query and filtering products by category_id, I get empty items array
I try to make a simple request using graphql and to get a response from my magento. I have a parent category named Products(id:"3"). In this one, there are 3 subcategories A(id:"5")...
0
votes
0answers
14 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
91 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
15 views
Return raw procedure in Laravel
I try to execute a procedure, and this procedura have return, but didn´t work.
$retorno = \DB::connection('sqlsrv05')
->select(\DB::raw("DECLARE @DATA DATE SET @DATA = '".$data.&...
0
votes
0answers
180 views
magento 2 change store id
I have a multi store Magento 2 site and I need to change the ID of one of the stores. Is there any easy way to do this? I tried to run an update query on the store table but I get and error because ...
2
votes
1answer
87 views
Base images as first image, change positions in bulk - Magento 2.3.5
We have a large catalog (125K products), with a lot of products where the base image is positioned somewhere in the middle of the gallery.
The selection of base image itself is fine, this should be ...
0
votes
0answers
9 views
Make last image in galary Base, Small and Thumbnail query - Magento 2.3.5
The following sql query changes the Base, Small and Thumbnail to the first image.
We want to use something like this query, but then select the last image in position and make that Base, Small and ...
1
vote
1answer
16 views
How to Wirte OR/and Query in magento2.?
SELECT main_table.sku, main_table.order_id, main_table.parent_item_id, main_table.name, main_table.product_id FROM sales_order_item AS main_table
WHERE (order_id = '3')
AND (parent_item_id IS NULL)
...
0
votes
1answer
212 views
Magento 2 : get Mysql Query of products
I wanna show in my mysql query of all attributes name,product name, product status is 1 and sku but in my query its not showing product name and status so could you help me in my this query ?
SELECT ...
0
votes
0answers
246 views
SQL Query for get all orders with customer and shipping data (sql query)
Need help making a query that returns the data:
Customer Code,
Customer name (first – middle – last),
Customer Email address,
Customer Phone,
shipping Address (full address data segmented),
Order ...
1
vote
0answers
394 views
Magento 2 GrahQL Product Sorting does not work for the product query with category Filter
I have this graphQL query
{
products(filter: {category_id:{eq: "5"}, price: {from: "0", to: "999999"}}, pageSize: 10, currentPage: 1
sort: {position: DESC}) {
...
0
votes
2answers
22 views
What is the query readers here i can't understand magento 2.3
I can't find the what query rendes here and i can't it is not readable so any can to find what query renders hear please tell what are the table involved here
public function getCount(\Magento\...
1
vote
0answers
26 views
SearchCriteria vs Collection - Query Performance Difference
I am trying to refactor get category data method. Initially, it was written with Collection filter. Later I refactored with the search builder interface.
When I tried to compare the querying time, I ...
0
votes
0answers
153 views
Magento 2 - How to use mysql find_in_set?
In Magento 2, I try to use find_in_set mysql function but without success.
I try this :
$result->getSelect()->order("find_in_set(e.entity_id,'".implode(',',$sort_order)."')");
But it give me ...
0
votes
0answers
31 views
Magento2 store table the store_id needs changed from 2 to 0 to be reset
Not sure how it has occurred, but in our store table, the store_id has changed to 2, but should be 0 I believe for admin. What is the query to run to reset it?
We have backups with the correct values,...
0
votes
0answers
7 views
Get shipping from order
I'm doing a cronjob and I need to get the shipping value, this one
SELECT "shipment_label_id" FROM "shipment_label" WHERE "order_id" = 66591
The shipping is saved like that in just that table atm ...
0
votes
2answers
107 views
Create an SQL script to find a simple product name with its configurable product SKU In Magento 2 [Magento 2]
Create an SQL script to find a simple product name with its configurable product SKU In Magento 2 [Magento 2]
Anyone with a better solution than my one are appreciated.
Please share your answer.
1
vote
1answer
326 views
Magento 2 Long Running Queries Stuck in 'Sending Data'
We are running Magento 2.3.1 which has been running properly for a while.
Recently the site began to experience extremely long wait times whenever hitting pages not cached by Varnish such as ...
1
vote
1answer
115 views
Use of sub query within collection in Magento
I am using a subquery in Magento collection,
Here is my code.
$websiteId = $this->storeManager->getStore()->getWebsiteId();
$collection = $this->_productCollectionFactory->create();
$...
0
votes
0answers
52 views
insert custom option from sql query
I want to insert custom options for a product via sql query.
I tried with these queries:
INSERT INTO catalog_product_option ( sku, product_id, image_size_x, file_extension, option_id, image_size_y, ...
1
vote
1answer
258 views
Magento 2 addFieldToFilter check for NULL not working
What i am doing
->addFieldToFilter('value', array(['eq' => '', array('isnull' => true)]))
What i want in the query - '((value = '' )is NULL)`
Can anyone help me on this
Thanks.
0
votes
1answer
61 views
How to compare value inside array in magento 2 join query?
$this->getSelect()->join(
["address" => "sales_order_address"],
'main_table.entity_id = address.parent_id AND address.address_type = "shipping"',
array('region')
)->joinLeft(
["so" => "...
1
vote
0answers
206 views
Most bough items block porto theme: addition of a stock check
A client is using Magento 2.1.3 with the Porto theme. The homepage of that site has a div which has tabs of different categories with the most bought items. Per category it loads a block in the ...
0
votes
0answers
23 views
How do I get the value of a property in my product on my phtml file?
Im looking at my
theme/Magento_Catalog/templates/product/price/final_price.phtml
and I am trying to set up a conditional so that when a custom field that another developer built in as a dropdown is ==...
1
vote
0answers
24 views
Single product page have slow loading
When I open the single product URL (open product). I wait 8 seconds or more for the page to load. Responsible for this behavior is the amount of queries that magento is performing. For this I enabled ...
0
votes
2answers
63 views
check visibilty of specific product
I want to select a value for status or visibility on a specific product for which I have only the sku. I have tried many ways but did not arrive a a solution to the problem. Anyone can help me, ...
0
votes
2answers
245 views
URL Query parameters are being lost
I am having a strange issue with my product filter (layered navigation) not working, I have narrowed this down to the URL paramaters being lost because of a 301 redirect.
If I go to the URL... http://...
0
votes
0answers
33 views
Assoicated Products relation with Main Products, SQL Query
Our magento store products have three drop downs like color, size & weight. The price of main product changes when we select from size or weight etc. Stuck to run a query to get assoicated ...
1
vote
2answers
329 views
Magento 2: SQL get orders with specific option id
I'm trying to get all orders with a specific option id to see what percentage of orders are ordered with measurement of centimeters.
Not very good with database queries and I can't seem to figure it ...
0
votes
1answer
553 views
Magento 2 Reset Modify FROM select query
How can one modify the FROM in select query without losing the left joins innerJoin etc... ? as I have tried:
$collection->getSelect()->reset(\Zend_Db_Select::FROM)->from('table_2') but then ...
0
votes
0answers
192 views
Slow Query in Magento 1.9
I have continuously found below query in my slow query log, It seems the query is used for the site navigation.
It has done the re-indexing and flush the cache but still the same.
Here is the query:
...
