How do you display a Magento sql query as a string?

Question

Magento constructs its SQL queries like

 $this->getSelect()->joinInner(
        array('sbao' => $this->getTable('sales/billing_agreement_order')),
        'main_table.entity_id = sbao.order_id',
        array()
    )

Is there a way to display the resulting query in a string format rather than printing out the huge object e.g.

echo $this->getSelect()->joinInner(
        array('sbao' => $this->getTable('sales/billing_agreement_order')),
        'main_table.entity_id = sbao.order_id',
        array()
    )->toString();

Pontiff

Answer 1

$select = $this->getSelect()->joinInner(
        array('sbao' => $this->getTable('sales/billing_agreement_order')),
        'main_table.entity_id = sbao.order_id',
        array()
    );

echo $select;

Mcnully

For the programmery folks, what's happening here is the select object is being cast as a string when used with echo. Using (string) $select or $seletct->__toString() would produce the same results. – Rogerrogerio

It would not contain any limits or filters applied to the query. Any idea to get the full query including limits and filters? – Botts

Answer 2

I nearly had it for those interested you need to use ->__toString() e.g.

echo $this->getSelect()->joinInner(
    array('sbao' => $this->getTable('sales/billing_agreement_order')),
    'main_table.entity_id = sbao.order_id',
    array()
)->__toString()

Pontiff

echo is just enough as it has it's own toString method – Orten

and sometimes you need to cast it to trigger the __toString method automatically. Something like echo (string)$this->getSelect... – Magistral