How to write a DELETE query with WHERE conditions using JDatabaseQuery syntax?

Question

I have a query which I am trying to convert to JDatabaseQuery syntax, but I'm not able to find the correct way.

The query is the following:

DELETE upt FROM #__mycomponent_user_posts AS upt WHERE upt.posts=0 AND upt.owner=0 AND upt.favorite=0 AND upt.subscribed=0 AND upt.params=''

With JDatabaseQuery, I'm only able to obtain the following query with nothing between DELETE and FROM:

DELETE FROM #__mycomponent_user_posts AS upt WHERE upt.posts=0 AND upt.owner=0 AND upt.favorite=0 AND upt.subscribed=0 AND upt.params=''

How to obtain the correct query with JDatabaseQuery?

Answer 1

A MySQL DELETE query doesn't have a column value between keywords DELETE and FROM.

There is also no benefit to declaring a table alias.

owner is a mysql keyword, but not a RESERVED keyword, so it doesn't need to be backtick-wrapped.

$db = Factory::getDBO();
try {
    $query = $db->getQuery(true)
                ->delete("#__mycomponent_user_posts")
                ->where(["posts + owner + favorite + subscribed = 0",
                         "params = ''"]);
    $db->setQuery($query);
    $db->execute();
    if (!$affrows = $db->getAffectedRows()) {
        echo "Possible Logic Error - No Qualifying Rows To Delete";
    }
    echo "Success $affrows Deleted Rows";
} catch (Exception $e) {
    echo "Syntax Error"; // . "Query: " . $query->dump() . " & Error: " . $e->getMessage();
}

I am actually taking the liberty to condense your where clause logic using simple mathematics.

I have built in some diagnostic checks for you. If you want to see the generated query, echo $query->dump(). Be sure to never reveal raw mysql errors to the public.

[I am not at home to test my snippet, let me know if I have any mistakes in it so that I can adjust.]

To mirror your original where clause logic...

$query = $db->getQuery(true)
            ->delete("#__mycomponent_user_posts")
            ->where(["posts = 0",
                     "owner = 0",
                     "favorite = 0",
                     "subscribed = 0",
                     "params = ''"]);