UPDATE "first argument is not an array" warning and "SQLSTATE[HY000]: General error" on successful execution

Question

UPDATE "first argument is not an array" warning and "SQLSTATE[HY000]: General error" on successful execution

PrincessOfSecret Jul '16

Created Jul '16	Last Reply Jul '16	Replies 2	Views 582	Votes 0

PrincessOfSecret 3.4k

Jul '16

Hi,

I experienced a strange behavior when I tried to update a table with ModelsManager.

$q = "UPDATE [\Main\Models\TopBanners] as top_banners SET top_banners.banner_id = NULL WHERE top_banners.banner_id NOT IN (SELECT banners.id FROM [\Main\Models\Banners] AS banners)";
$result = $this->modelsManager->executeQuery($q);

When I execute it this way, it fails and I always get this warning: Warning: First argument is not an array in (the line where the execution is)

When I try to execute it using raw SQL (as the example in the documentation says) like:

$q = "UPDATE top_banners SET top_banners.banner_id = NULL WHERE top_banners.banner_id NOT IN (SELECT banners.id FROM banners)";
$top = new \Main\Models\TopBanners();
$result = new \Phalcon\Mvc\Model\Resultset\Simple(null, $top, $top->getReadConnection()->query($q));

The query is successful, but it still produce the error: PDOException: SQLSTATE[HY000]: General error (for the execution line)

Phalcon debug:

0 PDOStatement->fetchAll()
1 Phalcon\Db\Result\Pdo->fetchAll()
2 Phalcon\Mvc\Model\Resultset->__construct(Object(Phalcon\Db\Result\Pdo), null)
3 Phalcon\Mvc\Model\Resultset\Simple->__construct(null, Object(Main\Models\TopBanners: ), Object(Phalcon\Db\Result\Pdo)) >> IT FAILS HERE
4 Main\Controllers\Admin\UsersController->deleteAction(12)
5 Phalcon\Dispatcher->dispatch()
6 Phalcon\Mvc\Application->handle()

But when I just execute it without Resultset, everything works fine, successful update, no warnings, no errors:

$q = "UPDATE top_banners SET top_banners.banner_id = NULL WHERE top_banners.banner_id NOT IN (SELECT banners.id FROM banners)";
$top = new \Main\Models\TopBanners();
$result = $top->getReadConnection()->query($q);

So what the hell is going on here? Why does it refuse to execute the query with the ModelsManager? And why does it output general error when the query is successfully executed? I'm trying to undersatnd but I'm confused...

Thanks in advance for your ideas.

Wojciech Ślawski
145.0k

Accepted
answer

edited Jul '16
Jul '16

PHQL just don't support syntax like this. PHQL don't have all abilites from SQL, same thing i guess would happen for example in doctrine and other ORMs.

PrincessOfSecret · Answer 1 · 2016-07-06T10:14:22-07:00

Well, it doesn't support UPDATE with JOIN, too, that was the first version of my query, but quickly found out that I can't do that. In my latest project I run into these boundaries of PHQL day by day. ModelsManager's SELECT statements are great, I like to work with them, but it's a pity that UPDATE, INSERT and DELETE statements are not developed this way. I hope they will be in the future, I'm waiting for this.

Wojciech Ślawski · Answer 2 · 2016-07-06T11:59:58-07:00

Well youy can solve update with join using subselect in where condition instead of join :)

Basically every update/insert/delete queries in PHQL is translated into SELECT + calling methods like delete/save/update etc on objects with foreach to make sure that all events etc are executed correctly.