0

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?

  • Are you certain that is the syntax you want? Doesn't appear it would be valid MySQL syntax dev.mysql.com/doc/refman/5.7/en/delete.html. Are you trying to delete a row or modify the table? – David Hayes May 28 at 3:33
  • Hello, In the doc quoted dev.mysql.com/doc/refman/5.7/en/delete.html, it says that there are multiple table syntax : DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] – user3533235 May 30 at 15:40
  • But your query isn't using and doesn't need [LOW_PRIORITY] [QUICK] [IGNORE] keywords in your query. – mickmackusa May 30 at 22:44
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 = ''"]);
  • Could be wrong, but the where clause definitely looks odd. – Lodder May 28 at 6:08
  • How so? Admittedly, I'm taking artustic license. (I'm not at my joomla computer right now.) – mickmackusa May 28 at 6:09
  • @user Are you meaning to delete rows of user_posts or rows of users with no activity? From the outside-looking-in, I feel like you might be trying to prune the dead weight from your users table. My crystal ball is at home with my Joomla computer, but if this is the case, perhaps the table name needs adjusting. – mickmackusa May 28 at 8:43
  • I want to delete some entries in the table which have default values – user3533235 May 30 at 15:49
  • Then my snippets should work as desired unless you have incorrectly posted the table or column names. Have you tried it? What was the outcome? – mickmackusa May 30 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.