All Questions
Tagged with collection database
98 questions
0
votes
1
answer
1k
views
Get specific columns from Left Join
I am trying to join two tables like below
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$collection = $objectManager->get('Advance\Module\Model\Method')
->...
CommunityBot
- 1
2
votes
1
answer
1k
views
Magento 2 - Best way to query data from tables (select with multiple joins)
I'm learning about Collections, Repositories and DTO, and I'm still trying to figure out what's the best way to query a table to fetch data. I also found that we can use \Magento\Framework\App\...
CommunityBot
- 1
0
votes
2
answers
3k
views
How to write inner join query in collection method (magento2)?
How to write this query in magento2 collection method?
SELECT o.increment_id , h.status, h.created_at
FROM sales_order AS o
INNER JOIN sales_order_status_history AS h ON h.parent_id = o.entity_id
...
CommunityBot
- 1
0
votes
1
answer
1k
views
process collection after load collection
$categoryid = 43;
$layer = Mage::getModel("catalog/layer");
$category = Mage::getModel("catalog/category")->load($categoryid);
$layer->setCurrentCategory($category);
$attributes = $layer
...
CommunityBot
- 1
2
votes
1
answer
327
views
How to apply filter on magento 2 collection like below query?
THis is my core Mysql query
select * from table_name where CONCAT(',',
subscriptions, ',') like '%,4793,%'
and i want to apply below filter on magento 2 collection.
where CONCAT(',', subscriptions, '...
- 318
2
votes
1
answer
2k
views
How to define custom table and retrieve the data into grid?
i'm writing a module to retrieve the data from a table (i manually created it by using phpmyadmin), but i don't know how to access it in grid.php
<?php
class ...
- 1,115
24
votes
4
answers
11k
views
How to truncate magento table using collection?
I have a custom table. I want to truncate the table using Magento collection without SQL query.
Hope someone will provide some useful information.
- 482
0
votes
0
answers
456
views
How to add another model collection data to an array. [Magento 1.9]
I have response collection $customerData, I have to add referral code of selected customers in the same array,
Model and field of referral code is :
$referCustomer = Mage::getModel('...
- 681
0
votes
1
answer
181
views
Magento 2: How to Show different quantity for specific product type in product grid?
I have a custom table for specific product type to store the product qty, the query to select the qty is like this for example:
SELECT a.qty FROM custom_product_table WHERE product_id = 1;
i noticed ...
- 358
1
vote
3
answers
938
views
How can I use mysql functions when I use collection filters?
How can I use something like this one NOW() - INTERVAL 10 DAY in collection filter?
->addFieldToFilter('period', ['gteq' => 'NOW() - INTERVAL 1 DAY'])
Before running the query, the value in ...
- 903
1
vote
1
answer
1k
views
In magento 2 on product collection both addFieldToFilter and addCategoryFilter is not working
I am trying to get the product based on the 3 rules.
1st rule -> the product should have been created within a month
2nd rule -> it should be belonging to a particular category
3rd rule ->it should ...
- 683
0
votes
1
answer
41
views
How set value for attribute?
I have this code:
foreach ($skuScope as $sku)
{
$object = Mage::getModel('catalog/product');
$product = $object->loadByAttribute('sku', $sku);
$attributes = $product->getAttributes(...
- 778
1
vote
1
answer
280
views
Fatal error: Uncaught Error: Call to undefined method Training\Reviews\Model\ResourceModel\Reviews\Collection::addAttributeToFilter()
I want SELECT COUNT (*) FROM name_table WHERE result LIKE 'likes%' to be executed, and a numeric value is displayed on the page.How do I do it? I'm a beginner. I'll be very grateful for your help!
/...
- 4,054
1
vote
0
answers
1k
views
Magento 2: Delete duplicate products programatically
I need to search Magento 2 DB for duplicate SKUs and remove duplicate products using a script, i found a very useful script here:
Remove duplicated products from catalog.
but it's not compatible with ...
- 1
1
vote
0
answers
38
views
How to add several input fields per entry - admin form in Magento2? [closed]
I want to be able to edit one poll and it's answers in one admin form, and have one input field per answer. I already have my collections ready, how can I create a working admin form ?
POLLS TABLE ...
- 965
1
vote
2
answers
1k
views
Magento 2: How to add sub Query on left join
$collection = $this->rmaLogsFactory->create();
$collection->addFieldToSelect('increment_id');
$collection->getSelect()->joinLeft('rma_log AS arl','main_table.increment_id = arl.order_id ...
- 7,628
0
votes
1
answer
556
views
Magento 1.9: Get Product details for a Customer Order
I am trying to get the product_id of each orders that the customer has ordered.
One simple way is to fetch all the orders, and for each order have a loop to get all items for each order. Which will ...
- 4,968
0
votes
0
answers
303
views
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.attribute_set_id'
I am trying to Filter the products based on vendorid with attribute_set_id.
if i use print_r in the collection i am getting the result like below
$objectManager = \Magento\Framework\App\...
- 341
1
vote
2
answers
52
views
How to output and filter collection using "->where" filter?
I trying to get output from collection using 'where'
filter but no result.
This is my code:
$collection = Mage::getModel('my_work/datanews')
->getCollection()
->...
- 1,405
0
votes
2
answers
4k
views
how to update data in database table
I am sending data to the database by checking duplicate data. But I can't be inserting/updating data in the database.
protected function saveEntityFinish(array $entityData, $table) {
$...
- 458
0
votes
1
answer
63
views
Magento 2 Product collection AddAttributeToFilter with AND/OR and !findinset and is null
How can i add " AND (at_product_userclass.value IS NULL) OR !FIND_IN_SET('1', at_product_userclass.value)" filter condition using addAttributeToFilter in product collection.
Thanks
- 6,450
0
votes
0
answers
128
views
Magento 2 custom module select query doesn't work
I have the following query:
SELECT `main_table`.*, `secondTable`.* FROM `table_one` AS `main_table`
LEFT JOIN `table_two` AS `secondTable`
ON main_table.blog_id = secondTable.blog_id
WHERE (...
- 2,661
6
votes
2
answers
1k
views
Empty Collection :: CollectionFactory Approach and getCollection approach both give empty result
Magento 2.3.1
Php7.2
Ubuntu 18.04
I have created a table in my database using db_schema.xml and then populated that table with sql insert commands directly in mysql workbench.
My model code is
&...
- 503
0
votes
0
answers
1k
views
Magento 2 product collection getSelect Order by Case unException Unknown column 'name' in 'order clause'
I need to use ORDER BY CASE name which is product name
Query:
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`,
`stock_status_index`.`stock_status` AS `is_salable`
FROM `...
- 2,661
0
votes
3
answers
779
views
custom table left join with catalog/product
I have a custom table called flagged_products which lists flagged products like this:
+-----------+------------+---------------------+
| entity_id | product_id | flagged_on |
+-----------+---...
- 1,883
1
vote
0
answers
56
views
how to use walk method to join two tables in magento 2
friends,
how to use the walk() method to join two tables in Magento 2
I have 2 tables and I want to join them based on some conditioning.
my present code looks like this
$result = array();
$...
- 583
1
vote
1
answer
55
views
Unable to display database table values on frontend in magento 2.1.7
I am trying to fetch table on frontend but getting this error
Fatal error: Class 'Crud\Crudatfrontend\Model\ResourceModel\Post' not
found in
D:\xampp\htdocs\newmag\vendor\magento\framework\...
- 77.7k
5
votes
2
answers
4k
views
Join 3 tables in Magento 2
I am new to magento 2, And want to join 4 tables.
TABLE 1) market_p_userdata (columns -> seller_id, image)
TABLE 2) market_product (columns -> seller_id, magepro_id)
TABLE 3) ...
- 532
-1
votes
1
answer
196
views
Magento 1.x addFieldToFilter using AND instead of OR (how can I join search using AND)
I am trying to use AND and OR based on user input.
OR = success
But I an unable to setup AND queries for addFieldToFilter
question: how can I add the second (looped filter) using AND to join
$...
- 1,579
10
votes
6
answers
11k
views
magento 2: How to use the order by and limit clause in sql query
I am using following code to filter result and i want to limit the result.
$giftColletion = $this->_giftFactory->getCollection();
$giftColletion->addFieldToFilter('store_id', 1);
$...
- 3,827
0
votes
1
answer
110
views
RewardspointsFactory class does not exist
Please anyone review my code.
Facing this error.
Exception #0 (ReflectionException): Class Sugarcode\Test\Model\ResourceModel\Test\RewardspointsFactory does not exist
Exception #1 (ReflectionException)...
0
votes
1
answer
485
views
Mysql FULL JOIN in magento 2
How can i execute something like full join in magento 2 then set query data in my collection. Below i try to do somethig.
$joinLeftCustomerSql = $connection
->select()
->from("$...
0
votes
1
answer
2k
views
Magento 2 bind param to query in order by clause
I'm trying to bind params to order by with expressions clause in Magento 2, my insecure code looks like:
$order = new \Zend_Db_Expr("POW((main_table.long - $this->_coordinates['lat']), 2) + POW((...
3
votes
2
answers
2k
views
How can I get collection result "column headers" or "column names" in Magento 1.9x?
How can I get collection result "column headers" or "column names" from query?
Suppose I have $data = Mage::getModel('catalog/product')->getCollection(); and I have some joins with other tables
I ...
- 226
0
votes
1
answer
790
views
Magento 1.9 join table issue
I'm having an issue with Magento 1.9.2.4 trying to make a join on my collection, here's the problem:
Actually I've got a custom table named product_banner which I'm getting by:
$collection = Mage::...
- 8,307
2
votes
2
answers
885
views
Product attribute keeps being "null" in flat table
I'm having a magento installation with flat product tables activated. As I need an attribute in collections I've set it to "Used in Product Listing", the rest is a pretty simple textfield for all ...
- 552
1
vote
2
answers
1k
views
Update product weight in Magento 1.9.x script
I'm writing a script to update product weight programmatically in magento 1.9.x.
The file contains more than ~50000
When I start script the execution time for 100 records is 20 seconds, then the ...
- 11.7k
2
votes
1
answer
1k
views
How do I get my Collection to use a different DB connection?
So I have extended a Magento collection to obtain my own collection only I want to use a different DB connection. What I have attempted to do is define the connection in config.xml like so:
<...
- 13.7k
3
votes
2
answers
4k
views
How to get datas from this table 'directory_currency_rate'
Table contains these columns.
I need to get data from this table 'directory_currency_rate'
which is come under this directory
/vendor/magento/module-directory/Model/Currency.php
/vendor/magento/...
- 1,519
2
votes
3
answers
6k
views
How do you deal with 61 tables join limit when building magento collection?
Mysql has known limitation of 61 tables to join, how do you overcome that when you have more than 61 attributes to join in magento collection?
- 1
2
votes
2
answers
5k
views
How to print sql query for order collection factory in magento 2
I am trying to print sql query. But it is not printing. Please help
public function __construct(
\Magento\Framework\View\Element\Template\Context $context,
\Magento\Customer\Model\...
- 61
2
votes
1
answer
4k
views
Need help with collection joinField() - Magento 2
Can anyone describe how to use joinField() method of collection (Magento\Eav\Model\Entity\Collection\AbstractCollection).
I actually don't know what means each argument:
public function joinField($...
- 2,854
0
votes
1
answer
393
views
AddFieldToFilter not working properly?
I have a code to add a list of products to a table in my database, as you can see here.
In my code below, you can see that it shouldn't add the product if id_product's (CODIGO) unique value is ...
- 11.7k
0
votes
2
answers
37
views
Picking items from the magento database
I am trying to get the client email from the sales_flat_order table but I am not getting this way
public function getOrderbyemail()
{
$email = Mage::getModel('sales/order')->getCollection()
...
- 11.7k
0
votes
1
answer
566
views
Select only one table in a join query
I have this working SQL query :
SELECT main_table.* FROM prd_brand AS main_table INNER JOIN catalog_product_entity_int ON main_table.brand_id=catalog_product_entity_int.value group by brand_id order ...
- 3,230
1
vote
0
answers
843
views
Magento sales_order_grid + join concatenated results from other query
We have some queries / extensions adding columns to the sales_order_grid as a 1:N relation. Normally 1:1 relations work fine. 1:N relation need a group by statement on increment_id and use ...
- 5,452
3
votes
2
answers
2k
views
Call to undefined method Collection::getData()
I use (Manthan_Marketplace) extension in my Magento store
I want to add a table to this extension
so create a custom table (photos) in Magento database with name:(manthan_marketplace_photos)
Table ...
- 4,047
1
vote
1
answer
194
views
How SELECT `e`.* is coming in Query while printing collection filter in magento 1.9
$collection = Mage::getModel("customer/customer")->getCollection()
->addAttributeToSelect('firstname')
->addAttributeToFilter('grisk_college_new_id', array('in' =>...
- 1,323
2
votes
1
answer
9k
views
Magento 2: get table name on collection join
I have a Magento 2 collection and I want to JOIN more tables to this collection's select:
public function prepareProductCollection(\Magento\Catalog\Model\ResourceModel\Product\Collection $collection) ...
- 18.1k
5
votes
2
answers
605
views
convert datatype varchar to in magento collection
i have magento collection
Mage::getResourceModel('customizebundles/customizebundles_collection')
->distinct(true)->addFieldToSelect('validity')
...
CommunityBot
- 1