5

I want to dig deeper into Magentos SQL adapter. There are a lot of answer that often suggests to use Magentos own implementation of the SQL-Adapter.

I currently want to do this SQL in a resource Model:

SELECT pd.sku 
FROM wishlist_item AS wl 
LEFT JOIN catalog_product_entity AS pd
ON pd.entity_id = wl.product_id
WHERE wl.wishlist_id = 2;

How would I transform that into Magento Code if I shall not use ->query($query)?

  • are you fine with using Mage::getModel('core/resource')->getConnection('core_read');? – Jurģis Toms Liepiņš Nov 21 '17 at 12:48
  • @JurģisTomsLiepiņš Sure – Max Nov 22 '17 at 7:48
4

Its always a good idea to use query generators instead of RAW queries (security issues)

$connection        = Mage::getModel('core/resource')->getConnection('core_read');
$catalogTable      = Mage::getSingleton('core/resource')->getTableName('catalog/product');
$wishlistItemTable = Mage::getSingleton('core/resource')->getTableName('whistlist/item');

$sql = $connection->select()->from(
     ["wi" => $wishlistItemTable], 
     []
)->join(
    ["cpe" => $catalogTable],
    'cpe.entity_id = wi.product_id',
    ['sku']
)->where(
    'wi.wishlist_id = ?', 2
);

$connection->fetchCol($sql);
  1. Use getTableName() method to get proper table names (for cases where database table prefixing is enabled)
  2. Using fetchCol() as there is only one column required in the query result. There are quite a few like fetchPairs(),fetchRow(), fetchOne() etc depends on the query result type, should not always fetchAll() unless necessary
  • That sounds very good to me. Thank you for your answer. – Max Nov 22 '17 at 9:02
  • This answer returns Array ( [0] => 1 [1] => 2 ) instead of the SKUS. The SQL still returns the correct result. – Max Nov 22 '17 at 15:58
  • the fetchCol should only be used when fetching a single column from the table, you might have multiple columns – Atish Goswami Nov 24 '17 at 8:20
4

Try Below code that will work for you

$collection = Mage::getModel('whistlist/item')->getCollection()
            ->addFieldToSelect('*')
            ->addFieldToFilter('wishlist_id', 2);

$collection->getSelect()
     ->joinLeft(
          array('product'=> Mage::getSingleton('core/resource')->getTableName('catalog/product')),
          'product.entity_id = main_table.product_id',
           array('pid'=>'product.product_id'));
  • For further readability you should try to format your code :) – Max Nov 22 '17 at 9:02
  • @Max Is it readable now? and also feel free to edit if you are not changing content – Murtuza Zabuawala Nov 22 '17 at 9:08
  • 1
    @Max you are always welcome – Murtuza Zabuawala Nov 22 '17 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.