ZF2 + Zend\Db\Sql\Insert + ON DUPLICATE KEY UPDATE

Question

I would like to insert some data using Zend\Db\Sql\Insert with the option ON DUPLICATE KEY UPDATE

Actually I use this kind of query:

INSERT INTO `user_data` (`user_id`, `category`, `value`) 
VALUES (12, 'cat2', 'my category value')
 ON DUPLICATE KEY UPDATE `user_id`=VALUES(`user_id`), `category`=VALUES(`category`), `value`=VALUES(`value`);

with

$this->dbAdapter->query($sql, Adapter::QUERY_MODE_EXECUTE);

But how I can generate this above query with Zend\Db\Sql\Insert ?

thanks for any suggestion. Cheers

not sure you can, you can do it with a raw query though. I extended the Insert to allow for this in one project i'll dig out the code — Andrew, Apr 15 '13 at 08:03
I just reviewed the current ZF2 docs (v 2.1), and it doesn't look like it has support for the ON DUPLICATE KEY UPDATE. However, what you might want to consider is extending the Insert class into your own InsertUpdate class. — jon__o, Apr 15 '13 at 18:56
ok thanks. I will extend the Insert class to process this. I will post some code soon. — Remi Thomas, Apr 15 '13 at 20:35

score 3 · Answer 1 · answered Sep 10 '14 at 23:53

Maybe this solution help you.

https://gist.github.com/newage/227d7c3fb8202e473a76

/**
* Use INSERT ... ON DUPLICATE KEY UPDATE Syntax
* @since mysql 5.1
* @param array $insertData For insert array('field_name' => 'field_value')
* @param array $updateData For update array('field_name' => 'field_value_new')
* @return bool
*/
public function insertOrUpdate(array $insertData, array $updateData)
{
$sqlStringTemplate = 'INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s';
$adapter = $this->tableGateway->adapter; /* Get adapter from tableGateway */
$driver = $adapter->getDriver();
$platform = $adapter->getPlatform();

$tableName = $platform->quoteIdentifier('table_name');
$parameterContainer = new ParameterContainer();
$statementContainer = $adapter->createStatement();
$statementContainer->setParameterContainer($parameterContainer);

/* Preparation insert data */
$insertQuotedValue = [];
$insertQuotedColumns = [];
foreach ($insertData as $column => $value) {
$insertQuotedValue[] = $driver->formatParameterName($column);
$insertQuotedColumns[] = $platform->quoteIdentifier($column);
$parameterContainer->offsetSet($column, $value);
}

/* Preparation update data */
$updateQuotedValue = [];
foreach ($updateData as $column => $value) {
$updateQuotedValue[] = $platform->quoteIdentifier($column) . '=' . $driver->formatParameterName('update_' . $column);
$parameterContainer->offsetSet('update_'.$column, $value);
}

/* Preparation sql query */
$query = sprintf(
$sqlStringTemplate,
$tableName,
implode(',', $insertQuotedColumns),
implode(',', array_values($insertQuotedValue)),
implode(',', $updateQuotedValue)
);

$statementContainer->setSql($query);
return $statementContainer->execute();
}

I developed a code to use the truely "update on duplicate" sql https://github.com/remithomas/rt-extends — Remi Thomas, Sep 11 '14 at 11:46

Remi Thomas · Accepted Answer · 2014-09-11T11:45:18.723

I did this code: an extended class of Sql\Insert

It's working but I think I need to light it. Because I just update lastest lines of "prepareStatement" and "getSqlString" from the Zend\Db\Sql\Insert base.

You can find my plugin here https://github.com/remithomas/rt-extends

What do you think ?

<?php

namespace RtExtends\Sql;

use Zend\Db\Adapter\AdapterInterface;
use Zend\Db\Adapter\StatementContainerInterface;
use Zend\Db\Adapter\ParameterContainer;
use Zend\Db\Adapter\Platform\PlatformInterface;
use Zend\Db\Adapter\Platform\Sql92;

use Zend\Db\Sql\Insert as ZendInsert;

class Insert extends ZendInsert{

    /**
     * Constants 
     */
    const SPECIFICATION_INSERTDUPLICATEKEY = 'insert';
    const VALUES_DUPLICATEKEY = "duplicatekey";

