Insert query with ignore of duplicates (without key)

Question

I have a query:

$query_add_analogue
  ->insert($db->quoteName('#__parts_analogues'))
  ->columns($db->quoteName($columns))
  ->values($value);
$db->setQuery($query_add_analogue);
$db->execute();

The table "#_parts_analogues" have two columns - "original_id" and "analogue_id".

I need to ignore the insert function, when the pair of values already exist in table. There is no primary key in table.

Answer 1

As you're using MySQL, you can create an unique index over your columns original_id and analogue_id.

Then use a raw query like:

$db = JFactory::getDbo();
$query = 'INSERT IGNORE INTO ' . $db->quoteName('#__parts_analogues') VALUES ("your", "values");
$db->setQuery($query)->execute();

It works like an insert, but does not return an error.

Keep in mind that working without an index can lead to serious performance issues.

Answer 2

I give a solution on this just for the new readers who may are looking for solution on this kind of problems. You cannot really solve this problem with Joomla query methods. As it was given above, a simple MySQL query like "INSERT IGNORE INTO" should work but it is not always the right solution. Instead, the "INSERT INTO...ON DUPLICATE KEY UPDATE" MySql query works. Why? Because if a record already exists, then it will be just updated (probably with the same record or with an updated record with the same ids and unique fields, so it is not a problem), if the record does not exist, then it will be created, inserted as a new record in the table.

First, understanding this particular problem in the original question (in this problem you want to insert or update a pair of keys into the table), you should not make the two columns in the table UNIQUE - "original_id" and "analogue_id". Because if you make them unique then you cannot insert again a pair of new keys where one of the keys is the same as the earlier inserted key of the pair. For example you earlier inserted a pair of keys like original_id = 1 and analogue_id = 1, and now you try to insert a new pair like original_id = 1 and analogue_id = 2. If these keys are both UNIQUE in the table you will not be able to insert the second pair as new pair and at the same time you will not want to update them either.

So what I would do is I would generate (in my code) a real UNIQUE key from the original_id and analogue_id keys and I would insert them together with the generated Unique key into the table. And then when I am updating the table with new data or inserting new data in the table I would use for example:

$my_unique_key = $original_id . $analogue_id . "something_to_make_it_more_unique";

$myTableItems = new stdClass();

// You can put all of the data items into the $myTableItems object
// if you have many of these data
// I will not do that here due to time saving

// and in foreach loop

foreach ($myTableItems as $item) {

  $fields = array($db->quoteName('myunique_key') . ' = ' . $db->quote($item->my_unique_key),
            $db->quoteName('original_id') . ' = ' . $db->quote($item->original_id),
            $db->quoteName('analogue_id') . ' = ' . $db->quote($item->analogue_id));

  $values = array($item->my_unique_key, $item->original_id, $item->analogue_id);

  $query = "INSERT INTO #__mytable (myunique_key, original_id, analogue_id) VALUES ('" . implode("', '", $values) . "')
  ON DUPLICATE KEY UPDATE" . implode(", ", $fields);

  $db->setQuery($query)->execute();
  }

And if you want to just insert the new records and you do not want to update the old records then you can use the INSERT IGNORE INTO query like this:

$query = "INSERT IGNORE INTO #__mytable (myunique_key, original_id, analogue_id) VALUES ('" . implode("', '", $values) . "')";

Simple reference on these Sql queries is for example here.

Answer 3

Option #1 - the clean way (my recommended approach)

