Databases
StackOverflow2013
Postsdbo
POJoining multiple tables based on a custom attribute
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoining multiple tables based on a custom attribute
    primarykey
    data
    text
    <p>I'm trying to join two tables inside Mage_Sales_Model_Resource_Report_Order_Createdat,</p> <pre><code>$columns = array( // convert dates from UTC to current admin timezone 'period' =&gt; $periodExpr, 'store_id' =&gt; 'o.store_id', 'order_status' =&gt; 'o.status', 'orders_count' =&gt; new Zend_Db_Expr('COUNT(o.entity_id)'), 'total_qty_ordered' =&gt; new Zend_Db_Expr('SUM(oi.total_qty_ordered)'), 'total_qty_invoiced' =&gt; new Zend_Db_Expr('SUM(oi.total_qty_invoiced)'), 'total_income_amount' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_grand_total', 0), $adapter-&gt;getIfNullSql('o.base_total_canceled',0), $adapter-&gt;getIfNullSql('o.base_to_global_rate',0) ) ), 'total_revenue_amount' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s - %s - (%s - %s - %s)) * %s)', $adapter-&gt;getIfNullSql('o.base_total_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_tax_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_shipping_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_total_refunded', 0), $adapter-&gt;getIfNullSql('o.base_tax_refunded', 0), $adapter-&gt;getIfNullSql('o.base_shipping_refunded', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_profit_amount' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s - %s - %s - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_total_paid', 0), $adapter-&gt;getIfNullSql('o.base_total_refunded', 0), $adapter-&gt;getIfNullSql('o.base_tax_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_shipping_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_total_invoiced_cost', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_invoiced_amount' =&gt; new Zend_Db_Expr( sprintf('SUM(%s * %s)', $adapter-&gt;getIfNullSql('o.base_total_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_canceled_amount' =&gt; new Zend_Db_Expr( sprintf('SUM(%s * %s)', $adapter-&gt;getIfNullSql('o.base_total_canceled', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_paid_amount' =&gt; new Zend_Db_Expr( sprintf('SUM(%s * %s)', $adapter-&gt;getIfNullSql('o.base_total_paid', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_refunded_amount' =&gt; new Zend_Db_Expr( sprintf('SUM(%s * %s)', $adapter-&gt;getIfNullSql('o.base_total_refunded', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_tax_amount' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_tax_amount', 0), $adapter-&gt;getIfNullSql('o.base_tax_canceled', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_tax_amount_actual' =&gt; new Zend_Db_Expr( sprintf('SUM((%s -%s) * %s)', $adapter-&gt;getIfNullSql('o.base_tax_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_tax_refunded', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_shipping_amount' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_shipping_amount', 0), $adapter-&gt;getIfNullSql('o.base_shipping_canceled', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_shipping_amount_actual' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_shipping_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_shipping_refunded', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_discount_amount' =&gt; new Zend_Db_Expr( sprintf('SUM((ABS(%s) - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_discount_amount', 0), $adapter-&gt;getIfNullSql('o.base_discount_canceled', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ), 'total_discount_amount_actual' =&gt; new Zend_Db_Expr( sprintf('SUM((%s - %s) * %s)', $adapter-&gt;getIfNullSql('o.base_discount_invoiced', 0), $adapter-&gt;getIfNullSql('o.base_discount_refunded', 0), $adapter-&gt;getIfNullSql('o.base_to_global_rate', 0) ) ) ); $select = $adapter-&gt;select(); $selectOrderItem = $adapter-&gt;select(); $qtyCanceledExpr = $adapter-&gt;getIfNullSql('qty_canceled', 0); $cols = array( 'order_id' =&gt; 'order_id', 'total_qty_ordered' =&gt; new Zend_Db_expr("SUM(qty_ordered - {$qtyCanceledExpr})"), 'total_qty_invoiced' =&gt; new Zend_Db_expr('SUM(qty_invoiced)'), ); $selectOrderItem-&gt;from($this-&gt;getTable('sales/order_item'), $cols) -&gt;where('parent_item_id IS NULL') -&gt;group('order_id'); $select-&gt;from(array('o' =&gt; $this-&gt;getTable('sales/order')), $columns) -&gt;join(array('oi' =&gt; $selectOrderItem), 'oi.order_id = o.entity_id', array()) -&gt;where('o.state NOT IN (?)', array( Mage_Sales_Model_Order::STATE_PENDING_PAYMENT, Mage_Sales_Model_Order::STATE_NEW )); </code></pre> <p>Can some please explain to me exactly what is happening inside:</p> <pre><code>$select-&gt;from(array('o' =&gt; $this-&gt;getTable('sales/order')), $columns) -&gt;join(array('oi' =&gt; $selectOrderItem), 'oi.order_id = o.entity_id', array()) -&gt;where('o.state NOT IN (?)', array( Mage_Sales_Model_Order::STATE_PENDING_PAYMENT, Mage_Sales_Model_Order::STATE_NEW )); </code></pre> <p>From what I understand sales/order_item table is being joined with sales/order... (can someone please explain this syntax?) I'm trying to understand how to correctly join tables because I will need to join sales/order_grid with sales/order as well.</p> <p>Thank you!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USOleg Snegov
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POJoining multiple tables based on a custom attribute
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor understanding joins, http://www.sitepoint.com/understanding-sql-joins-mysql-database/ is a good start. Then you might want to see what is the SQL query behind `$select`, see if `var_dump($select)` prints the query.
      singulars
      1. POJoining multiple tables based on a custom attribute
      1. UStmuguet
    2. COsee this question to learn how to join in magento: http://stackoverflow.com/questions/8337363/magento-join-table-collection
      singulars
      1. POJoining multiple tables based on a custom attribute
      1. USpzirkind
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload