How do I create user-defined sql functions using CakePHP?

Question

Consider the example given on this page about creating user-defined functions in SQL and then using the user-defined function in the WHERE clause.

mysql> CREATE FUNCTION myFunction(in_rep_id INT)
    ->      RETURNS INT
    ->      READS SQL DATA
    -> BEGIN
    ->      DECLARE customer_count INT;
    ->
    ->      SELECT COUNT(*)
    ->           INTO customer_count
    ->        FROM employee
    ->       WHERE id=in_rep_id;
    ->
    ->      RETURN(customer_count);
    ->
    -> END$$

mysql> SELECT id,myFunction(id)
    ->   FROM employee
    ->  WHERE myFunction(id)>0
    ->  ORDER BY myFunction(id) desc;

How do I replicate this using CakePHP? Do I need to define the function as part of a CakePHP Model? Or is there a way to execute the CREATE FUNCTION syntax but from within CakePHP?

Any help would be appreciated.

Answer 1

The CREATE FUNCTION syntax is really DDL (data definition language) and, like other DDL syntax (e.g. CREATE TABLE), isn't supported by Cake. You can execute a function via $this->Model->query( 'your sql here' ), I suspect, though I've never needed to try it.

I should add that you can really run any arbitrary SQL via the Model::query() method, but I made an assumption that this is something you want in place in the same way you want a table in place before it's ever actually needed.