Apply native functions for DataBases using models

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 (CONCATWS('.', IF(host = '@', '', host), zone))) AS fulldomain")) ->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 (CONCATWS('.', IF(host = '@', '', host), zone))) AS fulldomain FROM [Domains] WHERE data = 'x.x.x.x' ORDER BY fulldomain', when parsing: SELECT distinct id AS id, TRIM(BOTH '.' FROM (CONCATWS('.', IF(host = '@', '', host), zone))) AS fulldomain FROM [Domains] WHERE data = 'x.x.x.x' ORDER BY fulldomain (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!

When i need to use some mysql feature I usually create a static function inside the model. Here is snippet of some my code: ```php 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));

}

} ```



3.5k

Oh that's very helpful. Thanks for that!