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();

score 13 · Accepted Answer · answered Jan 13 '11 at 12:57

13

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

echo $select;

answered Jan 13 '11 at 12:57

Max Pronko

1,369
11
13

13

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. – Alana Storm Apr 27 '11 at 22:08
It would not contain any `limits` or `filters` applied to the query. Any idea to get the full query including `limits` and `filters`? – Basheer Kharoti Oct 01 '21 at 10:02

score 6 · Answer 2 · answered Jan 13 '11 at 12:55

6

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()

answered Jan 13 '11 at 12:55

woot586

3,906
10
32
40

2

echo is just enough as it has it's own toString method – Anton S Jan 13 '11 at 13:02
1

and sometimes you need to cast it to trigger the `__toString` method automatically. Something like `echo (string)$this->getSelect...` – clockworkgeek Jan 13 '11 at 13:24

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

2 Answers2

Linked