We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

UPDATE query with JOIN in PHQL

Hi,

I have some raw-sql queries, which I want translate to PHQL. For example:

raq sql:

UPDATE versions
LEFT JOIN composerlink ON composerlink.TrackRef=versions.TrackRef
SET Price=PriceOrig
WHERE composerlink.ComposerRef=:artistId
AND (Price=0) OR (Price IS NULL)

And PHQL query of this sql query:

public static function updateVersions($artistId)
{
        $artist = new Artist();
        $phql = "
            UPDATE Version
            LEFT JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef
            SET Version.Price=Version.PriceOrig
            WHERE RelArtistTrack.ComposerRef=:artistId:
            AND (Version.Price=0) OR (Version.Price IS NULL)
        ";
        $result = $artist->getModelsManager()->executeQuery($phql, ['artistId' => $artistId]);
}

So, it's very simple UPDATE query with JOIN. But after executing this query I see an error:

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token LEFT, near to ' JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef\n   SET Version.Price=Version.PriceOrig\n   WHERE RelArtistTrack.ComposerRef=:artistId:\n   AND (Version.Price=0) OR (Version.Price IS NULL)\n  ', when parsing: \n UPDATE Version\n    LEFT JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef\n  SET Version.Price=Version.PriceOrig\n   WHERE RelArtistTrack.ComposerRef=:artistId:\n   AND (Version.Price=0) OR (Version.Price IS NULL)\n  (231)

If I understand correctly, these types of PHQL queries are not supported? Or this is a bug?



85.5k
edited Apr '16

i am probably wrong but Version might be a reserved word... if you try 


$phql = "
            UPDATE [Version]
            LEFT JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef
            SET Version.Price=Version.PriceOrig
            WHERE RelArtistTrack.ComposerRef=:artistId:
            AND (Version.Price=0) OR (Version.Price IS NULL)
        ";


1.1k

Unfortunately, this didn't help. The error is still the same. (Also I've added brackets to all uses of Version)



145.0k

How about trying to update like this:

UPDATE Version, RelArtistTrack

Or using sub queries ? I guess phalcon don't support joins in update which is kind of bad.



1.1k

When updating like this

UPDATE Version, RelArtistTrack
LEFT JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef
SET Version.Price=Version.PriceOrig
WHERE RelArtistTrack.ComposerRef=:artistId:
AND (Version.Price=0) OR (Version.Price IS NULL)

I've got this error

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token COMMA, near to ' RelArtistTrack\n   LEFT JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef\n  SET Version.Price=Version.PriceOrig\n   WHERE RelArtistTrack.ComposerRef=:artistId:\n   AND (Version.Price=0) OR (Version.Price IS NULL)\n  ', when parsing: \n UPDATE Version, RelArtistTrack\n    LEFT JOIN RelArtistTrack ON RelArtistTrack.TrackRef=Version.TrackRef\n  SET Version.Price=Version.PriceOrig\n   WHERE RelArtistTrack.ComposerRef=:artistId:\n   AND (Version.Price=0) OR (Version.Price IS NULL)\n  (247)

Probably I need to create an issue on github about this use case.



145.0k
edited Apr '16

I mean when you use Update TABLE1, TABLE2 you don't need join, how about removing joing and try to use sub query ?

I think with subqueries it should work for sure.



12.2k


145.0k

@StudioMaX

As i already mentioned SUBQUERIES instead of JOINS are working fine.



1.1k

Yes, subqueries could solve this issue, but this is very ugly workaround and cannot be used in real-world databases with hundreds of thousands of records. I do not think that using of sub-queries for each record is a good idea.



145.0k
edited Apr '16

If you do join then mysql is doing itself subquery - but you just don't know about it. If you have indexes etc then there will be no diffrence in cost of performance(in most cases). Well it depends how many those queries you have. You can always use raw queries.