Welcome to The Cookbook

query(string $query)

Custom SQL calls can be made using the model's query() method.

If you’re ever using custom SQL queries in your application, be sure to check out CakePHP’s Sanitize library, which aids in cleaning up user-provided data from injection and cross-site scripting attacks.

query() uses the table name in the query as the array key for the returned data, rather than the model name. For example,

$this->Picture->query("SELECT * FROM pictures LIMIT 2;");

1. $this->Picture->query("SELECT * FROM pictures LIMIT 2;");

might return

Array
(
    [0] => Array
        (
            [pictures] => Array
                (
                    [id] => 1304
                    [user_id] => 759
                )
        )

    [1] => Array
        (
            [pictures] => Array
                (
                    [id] => 1305
                    [user_id] => 759
                )
        )
)

1. Array
2. (
3. [0] => Array
4. (
5. [pictures] => Array
6. (
7. [id] => 1304
8. [user_id] => 759
9. )
10. )
11. [1] => Array
12. (
13. [pictures] => Array
14. (
15. [id] => 1305
16. [user_id] => 759
17. )
18. )
19. )

To use the model name as the array key, and get a result consistent with that returned by the Find methods, the query can be rewritten:

$this->Picture->query("SELECT * FROM pictures AS Picture LIMIT 2;");

1. $this->Picture->query("SELECT * FROM pictures AS Picture LIMIT 2;");

which returns

Array
(
    [0] => Array
        (
            [Picture] => Array
                (
                    [id] => 1304
                    [user_id] => 759
                )
        )

    [1] => Array
        (
            [Picture] => Array
                (
                    [id] => 1305
                    [user_id] => 759
                )
        )
)

1. Array
2. (
3. [0] => Array
4. (
5. [Picture] => Array
6. (
7. [id] => 1304
8. [user_id] => 759
9. )
10. )
11. [1] => Array
12. (
13. [Picture] => Array
14. (
15. [id] => 1305
16. [user_id] => 759
17. )
18. )
19. )