    /**
     * @var array Specification array
     */
    protected $duplicatespecifications = array(
        self::SPECIFICATION_INSERTDUPLICATEKEY => 'INSERT INTO %1$s (%2$s) VALUES (%3$s) ON DUPLICATE KEY UPDATE %4$s'
    );

    /**
     * Constructor
     *
     * @param  null|string|TableIdentifier $table
     */
    public function __construct($table = null)
    {
        parent::__construct($table);
    }

    /**
     * Prepare statement
     *
     * @param  AdapterInterface $adapter
     * @param  StatementContainerInterface $statementContainer
     * @return void
     */
    public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
    {
        $driver   = $adapter->getDriver();
        $platform = $adapter->getPlatform();
        $parameterContainer = $statementContainer->getParameterContainer();

        if (!$parameterContainer instanceof ParameterContainer) {
            $parameterContainer = new ParameterContainer();
            $statementContainer->setParameterContainer($parameterContainer);
        }

        $table = $this->table;
        $schema = null;

        // create quoted table name to use in insert processing
        if ($table instanceof TableIdentifier) {
            list($table, $schema) = $table->getTableAndSchema();
        }

        $table = $platform->quoteIdentifier($table);

        if ($schema) {
            $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
        }

        $columns = array();
        $values  = array();

        foreach ($this->columns as $cIndex => $column) {
            $columns[$cIndex] = $platform->quoteIdentifier($column);
            if (isset($this->values[$cIndex]) && $this->values[$cIndex] instanceof Expression) {
                $exprData = $this->processExpression($this->values[$cIndex], $platform, $driver);
                $values[$cIndex] = $exprData->getSql();
                $parameterContainer->merge($exprData->getParameterContainer());
            } else {
                $values[$cIndex] = $driver->formatParameterName($column);
                if (isset($this->values[$cIndex])) {
                    $parameterContainer->offsetSet($column, $this->values[$cIndex]);
                } else {
                    $parameterContainer->offsetSet($column, null);
                }
            }
        }

        $sql = sprintf(
            $this->duplicatespecifications[self::SPECIFICATION_INSERTDUPLICATEKEY],
            $table,
            implode(', ', $columns),
            implode(', ', $values),
            implode(",", array_map(array($this, "mapValue"), $columns))
        );

        $statementContainer->setSql($sql);
    }

    /**
     * Get SQL string for this statement
     *
     * @param  null|PlatformInterface $adapterPlatform Defaults to Sql92 if none provided
     * @return string
     */
    public function getSqlString(PlatformInterface $adapterPlatform = null)
    {
        $adapterPlatform = ($adapterPlatform) ?: new Sql92;
        $table = $this->table;
        $schema = null;

        // create quoted table name to use in insert processing
        if ($table instanceof TableIdentifier) {
            list($table, $schema) = $table->getTableAndSchema();
        }

        $table = $adapterPlatform->quoteIdentifier($table);

        if ($schema) {
            $table = $adapterPlatform->quoteIdentifier($schema) . $adapterPlatform->getIdentifierSeparator() . $table;
        }

        $columns = array_map(array($adapterPlatform, 'quoteIdentifier'), $this->columns);
        $columns = implode(', ', $columns);

        $values = array();
        foreach ($this->values as $value) {
            if ($value instanceof Expression) {
                $exprData = $this->processExpression($value, $adapterPlatform);
                $values[] = $exprData->getSql();
            } elseif ($value === null) {
                $values[] = 'NULL';
            } else {
                $values[] = $adapterPlatform->quoteValue($value);
            }
        }

        $values = implode(', ', $values);
        $valuesDuplicate = implode(",", array_map(array($this, "mapValue"), $columns));

        return sprintf($this->duplicatespecifications[self::SPECIFICATION_INSERTDUPLICATEKEY], $table, $columns, $values, $valuesDuplicate);
    }

    private function mapValue($columns){
        return $columns."=VALUES(".$columns.")";
    }
}

?>

ZF2 + Zend\Db\Sql\Insert + ON DUPLICATE KEY UPDATE

2 Answers2