Zend\Db\Adapter\Driver problem

Question

Zend\Db\Adapter\Driver problem

Closed this issue 4 years ago · 3 comments

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);
    ...
}

Originally posted by @lonejack at zendframework/zend-db#182

Answer 1 · 2020-01-16T19:27:43.000Z

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.

Originally posted by @alextech at zendframework/zend-db#182 (comment)

Answer 2 · 2020-01-16T19:27:46.000Z

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
zendframework/zend-db#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

Originally posted by @lonejack at zendframework/zend-db#182 (comment)

Answer 3 · 2021-02-23T22:10:39.000Z

This package is considered feature-complete, and is now in security-only maintenance mode, following a decision by the Technical Steering Committee.
If you have a security issue, please follow our security reporting guidelines.
If you wish to take on the role of maintainer, please nominate yourself

If you are looking for an actively maintained package alternative, we recommend:

doctrine/dbal