Nette\Database\Table\Selection::insert() to PostgreSQL table with PK without sequence

3 years ago

carlos
Member | 4
+
0
-

Hi,

I have a problem with inserting to table with primary key without sequence. Nette (PDO) not throw exception, bud in SQL log is

2017-01-25 01:39:24 GMT ERROR:  lastval is not yet defined in this session
2017-01-25 01:39:24 GMT STATEMENT:  SELECT LASTVAL()

PostgreSQL 9.6.1, PHP 5.6, Nette 2.4

on every INSERT called query like this

$table->insert([
  'basket_id' => $basketId,
  'product_id' => $productId,
  'quantity' => $quantity
]);
-- primary key basket_id, product_id
INSERT INTO "basket_product" ("basket_id", "product_id", "quantity")
VALUES (132, 17489, 1);

SELECT LASTVAL();

but LASTAVAL() can be called only after update sequence

Its bug or bad using?

I tried fix it

$primaryKey = NULL;
$primarySequence = $this->getPrimarySequence();

if ( ! $this->context->getConnection()->getSupplementalDriver()->isSupported(Nette\Database\ISupplementalDriver::SUPPORT_SEQUENCE) && ! $primarySequence) {
    $primaryKey = $this->context->getInsertId();

} elseif ($primarySequence) {
    $primaryKey = $this->context->getInsertId(implode('.', array_map([$this->context->getConnection()->getSupplementalDriver(), 'delimite'], explode('.', $primarySequence))));
}

instead of

$primaryKey = $this->context->getInsertId(
            ($tmp = $this->getPrimarySequence())
                ? implode('.', array_map(array($this->context->getConnection()->getSupplementalDriver(), 'delimite'), explode('.', $tmp)))
                : NULL
        );

to block call lastInsettId().

Thanks

3 years ago

carlos
Member | 4
+
0
-

Now I fixed it like this.
But, I'm curious, where is problem in my previous post :-)

abstract class BaseModel
{

    /**
     * @var Context
     */
    protected $context;


    /**
     * @param Context $context
     */
    public function __construct(Context $context)
    {
        $this->context= $context;
    }


    /**
     * @param string|Selection $table
     * @param array $data
     */
    public function insert($table, array $data)
    {
        $tableName = ($table instanceof Selection ? $table->getName() : $table);
        $sqlBuilder = new SqlBuilder($tableName, $this->context);
        $this->context->query($sqlBuilder->buildInsertQuery() . ' ?values', $data);
    }

}
class BasketFacade extends BaseModel
{

    public function addProduct($basketId, $productId, $quantity)
    {
        $params = [
            'basket_id' => $basketId,
            'product_id' => $productId,
            'quantity' => $quantity
        ];
        $this->insert($this->getBasketProducts(), $params);
    }


    /**
     * @return Selection
     */
    public function getBasketProducts()
    {
        return $this->context->table('basket_products');
    }

}