19 votes
Accepted

Magento 2: join custom table with product collection while sql query is running fine

I finallly found solution. Actually collection making alias for join table. You don't have to set alias $joinConditions = 'e.entity_id = store_price.product_id'; $collection->addAttributeToSelect('...
Abhishek's user avatar
  • 939
14 votes
Accepted

How to join product collection of magento 2 to a custom table?

We can use the join like this: $joinConditions = 'u.sku = e.sku'; $productColelction->getSelect()->join( ['u' => $productColelction->getTable('...
Khoa TruongDinh's user avatar
14 votes

Join the two tables in Magento 2

This works for me. The only change between Magento 1 and Magento 2 is how to get the table name: $collection->getSelect()->joinLeft( ['admin'=>$collection->getTable('admin_user')], '...
Dallas Clarke's user avatar
5 votes
Accepted

Error Filtering grid from joined table collection

you need to create a column like this after your join $select->getSelect()->columns(new \Zend_Db_Expr('CONCAT_WS(" ", main_table.lastname, main_table.firstname) as customer_name')); $this->...
Taschert's user avatar
  • 559
4 votes
Accepted

How to Join an EAV to a Flat Table & Import Flat Table and Update EAV

We can join eav table to flat table. Below is the example for joining the wishlist table to the customer's eav tables: $wishlistCollection = Mage::getModel('wishlist/wishlist')->getCollection(); $...
CedCommerce's user avatar
4 votes
Accepted

Magento 2.2 - custom product sorting issue

In your stock condition just remove alias name csi and try, It's work for me. if ($this->getCurrentOrder() == 'stock') { $this->_collection->getSelect() ->joinLeft( ...
Bhupesh's user avatar
  • 164
3 votes
Accepted

Magento 2: What's best way to convert MySql to Magento

Try this: $items->getSelect() ->join( array('l' => 'customer_entity_varchar'), 'l.entity_id = l.entity_id', array('profile' => 'l.value') ) ->join( array('m' => '...
Stepan Furman's user avatar
3 votes
Accepted

Magento 1.9.x - I want to join first name and last name in customer grid

User renderer $this->addColumn('name', array( 'header' => Mage::helper('customer')->__('Name'), 'index' => 'firstname', 'renderer' => '...
Qaisar Satti's user avatar
  • 32.5k
3 votes

join tables for get payment method of order id

// id you are getting from your custom table on which you have to load order_id. $id = '1'; //Note we are considering order_id of custom table as increment id try { $collection = Mage::getModel(...
InfoBeans's user avatar
  • 332
3 votes
Accepted

Magento2 use Multiple LIKE operator in Join Query

Use IN instead LIKE. For example: $collection->getSelect()->where('paproduct.product_id IN (?)', $productId); $productId should be array. Like: $productId = [1,2];
Sohel Rana's user avatar
  • 35.7k
3 votes
Accepted

join() is not working

<?php namespace Krish\CustomAdmin\grid; class Collection extends \Magento\Sales\Model\ResourceModel\Order\Grid\Collection { protected function _initSelect() { parent::_initSelect()...
26vivek's user avatar
  • 457
3 votes
Accepted

Use of sub query within collection in Magento

$collection->getSelect()->where('e.entity_id NOT IN(?)', new \Zend_Db_Expr('(SELECT b.parent_id FROM cataloginventory_stock_item AS a JOIN catalog_product_relation AS b ON a.product_id = b....
Shailesh Katarmal's user avatar
2 votes

Joining two collection / tables

$this->addColumn('increment_id', array( 'header' => Mage::helper('creditlog')->__('Your orders'), 'index' => 'increment_id', 'renderer' => '...
Qaisar Satti's user avatar
  • 32.5k
2 votes
Accepted

joinLeft escape string from ON condition

I found a way. $this->getSelect()->joinLeft( array('notcountries' => $countriesTable), $this->getConnection()->quoteInto("main_table.page_id = notcountries....
JohnyFree's user avatar
  • 1,539
2 votes

Magento-2 : How to set admin form data by join tables

I have completed by add below function to Model Resource protected function _getLoadSelect($field, $value, $object) { $select = parent::_getLoadSelect($field, $value, $object); $select->...
Suresh Chikani's user avatar
2 votes

How to Get Distinct Entries from Joined Tables Through Join()

Distinct and Group, as described by David and Paras, will work on 9 out of 10 queries. If it doesn't, make sure that you aren't reusing the column name of the primary column of the collection. The ...
james.c.funk's user avatar
2 votes

Magento 2 : Join 2 tables in Existing collection

Try to do with the addition of joins , example : (substitute your columns) $items->getSelect()->join( ['table1join'=>$collection->getTable('customer_entity_text')], ...
Eugene Kapelko's user avatar
2 votes
Accepted

Magento 1.9 join table issue

Please try with below code as I haven't run a query on MySQL because I have no any custom module table include with category id field but I build it based on your collection $collection = Mage::...
Sanjay Patolia's user avatar
2 votes
Accepted

Magento 2.3.4 Custom uiComponent admin form joint table Add New gives ambiguous query

Add map inside construct method. Ex: $this->_map['fields']['employee_id'] = 'main_table. employee_id'; Check for more detail
Sohel Rana's user avatar
  • 35.7k
1 vote
Accepted

Magento 2 Join table customer entity and customer address entity

The error is because the main_table does not exist. If you debug, you can see in vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php the line $this->getConnection()->...
raumatbel's user avatar
  • 1,225
1 vote
Accepted

Getting issues while export Attribute Description in Magento 2

Solution: You need to specify the tableName since multiple tables contains the same column name (store_id). Add your table name like table.store_id.
Nikunj Vadariya's user avatar
1 vote

Magento 2.2 - Load eav attribute data for every product on collection load

You can do it by following these steps, in your module you need to add catalog_attribute in etc/catalog_attributes.xml by this attribute will be loaded by default. <?xml version="1.0"?> <...
xMageStore's user avatar
1 vote

How to JOIN TABLES with WHERE clause in Magento 1.9?

use this way: $customer=Mage::getModel('customer/customer')->getCollection(); $customer->getSelect()->join(array('cev' => Mage::getSingleton('core/resource')->getTableName('...
Aman Agarwal's user avatar
1 vote
Accepted

Update database query Magento 1.9

This should work; SELECT `e`.`entity_id` AS `product_id`, `e`.`sku`, `catalog_name`.`value` AS `product_name`, `catalog_price`.`value` AS `product_price`, `quote_table`.`entity_id` AS `cart_id`, `...
Ricky Odin Matthews's user avatar
1 vote
Accepted

Join Query in magento 2

Check below query $this->getSelect()->join( ['details' => $this->getReviewDetailTable()], 'main_table.review_id = details.review_id', ['detail_id', 'title', 'detail', '...
Suresh Chikani's user avatar
1 vote

Left join order grid collection using observer method. Magento 1.9

I solved my problem by making following changes: public function prepareOrderGridCollection(Varien_Event_Observer $observer) { $collection = $observer->getEvent()->getOrderGridCollection(); ...
P S's user avatar
  • 1,983
1 vote

Magento 2: Join EAV attribute from product attribute set on a custom flat table

I've managed to get a working solution where you can use the attribute set model ( or the full EAV ) simply by inversing the query, ie. instead of joining on the flat table, i'm in turn joining the ...
Merijn's user avatar
  • 152
1 vote

Joining Products Table with Custom Module Table (Adding Product Thumbnails to Custom Grid)

To this to work, your collection must be catalog type. First add this in your _prepareCollection() //$collection = your collection $store = $this->_getStore(); $collection->joinAttribute('...
Adarsh Khatri's user avatar
1 vote

Magento 2: join custom table with product collection while sql query is running fine

Magento will be much more friendly if you stop using unnecessary rewrites and use Observers more often. For your problem you need to add: Vendor/Module/etc/frontend/events.xml: <config xmlns:xsi='...
dudzio's user avatar
  • 611
1 vote

How to LEFT-JOIN 'catalog/category' fields to 'sales/order_item' collection?

I know that it might lead to duplicated rows if the product has more than 1 category, that's exactly what I need. Then it is not possible. Internally, collections use an array for the loaded items ...
Fabian Schmengler's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible