[NFR] UPDATE query with JOIN in PHQL

[StudioMaX]

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

raw 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?

phalcon: 2.0.10
Reelated forum thread: https://forum.phalconphp.com/discussion/11065/update-query-with-join-in-phql

[Jurigag]

As i wrote earlier. Did you tried it with subquieries INSTEAD of joins ?

This types of PHQL queries i guess are not supported. But i think that subqueries should work so:

UPDATE Version
SET Version.Price=Version.PriceOrig
WHERE (SELECT ComposerRef FROM RelArtistTrack WHERE RelArtistTrack.TrackRef = Version.TrackRef) = :artistId:
AND (Version.Price=0) OR (Version.Price IS NULL)

It should work fine. I tested it already.

[StudioMaX]

Do you mean PHQL query like this?

UPDATE Version
SET Version.Price=PriceOrig
WHERE Version.TrackRef IN (SELECT TrackRef FROM RelArtistTrack WHERE RelArtistTrack.ComposerRef=:artistId:)
AND (Version.Price=0) OR (Version.Price IS NULL)

I think it might be useful only for the most simple queries (update data in one table based on ids from another table). But what about joining multiple tables?

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

Or calculating values based on the values of a few records from different tables?

SET Version.Price=Version.PriceOrig * Artist.PriceModifier

[Jurigag]

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

will be something like:

UPDATE Version
SET Version.Price=Version.PriceOrig
WHERE (SELECT ComposerRef FROM RelArtistTrack WHERE RelArtistTrack.TrackRef = Version.TrackRef) = :artistId: AND
 (SELECT Artist.Status FROM RelArtistTrack LEFT JOIN Artist WHERE RelArtistTrack.TrackRef = Version.TrackRef) = 1
AND (Version.Price=0) OR (Version.Price IS NULL)

When you have done your relations between models then ON clause is not needed i think.

SET Version.Price=Version.PriceOrig * Artist.PriceModifier

will be something like:

SET Version.Price=Version.PriceOrig * (SELECT Artist.PriceModifier FROM RelArtistTrack LEFT JOIN Artist WHERE RelArtistTrack.TrackRef = Version.TrackRef)

But it will become uglier and uglier. So the joins would be really helpful. You can rewirte joins to subqueries like in 90% of times, but it will look awful, but if you need "joins" you can use this fallback for now.

@sergeyklay i think it would be really nice if phql could support joins in update statements.

[andrew-demb]

+1

[StudioMaX]

This is still relevant.

[StudioMaX]

Still doesn't work.

[StudioMaX]

Bump

[StudioMaX]

Bump

[StudioMaX]

Bump

[StudioMaX]

Still relevant