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.

Rob Wilkerson · Accepted Answer · 2010-10-13 16:55:01Z

2

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.

answered Oct 13, 2010 at 16:55

Rob Wilkerson

41.3k44 gold badges140 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user474710 Over a year ago

Many thanks Rob. I guess I'll have to create the function I want directly in SQL and execute the function from within Cake.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related