pdo\mysql sphinxql syntax compatibility

Question

pdo\mysql sphinxql syntax compatibility

bobcho Aug '15

Created Aug '15	Last Reply Dec '16	Replies 4	Views 754	Votes 0

bobcho 857

Aug '15

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

Andres Gutierrez · Answer 1 · 2015-08-31T08:10:59-07:00

Andres Gutierrez
34.6k

Aug '15

Are you using PHQL or the SQL component directly?

bobcho · Answer 2 · 2015-08-31T09:18:05-07:00

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');