3

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();
}
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.

  • Because this can be done more elegantly with a single call to the database (cut out the middleman -- php), please consider the answers from Alexandr and myself. – mickmackusa Jun 8 '18 at 6:29
1

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.

0

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));
-1

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

  • 2
    Not the most efficient way as subQuery in WHERE clause would be executed as many times as rows in the FROM table. and it's at least "count of users at the site +1" so it may make your MySql hang in a while. – Alexandr Oct 27 '17 at 14:49

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.