Zend\Db\Adapter\Driver problem

[lonejack]

When I do a query on my database I obtain an error depending on what driver is configurated. I tryed to understand how the query is resolved and performed a test with two adapters: Mysqli and Pdo_Mysql. On both cases I obtain the same query:

SELECT a.*, CONCAT(b.first_name,' ',b.family_name) AS author FROM domain AS a LEFT JOIN users AS b ON b.id=a.owner_id WHERE a.owner_id= '15' LIMIT '10' OFFSET '0'

Problem: with driver "MySqli" I get an exception. I don't know if it is important but applying the above query on phpmyadmin and I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10' OFFSET '0'' at line 1

With Pdo_Mysql adapter the same statement result correct.

Code applied:

on global configuration:

'db' => [
    'driver' => 'Pdo_Mysql',  // MySqli
    'database' => 'mydb',
    'port' => '3306',
    'host' => 'localhost',
    ...
]

on a model:

use Zend\Db\Sql\Select;
public function method() {

    $select = new Select();

    $select->from(['a' => $this->table])
            ->join(['b'=>USERS_TABLE],'b.id = a.owner_id ', ['author' => new Expression("CONCAT(`b`.`first_name`,' ',`b`.`family_name`)")],Select::JOIN_LEFT)
            ->where(['a.owner_id' => $this->owner]);

    $adapter = $this->getAdapter();
    $pf =$adapter->getPlatform();        
    $str = $select->getSqlString($pf);
    $result = $adapter->query($str);
    ...
}

[alextech]

According to https://github.com/zendframework/zendframework/issues/3224 should be

use Zend\Db\Sql\Select;
public function method() {

    $select = new Select();

    $select->from(['a' => $this->table])
        ->join(['b'=>USERS_TABLE],'b.id = a.owner_id ', ['author' => new Expression("CONCAT(`b`.`first_name`,' ',`b`.`family_name`)")],Select::JOIN_LEFT)
        ->where(['a.owner_id' => $this->owner]);

    $sql = new Sql($this->getAdapter());
    $str = $sql->buildSqlString($select);
    $result = $adapter->query($str);
}

IMHO this is confusing, if Select object is aware of adapter, why would it not be capable of using it. But such is the current design of query objects trying to both be value objects and builders but not always getting it right unless explicitly passed through Sql object. @turrsis is this something that your refactor fixes? I vaguely saw you move SQL generation to dedicated builder. Will that fix this problem of doing seamingly same thing in two ways but one being technically wrong? If so, this can be closed and maybe marked as fixed with your PR.

[lonejack]

Now (with Sasha's modification) is working correctly. Thank you

2016-10-24 5:21 GMT+02:00 Sasha Alex Romanenko [email protected]:

According to zendframework/zendframework#3224 https://github.com/zendframework/zendframework/issues/3224 should be

use Zend\Db\Sql\Select; public function method() {

$select = new Select();

$select->from(['a' => $this->table])
    ->join(['b'=>USERS_TABLE],'b.id = a.owner_id ', ['author' => new Expression("CONCAT(`b`.`first_name`,' ',`b`.`family_name`)")],Select::JOIN_LEFT)
    ->where(['a.owner_id' => $this->owner]);

$sql = new Sql($this->getAdapter());
$str = $sql->getSqlStringForSqlObject($select);
$result = $adapter->query($str);

}

IMHO this is confusing, if Select object is aware of adapter, why would it not be capable of using. But such is the current design of query objects trying to both be value objects and builders but not always getting it right unless explicitly passed through Sql object. @turrsis https://github.com/turrsis is this something that your refactor fixes? I vaguely saw you move SQL generation to dedicated builder. Will that fix this problem of doing seamingly same thing in two ways but one being technically wrong? If so, this can be closed and maybe marked as fixed with your PR.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/zendframework/zend-db/issues/182#issuecomment-255639978, or mute the thread https://github.com/notifications/unsubscribe-auth/AA4_9LYEF_6-s4fudRP1ZElHh6I6M0WKks5q3CQ2gaJpZM4KdzC9 .

Claudio Eterno via colle dell'Assietta 17 10036 Settimo Torinese (TO) Linux user n. 499785 registered on linuxcounter.net https://linuxcounter.net/user/499785.html

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at https://github.com/laminas/laminas-db/issues/90.