We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

pdo\mysql sphinxql syntax compatibility

Hi,

My model to sphinx does not work due to Pdo/Mysql prepending the table name before each column in the SELECT statement. For example this query:

select id from index;

Would become:

select `index`.`id` from `index`;

And sphinx search engine is not compatible with this syntax:

error=sphinxql: syntax error, unexpected '.', expecting FROM or ',' near '.`id` AS `id` FROM `index`'

It appears that \Phalcon\Db\Adapter\Pdo\Mysql does not apply PDO::ATTR_FETCH_TABLE_NAMES when set like this:

return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
       "host"     => "192.168.2.2",
       "port" => "9306",
        "options" => [\PDO::ATTR_FETCH_TABLE_NAMES => 0] // tried false here too, doesn't seem to be taken in account at all
));

How can I solve this?

Regards, Boyko

Are you using PHQL or the SQL component directly?



857
edited Aug '15

Are you using PHQL or the SQL component directly?

I have a model for sphinx and using the query builder like this:

DI:

        $di->set('sphinx', function () {
            return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
                   "host"     => "192.168.2.2",
                   "port" => "9306",
                   "options" => [\PDO::ATTR_FETCH_TABLE_NAMES => 0] // tried false here too, doesn't seem to be taken in account at all
            ));
        });

Model:

class test_index extends \Phalcon\Mvc\Model
{
    /**
     * Initialize method for model.
     */
    public function initialize()
    {
        $this->setConnectionService('sphinx');
    }

Controller:

                $builder = $this->modelsManager->createBuilder()
                    ->columns('id as pid')
                    ->from('Admin\Models\test_index');

Yes, PHQL will transform the SQL to avoid errors related to ambiguous columns and reserved words, that's why it prepends the index



857

Can this feature be disabled as with PDO::ATTR_FETCH_TABLE_NAMES?



977

do you resolve this issue?

Can this feature be disabled as with PDO::ATTR_FETCH_TABLE_NAMES?



977

my solution is add custom query function for sphinx