Call this query from phpMyAdmin to permanently establish the two columns as UNIQUE KEYS. (You will need to replace the #_ prefix with your private prefix string.)

ALTER TABLE `#__parts_analogues` ADD UNIQUE KEY(original_id, analogue_id)

Then whenever you wish to throw new rows into the table, you can use a INSERT IGNORE INTO query to avoid receiving those syntax errors. This adjustment is applied inside the setQuery() call.

Here is how I would write the OO code:

$db = JFactory::getDBO();
try {
    $insert_query = $db->getQuery(true)
                       ->insert("#__parts_analogues")
                       ->columns("original_id, analogue_id")
                       ->values($value); // I'm not sure if you are delivering a string, 1-dim array, or multi-dim array

    $db->setQuery(preg_replace('~INSERT \K~', 'IGNORE ', $insert_query, 1));    // add IGNORE after first occurring `INSERT ` in query
    $db->execute();
    echo $db->getAffectedRows() , " row(s) inserted into parts_analogues table";
} catch (Exception $e) {
    echo  "Syntax Error"; // . " & Error: " . $e->getMessage();
}

I'm not sure if $value is a comma-separated string, a one-dimensional array containing two elements, or a multi-dimensional array containing sets of 2-element subarrays. If these id values are integers and coming from an external source, you should cast each id as (int) before feeding to the query for security reasons. Here's some relevant literature:

• https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase#Inserting_a_Record
• https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_values
• https://docs.joomla.org/Secure_coding_guidelines#Constructing_SQL_queries

I should also point out that there is technically a possibility of making unintended multiple replacements within the query while using str_replace(). For this reason, I am using preg_replace() with a replacement limit of 1 for peace of mind.

Another note: Joomla puts \r\n (or just \n depending on your operating system) at the start of the query; var_dump($db) after setQuery() to see. For this reason, my pattern does not use ~^INSERT ~, but I could just as easily use ~^\sINSERT~ or ~^\RINSERT ~ to anchor the pattern to the start of the string.

---

Option #2 - which IMO is the less elegant way because it requires two round trips to the database.

Basically, you would run a SELECT COUNT(*) query with a WHERE clause that searches for matches using your $values data to determine if the pair of values exists in the table, then you call an INSERT query when it is unique. *note, you can use two where() method calls, but I don't prefer that style because it is too much of a departure from raw sql syntax. You could also hardcode the AND and write a single string, but I was trying to get the whole code block to fit the available width on this page.

$db = JFactory::getDBO();
try {
    $select_query = $db->getQuery(true)
                       ->select("COUNT(*)")
                       ->from("#__parts_analogues")
                       ->where(
                           array(
                             "original_id = " . (int)$original_id),
                             "analogue_id = " . (int)$analogue_id)
                           )
                         )
                       ->setLimit(1);

    $db->setQuery($select_query);
    if (!$db->loadResult()) {
        // if count is 0 (false-y) that means the values are unique
        $insert_query = $db->getQuery(true)
                           ->insert("#__parts_analogues")
                           ->columns("original_id, analogue_id")
                           ->values((int)$original_id . "," . (int)$analogue_id);

        $db->setQuery($insert_query);
        $db->execute();
        echo "Row Added";
    } else {
        echo "Non-unique Values";
    }
} catch (Exception $e) {
    echo  "Syntax Error"; // . " & Error: " . $e->getMessage();
}

Answer 4

After some feedback from @Dom, I think I mostly agree that the other existing answers up to this point are providing hackish techniques on the query which will either:

1. be less portable
2. lead to inconsistent query building techniques throughout a single project
3. be vulnerable to race conditions
4. and/or diminish script readability

Some suggested techniques advise to adjust the query (my earlier answer included) to enjoy the features of a limited number of database types. This means that if/when you want to switch your project to another database, your code will break because the keyword/technique is not available.

More generally, I think it is best practice to fully utilise Joomla's query building helper methods ...until absolutely necessary to circumvent them.

And finally, making two trips to a database (one to check if unique identifiers exist and a second to insert when missing) is not a 100% reliable solution because there is technically the potential that a concurrent query write can occur on the target row in the moment between the first and the second query executing. (Not very likely, but possible)

TL;DR

Just make your identifying columns unique:

ALTER TABLE `#__parts_analogues` ADD UNIQUE KEY(original_id, analogue_id)

Then execute your insert query normally, but catch the error so that there are never any unexpected interruptions to your application.

$query_add_analogue
  ->insert($db->quoteName('#__parts_analogues'))
  ->columns($db->quoteName($columns))
  ->values($value);
$db->setQuery($query_add_analogue);
try {
    $db->execute();
} catch (Exception $e) {
    // do nothing
}

I don't know if other databases have a different error number for Duplicate Key errors (anyone is welcome to educate me), but in MySQL it is specifically 1062, so you could even specify which error to ignore and which ones you actually want to acknowledge (in case your query might fail in a different way).

try {
    $db->execute();
} catch (Exception $e) {
    if ($e->getCode() == 1062) {
        // do nothing with duplicate key failure
    } else {
        // log the error or present to screen with enqueueMessage or whatever
    }
}

P.S. You might want to research $db->insertObject() to execute the insert as a means of a more concise coding technique.