ZF2-479: Smarter handling of null value

Issue Type: Improvement Created: 2012-08-19T20:47:18.000+0000 Last Updated: 2012-08-22T06:57:09.000+0000 Status: Resolved Fix version(s): Reporter: coolmic (coolmic) Assignee: Ralph Schindler (ralph) Tags: - Zend\Db

  • Zend_Db
  • Zend_Db_Sql

Related issues: Attachments:


I often use NULL values in database, when I use foreign keys for example

now, if I try

<pre class="highlight">
        $sql = new \Zend\Db\Sql\Sql($this->adapter);
        $insert = $sql->insert('post')
                ->values(array('post_id' => null, 'user_id' => null, 'post_title' => 'foobar'));
        $query = $sql->getSqlStringForSqlObject($insert);
        $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);

user_id has a fk constraint.

It would fail because the generated sql string will be

<pre class="highlight">
INSERT INTO `post` (`post_id`, `user_id`, `post_title`) VALUES ('', '', 'foobar')

If I want this to work, I have to use \Zend\Db\Sql\Expression:

<pre class="highlight">
->values(array('post_id' => null, 'user_id' => new \Zend\Db\Sql\Expression('null'), 'post_title' => 'foobar'));

I guess it's would be nice feature to automatically transform php NULL value to \Zend\Db\Sql\Expression('null') .

The same for Where

<pre class="highlight">
        $update = $sql->update('post')
                ->set(array('user_id' => 1))
                ->where(array('user_id' => null, 'post_valid' => 1));
        $query = $sql->getSqlStringForSqlObject($update);

The generated sql string will be

<pre class="highlight">
UPDATE `post` SET `user_id` = '1' WHERE `user_id` = '' AND `post_valid` = '1'

The workaround is a little more verbose

<pre class="highlight">
        $update = $sql->update('post')
                ->set(array('user_id' => 1));
        $update->where->addPredicate(new \Zend\Db\Sql\Predicate\IsNull('user_id'));
        $update->where->addPredicate(new \Zend\Db\Sql\Predicate\Operator('post_valid', Predicate\Operator::OPERATOR_EQUAL_TO, 1));
        $query = $sql->getSqlStringForSqlObject($update);

2 things to enhance here :

Automatically transform null value to \Zend\Db\Sql\Predicate\IsNull, and allow something like this

<pre class="highlight">
        $update = $sql->update('post')
                ->set(array('user_id' => 1))
                   new \Zend\Db\Sql\Predicate\IsNull('user_id'),
                   'post_valid' => 1
        $query = $sql->getSqlStringForSqlObject($update);

Protip :

If I use something like :

<pre class="highlight">
      $select = $sql->select('post')
                   'post_id' => array(1, 2, 3)
        $query = $sql->getSqlStringForSqlObject($select);

It would be nice to detect that the post_id value is an array, and automatically use the predicate \Zend\Db\Sql\Predicate\In instead of \Zend\Db\Sql\Predicate\Operator


Posted by coolmic (coolmic) on 2012-08-20T14:22:32.000+0000

Pull-request :

Posted by Maks 3w (maks3w) on 2012-08-22T06:57:09.000+0000

Patch merged

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.