0

I am running a join query on two tables in Magento, this is the query :

    $collection = Mage::getModel('sales/flat_shipment_track')->getCollection();
    $collection->getSelect()->join( array('shipment'=>
                 $this->getTable('sales/flat_shipment')), 'main_table.order_id = 
                 shipment.order_id', array('shipment.*'), 'schema_name_if_different');

I was expecting to see some rows from join, but when I do var_dump($collection) I am getting Object of Mage_Sales_Model_Resource_Flat_Shipment_Track_Collection, this is a big object but I don't see actual rows, some starting content is here :

object(Mage_Sales_Model_Resource_Flat_Shipment_Track_Collection)#35 (36) {
  ["_eventPrefix":protected]=>
  string(36) "sales_flat_shipment_track_collection"
  ["_eventObject":protected]=>
  string(30) "flat_shipment_track_collection"
  ["_orderField":protected]=>
  string(8) "order_id"
  ["_salesOrder":protected]=>
  NULL
  ["_model":protected]=>
  string(25) "sales/flat_shipment_track"
  ["_resourceModel":protected]=>
  string(25) "sales/flat_shipment_track"
  ["_resource":protected]=>
  object(Mage_Sales_Model_Resource_Flat_Shipment_Track)#56 (20) {
    ["_eventPrefix":protected]=>
    string(34) "sales_flat_shipment_track_resource"
    ["_grid":protected]=>
    bool(false)
    ["_useIsObjectNew":protected]=>
    bool(true)
    ["_useIncrementId":protected]=>
    bool(false)
    ["_entityTypeForIncrementId":protected]=>
    string(0) ""
    ["_virtualGridColumns":protected]=>
    NULL
    ["_gridColumns":protected]=>
    NULL
    ["_eventObject":protected]=>
    string(8) "resource"
    ["_resources":protected]=>
    object(Mage_Core_Model_Resource)#134 (5) {
      ["_connectionTypes":protected]=>
      array(0) {
      }
      ["_connections":protected]=>
      array(5) {
        ["core_write"]=>
        object(Varien_Db_Adapter_Pdo_Mysql)#104 (30) {
          ["_defaultStmtClass":protected]=>
          string(29) "Varien_Db_Statement_Pdo_Mysql"
          ["_transactionLevel":protected]=>
          int(0)
          ["_connectionFlagsSet":protected]=>
          bool(true)
          ["_ddlCache":protected]=>
          array(0) {
          }
          ["_bindParams":protected]=>
          array(0) {
          }
          ["_bindIncrement":protected]=>
          int(0)
          ["_debug":protected]=>
          bool(false)
          ["_logQueryTime":protected]=>
          float(0.05)
          ["_logAllQueries":protected]=>
          bool(false)
          ["_logCallStack":protected]=>
          bool(false)
          ["_debugFile":protected]=>
          string(23) "var/debug/pdo_mysql.log"
          ["_debugIoAdapter":protected]=>
          NULL
          ["_debugTimer":protected]=>
          int(0)
          ["_cacheAdapter":protected]=>
          object(Varien_Cache_Core)#14 (6) {
            ["_backend":protected]=>
            object(Zend_Cache_Backend_File)#19 (3) {
              ["_options":protected]=>
              array(9) {
                ["cache_dir"]=>
                string(28) "/var/www/magento/var/cache/"
                ["file_locking"]=>
                bool(true)
                ["read_control"]=>
                bool(true)
                ["read_control_type"]=>
                string(5) "crc32"
                ["hashed_directory_level"]=>
                int(1)
                ["hashed_directory_umask"]=>
                int(511)
                ["file_name_prefix"]=>
                string(4) "mage"
                ["cache_file_umask"]=>
                int(384)
                ["metadatas_array_max_size"]=>
                int(100)
              }
              ["_metadatasArray":protected]=>
              array(1) {
                ["176_CORE_CACHE_OPTIONS"]=>

Class Mage_Sales_Model_Resource_Flat_Shipment_Track_Collection is created by me this is the content of this class :

class Mage_Sales_Model_Resource_Flat_Shipment_Track_Collection
    extends Mage_Sales_Model_Resource_Order_Collection_Abstract
{

    protected $_eventPrefix    = 'sales_flat_shipment_track_collection';

    protected $_eventObject    = 'flat_shipment_track_collection';

    protected $_orderField     = 'order_id';

    protected function _construct()
    {
        $this->_init('sales/flat_shipment_track');
    }

    public function setShipmentFilter($shipmentId)
    {
        $this->addFieldToFilter('parent_id', $shipmentId);
        return $this;
    }
}

How can I get the result of joining two table this query should return me 15,000 rows. Thanks

Edit :

This is the query I am trying :

SELECT count(DISTINCT sfs.`order_id`) 
            FROM livedb.`sales_flat_shipment_track` sfst
            JOIN livedb.`sales_flat_shipment` sfs ON sfs.`order_id` = sfst.`order_id`

this returns 15,000 from console.

1

Collections are lazy loaded. What you see is the definition, once you iterate it, the query is executed and rows are fetched. Or you can call ->load on it, if for some reason you want to force it.

  • I tried var_dump($collection->load), this return NULL – Naveen Kumar Feb 4 '15 at 8:10
  • Load is a method. – Melvyn Feb 4 '15 at 8:15
  • getting different error after $collection->load() : PHP Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ''' in /var/www/magento/lib/Zend/Db/Statement/Pdo.php:241 – Naveen Kumar Feb 4 '15 at 8:22
  • fount the issues query generated is wrong : SELECT main_table.*, shipment.order_id` FROM sales_flat_shipment_track AS main_table INNER JOIN sales_flat_shipment.`` AS shipment ON main_table.order_id = shipment.order_id` – Naveen Kumar Feb 4 '15 at 9:56

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.