Apply native functions for DataBases using models

Question

Apply native functions for DataBases using models

Sebastian Sep '13

Created Sep '13	Last Reply Sep '13	Replies 1	Views 1144	Votes 1

Sebastian 5.8k

Sep '13

Hello everyone.

I would like to apply functions for the columns in the SQL query, but using the \Phalcon\MVC\Model class.

I know I have the method for columns. This is what I'm trying to do:

$this->query() ->columns(array('distinct id AS id', " TRIM(BOTH '.' FROM (CONCAT_WS('.', IF(host = '@', '', host), zone))) AS full_domain")) ->where("data = 'x.x.x.x'") ->order('full_domain') ->execute();

Those are just native functions for MySQL, but I cannot use it because is complaining when I try to excecute. So my solution was just use the Pdo adapter, and do a full query. But I'd like to be able to use more the OO approuch to do this.

The error that I'm getting is:

Syntax error, unexpected token STRING(.), near to ' FROM (CONCAT_WS('.', IF(host = '@', '', host), zone))) AS full_domain FROM [Domains] WHERE data = 'x.x.x.x' ORDER BY full_domain', when parsing: SELECT distinct id AS id, TRIM(BOTH '.' FROM (CONCAT_WS('.', IF(host = '@', '', host), zone))) AS full_domain FROM [Domains] WHERE data = 'x.x.x.x' ORDER BY full_domain (181)

You can see that the query is being built very well, but is complaining because of the dot ('.')

Any ideas how to improve what I'm doing? The thing is I'd like to use the Tag::select helper with the object from the Model and not to have to do a query, and create manually the array that I'm sending now to the Tag::selectStatic helper.

Thanks!

1

Gigih Aji Ibrahim · Answer 1 · 2013-09-16T07:03:04-07:00

When i need to use some mysql feature I usually create a static function inside the model. Here is snippet of some my code:

class SomeModel extend ModelBase {

    public static function findWithSubscription($productId){
        $sql = "
        select 
            ps.*,
            s.name as name,
            s.maximum_pool as maximum_pool,
            DATEDIFF(ps.end_date, now()) as remaining_day
        from product_subscription ps
        left join subscription s on s.id = ps.subscription_id
        where product_id=:productId
       ";

        $params = [
            'productId' => $productId
        ];

        $records = new static();
        return new \Phalcon\Mvc\Model\Resultset\Simple(null, $records, $records->getReadConnection()->query($sql, $params));

    }
}

Sebastian · Answer 2 · 2013-09-16T17:19:53-07:00

Sebastian
5.8k

Sep '13

Oh that's very helpful. Thanks for that!