Using raw SQL inside Criteria

You might just be better off using PHQL.

Wow, I can't believe I got any sort of reply on this forum :)))))

You might just be better off using PHQL.

This forum isn't used as much as it could be. Generally the community is pretty active when it comes to answering questions.

@quasipickle has v4 improved on supported SQL keywords?

In v3, i had to register custom functions on the db dialect to support things like INTERVAL:

use Phalcon\Db\Dialect\Mysql;

class MyDialect extends Mysql
{
    /**
     * Transforms an intermediate representation for a expression into a database system valid expression
     *
     * @param array  $expression
     * @param string $escapeChar
     * @param mixed $bindCounts
     *
     * @return string
     * @throws \Exception
     */
    public function getSqlExpression(array $expression, $escapeChar = null, $bindCounts = NULL)
    {
        if ($expression["type"] == 'functionCall') {
            switch ($expression["name"]) {
                case 'SIMILARITY':
                case 'JARO_WINKLER_SIMILARITY':
                    if (count($expression["arguments"]) != 2) {
                        throw new \Exception('JARO_WINKLER_SIMILARITY requires 2 parameters');
                    }
                    return 'JARO_WINKLER_SIMILARITY('.$this->getSqlExpression($expression["arguments"][0]).', '.$this->getSqlExpression($expression["arguments"][1]).')';
                    break;

                case 'DATE_INTERVAL':
                    if (count($expression["arguments"]) != 2) {
                        throw new \Exception('DATE_INTERVAL requires 2 parameters');
                    }
                    $unit = trim($expression["arguments"][1]['value'],"'");
                    switch($unit) {
                        case 'YEAR':
                        case 'MONTH':
                        case 'DAY':
                        case 'HOUR':
                        case 'MINUTE':
                        case 'SECOND':
                            return 'INTERVAL ' . $this->getSqlExpression($expression["arguments"][0]) . ' '.$unit;
                            break;
                    }
                    break;

                case 'DATE_DIFF':
                    if (count($expression["arguments"]) != 3) {
                        throw new \Exception('DATE_DIFF requires 3 parameters');
                    }
                    $unit = trim($expression["arguments"][0]['value'],"'");
                    switch($unit) {
                        case 'YEAR':
                        case 'MONTH':
                        case 'DAY':
                        case 'HOUR':
                        case 'MINUTE':
                        case 'SECOND':
                            return 'TIMESTAMPDIFF('.$unit.', '.$this->getSqlExpression($expression["arguments"][1]).', '.$this->getSqlExpression($expression["arguments"][2]).')';
                            break;
                    }
                    break;

                case 'TIME':
                    return "TIME(" . $this->getSqlExpression($expression["arguments"][0]) . ")";
                    break;

                case 'AGE':
                    return "DATE_FORMAT(FROM_DAYS(DATEDIFF(CURRENT_DATE()," . $this->getSqlExpression($expression["arguments"][0]) . ")), '%Y')";
                    break;

                case 'FULLTEXT_MATCH':
                    if (count($expression["arguments"]) < 2) {
                        throw new \Exception('FULLTEXT_MATCH requires 2 parameters');
                    }

                    $arguments = array();
                    $length = count($expression["arguments"]) - 1;
                    for ($i = 0; $i < $length; $i++) {
                        $arguments[] = $this->getSqlExpression($expression["arguments"][$i]);
                    }

                    return 'MATCH(' . join(', ', $arguments) . ') AGAINST (' .
                        $this->getSqlExpression($expression["arguments"][$length]) . ')';

                case 'FULLTEXT_MATCH_BMODE':
                    if (count($expression["arguments"]) < 2) {
                        throw new \Exception('FULLTEXT_MATCH requires 2 parameters');
                    }

                    $arguments = array();
                    $length = count($expression["arguments"]) - 1;
                    for ($i = 0; $i < $length; $i++) {
                        $arguments[] = $this->getSqlExpression($expression["arguments"][$i]);
                    }

                    return 'MATCH(' . join(', ', $arguments) . ') AGAINST (' .
                        $this->getSqlExpression($expression["arguments"][$length]) . ' IN BOOLEAN MODE)';
            }
        }

        return parent::getSqlExpression($expression, $escapeChar);
    }
}

// services.php

$di->setShared('db', function() {
    $db = new Driver();
    // etc...
    $db->setDialect(new MyDialect);
    return $db;
});

I've yet to migrate a project to v4, so i wouldnt know about that specifically...

If I only knew where this "community" is. It's a shame since I actually enjoy Phalcon and it's rapidly improving. But the documentation really leaves a lot to be desired for

This forum isn't used as much as it could be. Generally the community is pretty active when it comes to answering questions.

The documentation != forum. I too think the documentation isn't great. They keep writing new documentation, but it's structured like it always has, which doesn't result in much improvement. It's written like a story - to take people through all the functionality. It needs to be written like... documentation - listing and describing each piece of functionality separately.

If I only knew where this "community" is. It's a shame since I actually enjoy Phalcon and it's rapidly improving. But the documentation really leaves a lot to be desired for

@quasipickle has v4 improved on supported SQL keywords?

I've yet to migrate a project to v4, so i wouldnt know about that specifically...

I don't know - I've not migrated anything either. They're pretty sticky about supporting everything PDO supports for all DBMSs. So anything MySQL specific is probably still not supported by PHQL. One can always drop down even further into raw SQL to get what one needs.

I'm converting a few sites to 4.0. I haven't historically done much with raw SQL until this one project where I really need to use BINARY on a column becuase case is important. But the Phalcon models don't seem to support BINARY. A call like $model::findFirst("binary file_name='thisFile.txt'") fails. But the raw SQL "select * from tablename where BINARY file_name='thisFile.txt';" works.

So I had to resort to raw SQL to use the BINARY notation.

Or rather, I really should say, I haven't found how to make it work in the "wonderful" documentation provided! :D

Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API DGCustomerFirst.com

@cynthiiac can you please be more detailed about it

@andrewpiana that was a spammer. I've deleted their post.