Print SQL query of ORM query builder in cakephp3

Question

How to print ORM query

$query = $articles->find('all')->contain(['Comments']);

For example print =>

SELECT * FROM comments WHERE article_id IN (comments);

score 11 · Accepted Answer · answered Mar 18 '16 at 09:59

11

Wrapping your ORM query result with the debug function will show the SQL and bound params:

debug($query);

You can also similarly look at the query results with the debug function.See CakePHP 3: retrieving data and result sets — Debugging Queries and ResultSets

answered Mar 18 '16 at 09:59

Yosyp Schwab

161
1
4

Do you know how to print only sql part of it? – Fury Mar 18 '16 at 22:24
Did you try string casting it? `(string)$query`. – mark Mar 19 '16 at 10:07

score 10 · Answer 2 · answered Oct 14 '17 at 06:57

what about $query->sql()?

$qb = $this->Person->find()->select(["id", "text" => "concat(Name,' ',Family)"])
            ->where(['id >' => 0])
            ->where($query ? ["OR" => $filters] : null)
            ->limit(10);
dd($qb->sql());

and result:

.../src/Controller/ClientController.php (line 86)
'SELECT Person.id AS `Person__id`, concat(Name,' ',Family) AS `text` FROM person Person WHERE (id > :c0 AND (Family like '%sam%' OR Name like '%sam%' OR Family like '%sam%' OR Name like '%sam%')) LIMIT 10'

The official DebugKit plugin has the [helper functions](https://book.cakephp.org/3.0/en/debug-kit.html#helper-functions) that you need. — Ilie Pandia, Apr 24 '18 at 07:20

Fury · Answer 3 · 2016-03-19T08:47:55.330

3

I prefer this:

public function __debugInfo()
    {
        return [
            'query' => $this->_query,
            'items' => $this->toArray(),
        ];
    }

// Print the query
debug($query->__debugInfo()['sql']);

// Prints this
SELECT * FROM comments WHERE article_id IN (comments);

edited Mar 19 '16 at 08:47

answered Mar 19 '16 at 01:25

Fury

4,643
5
50
80

Print SQL query of ORM query builder in cakephp3

3 Answers3