Questions tagged [query]
Denotes the question related to database SQL query( in context of magento ).
260
questions
0
votes
0answers
4 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
20 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
40 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
7 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
14 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
24 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
29 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
30 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
15 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
41 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
11 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
5 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
0answers
12 views
magento 1.9, how correctly create mysql escap
I want to save a lot of data in a table with one request
but this code don't working for me
Please,tell me how to implement the code correctly
$insertInto = array();
foreach ($...
0
votes
2answers
34 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
106 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
62 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
32 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
85 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
35 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
75 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
21 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 ==...
0
votes
0answers
33 views
Magento 2 usage of UNION in a custom query?
I am working on a module to remove images of all deleted products, which is not the standard M2 behavior. My initial query to found images path still used by products was :
$connection = $this->...
0
votes
0answers
25 views
Slow Query in Magento 1.9?
My Slow query log will print this query recursively:
SELECT `e`.*, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`, IF(at_is_active.value_id > 0, at_is_active.value, ...
1
vote
0answers
22 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
54 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
0answers
55 views
Get products within certain period with limit in sql query Magento 2
I am trying to SQL query (not Magento collection) to get at most 5 products day-wise which lie under my SQL date comparison check .
$sql = "SELECT `*` FROM `sales_bestsellers_aggregated_daily` AS `...
0
votes
2answers
96 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
30 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
108 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
285 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
131 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:
...
1
vote
1answer
114 views
Explanation about getSelect() join() joinLeft()
I don't understand these functions of Magento 1:
getSelect()
join()
joinLeft()
Where can I find a complete doc with all functions documentation ?
Thanks..
1
vote
0answers
46 views
Magento 2 : Which SQL query will be executed in attributes clean console command?
Does any one know which sql query will be executed while running below console command?
$ php bin/magento catalog:product:attributes:cleanup
1
vote
2answers
91 views
magento2 join query
I want to join the main table code with the product_options value giftcard_created_codes
My query till now
$this->getSelect()->joinLeft(['secondTable' => $this->getTable('...
2
votes
0answers
153 views
Magento 2.1.10: Sort by Special Price doesn't working
I'm working on category page, and try to sort the products collection by Special Price.
I've override setCollection() of \Magento\Catalog\Block\Product\ProductList\Toolbar
And the result is the only ...
2
votes
2answers
602 views
Magento 2: Allow empty query field in search
On the webshop that I'm working on, the user can find products by choosing filters (attributes like color/size/category) and/or entering a search query, the search results are then displayed on the ...
1
vote
1answer
50 views
How to solve warning “fetchAll can be memory inefficient for large data sets” in magento2
I am getting a warning as
"fetch All can be memory inefficient for large data sets",
When I did MEQP2.
Please anyone can help me to resolve this warning.
I am getting a warning in below line
$...
0
votes
2answers
149 views
How to resolve the “Direct object instantiation (object of \Zend_Db_Expr) is discouraged in Magento 2”?
I am getting warning as
"Direct object instantiation (object of \Zend_Db_Expr) is discouraged
in Magento2"
when I did MEQP2.
Please anyone can help me to resolve this warning.
I am getting ...
0
votes
1answer
32 views
Why magento2 outputs the query while showing data from ResourceModel collection Factory..?
protected $productCollectionFactory;
public function __construct(
.....
\Custom\MyModule\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
...
) {
...
1
vote
1answer
33 views
Update sales order immediately after inserted
I want to update the sales order right after it is inserted.
I want to reflect the change on the sales order conformation email also which is going to the customer.
So i have prepared the below code ...
1
vote
1answer
73 views
Magento 2 Custom Filters
Dear Magento 2 developers,
I am currently new in the Magento 2 development, now I struggle about how to make custom Product filter on the sidebar, like the picture below.
My new brand column in ...
0
votes
0answers
284 views
Category page take too much time to load because of search_tmp_* table
my sites category page slows down because firing query for search_tmp_* table take more than 1 second and it runs saveral time on one page load here is the query :
7624 ## QUERY
SQL: INSERT INTO ...
0
votes
1answer
255 views
Magento 2.3: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)
I'm very new to magento 2.3, I'm facing some error maybe from DB side....
Here is my code:
/app/code/EC/Customimport/Controller/Adminhtml/Index/Import.php
<?php
namespace EC\Customimport\...
1
vote
3answers
989 views
Quote id is empty when inserting multiple records into cart in magento2
I am adding product to cart from ajax by passing product id
Here is my controller code.
use Magento\Framework\Controller\ResultFactory;
use Magento\Framework\App\Action\Action;
use Magento\...
1
vote
0answers
310 views
Magento 2 - Query Builder
I need to create a query with searchCriteriaBuilder with this condition:
WHERE visibility = [value] AND status = [value] AND ((category_id = [value] AND designer = [value]) OR (category_id = [value]))...
1
vote
1answer
84 views
Magento 2 add query string to url when redirect to 404 page
Magento 2 custom module i am trying to add query param
https://test.com/index?id=L_FRR3039-ODKLWG-GP4QPY
if i give above format getting 404 page not found
https://test.com/index?id=L_FRR3039
it ...
3
votes
1answer
2k 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
22 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.
...
2
votes
2answers
558 views
Magento 2 query url rewrite
If I have URL like this -
mainpage.com/products?test_category=99
and want it to be rewritten to this
mainpage.com/testproducts
Since magento2 URL rewrite won't take query how can this be ...
