Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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

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.



99.6k
Accepted
answer
edited 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.

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.

edited Jul '16

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.