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 zendframework/zendframework#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 notifications@github.com:

According to zendframework/zendframework#3224
zendframework/zendframework#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
#182 (comment),
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 laminas/laminas-db#90.