How to only transfer new column values from one table to another?

Question

I'm attempting to create a new table: _clients that will be simply populated with, for now, with the user_id column from #__user_usergroup_map.

Ideally, the below code should pull all id's from #__user_usergroup_map table if matching group_id = 10 and only id's that aren't already existing within the _clients table.

This appears to work when I add/remove users from the group_id = 10 in phpmyadmin. The $ncl shows a correct list.

#__clients updates successfully upon page refresh, however I get an sql error for duplicate primary key insert on #__clients. It seems it is attempting to add the same user to #__clients again (it added the user to the table on the first refresh).

I thought this code should only be selecting $ncl as those client ids are not already in the #__clients table. Anybody have any thoughts?

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.user_id', 'a.group_id', 'b.id')
      ->from('#__user_usergroup_map as a', '#__clients as b');
$query->join('RIGHT', '#__clients AS b ON a.user_id != b.id')
      ->where('a.group_id = ' . $db->quote("10"));
$db->setQuery($query);
$ncl = $db->loadColumn();
print_r($ncl);

foreach($ncl as $encl) {
    $db    = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->insert('#__clients')
          ->set('id = ' . (int) $encl);
    $db->setQuery($query);
    $db->execute();
}

Answer 1

I just now realized a more simple method... Since I already had existing arrays created, I could simply use something like:

$filteredFoo = array_diff($foo, $bar);

Which will filter out duplicates from the two arrays representing each table and then perform a SQL insert with the $filteredFoo without the risk of dups.

Answer 2

You can adjust your code to:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.user_id as id') /* changed to load object and insert it */
    ->from('#__user_usergroup_map as a')
    ->leftJoin('#__clients AS b ON a.user_id = b.id') /* you should join with "=" not with "!=" */
    ->where('a.group_id = ' . $db->quote("10"))
    ->where('b.id IS NULL'); /* and filter to "new only" like this */
$ncl = $db->setQuery($query)->loadColumn();
print_r($ncl);

foreach($ncl as $encl) {
    $db->insertObject('#__clients',$encl); /* less code this way */
}

it would work fine with this code, but you can go further and make it

$db = JFactory::getDBO();
$sel_query = $db->getQuery(true);
$ins_query = $db->getQuery(true);
$sel_query->select('a.user_id as id')
    ->from('#__user_usergroup_map as a')
    ->leftJoin('#__clients AS b ON a.user_id = b.id')
    ->where('a.group_id = ' . $db->quote("10"))
    ->where('b.id IS NULL');
$ins_query->insert('#__clients')
    ->columns('id')
    ->values($sel_query);
$db->setQuery(str_replace('VALUES','',$ins_query))->execute();

this way you'll do the job with single request.

Answer 3

Because you need only INSERT data from #__user_usergroup_map to #__clients where id is null AND where group_id is 10, you need to use LEFT JOIN to join the tables. Other joins will eliminate rows that produce a NULL column and that is not desirable.

|  #__user_usergroup_map |  LEFT JOIN ON user_id=id  |  #__clients  |
|------------------------|              <            |--------------|
|  user_id  |  group_id  |              <            |      id      |
|-----------|------------|              <            |--------------|
|     1     |     10     |              <            |     NULL     |
|     2     |      9     |              <            |     NULL     |
|     3     |     10     |              <            |     NULL     |
|     4     |      6     |              <            |     NULL     |
|     5     |     10     |              <            |       5      | //previously INSERTED for demonstration's sake
|     6     |     10     |              <            |     NULL     |
|     7     |     10     |              <            |     NULL     |
|     8     |     10     |              <            |     NULL     |
|     9     |      4     |              <            |     NULL     |
|    10     |     10     |              <            |     NULL     |
--------------------------                           ----------------

Before I get to the solutions, I want to bring attention to the fact that your php/Joomla syntax is not generating your intended query.

$query = $db->getQuery(true)
            ->select('a.user_id', 'a.group_id', 'b.id')
            ->from('#__user_usergroup_map as a', '#__clients as b')
            ->join('RIGHT', '#__clients AS b ON a.user_id != b.id')
            ->where('a.group_id = ' . $db->quote("10"));
echo $query->dump();

Generates:

SELECT a.user_id                                    // 2nd & 3rd columns are lost (should have been written as an array, but they were unimportant anyhow)
FROM prefx_user_usergroup_map as a                  // comma joined table is lost (not that it was good for anything)
RIGHT JOIN prefx_clients AS b ON a.user_id != b.id  // this is not the correct join, nor ON logic
WHERE a.group_id = '10'                             // this is half of what is required

This will return zero rows.

---

While I am confident that Alexandr's snippet will provide the expected outcome because the logic/syntax is sound, I'll offer my version that has a few differences.

$db = JFactory::getDBO();
try {
    $select_query = $db->getQuery(true)
                       ->select("A.user_id")
                       ->from("#__user_usergroup_map A")
                       ->leftJoin("#__clients B ON A.user_id = B.id")
                       ->where("A.group_id = 10 AND B.id IS NULL");
    //echo $select_query->dump();

    $insert_query = $db->getQuery(true)
                       ->insert('#__clients')
                       ->columns('id')
                       ->values($select_query);
    // echo $insert_query->dump();

    $db->setQuery($insert_query);
    $db->execute();
    // echo $db->getAffectedRows() , " row(s) inserted into clients table";
} catch (Exception $e) {
    echo "Syntax Error"; // . " & Error: " . $e->getMessage();
}

I have tested my snippet to be successful on my local host. I also created a SQLFiddle Demo if anyone wants to play around.

• I am chaining each query from the getQuery() method to eliminate duplicated mentions of ${word}_query.
• I am not using any qn() or q() calls because they are not necessary for parsing/security reasons.
• I use capitalized table aliases to help them stand out from table names and columns.
• I am not aliasing the column in the SELECT because it is not required.
• setQuery(str_replace('VALUES','',$ins_query)) is not necessary.

In case anyone is concerned that I am shamelessly stealing Alexandr's solution, I'll post a different brute-force query-build that will have the same effect assuming your #__clients.id column is a PRIMARY/UNIQUE KEY. I don't think I would use this less elegant solution (even if, for some reason, it was slightly more performant).

$select_query = $db->getQuery(true)
                   ->select("A.user_id")
                   ->from("#__user_usergroup_map A")
                   ->where("A.group_id = 10");
// echo $select_query->dump();

$insert_query = $db->getQuery(true)
                   ->insert('#__clients')
                   ->columns('id')
                   ->values($select_query);

$db->setQuery(str_replace("INSERT", "INSERT IGNORE", $insert_query));

Answer 4

I think this is a better solution:

$query->select('a.user_id')
      ->from('#__user_usergroup_map as a')
      ->where('a.group_id = '.$db->quote("10"))
      ->where('a.user_id NOT IN (SELECT id FROM #__clients)');

Anyway pay attention to the insert query, you should use values instead of set.

Check this page for more details on Joomla Insert/Update queries