[Solved] Extending joomla users table using a join

[Sophist]

I don't want to add columns to the joomla users table, but I want to store some stats for each user.

So I have create a list and done a Right Join on the users table (in order that the list shows every user). That works fine, showing empty rows where the list hasn't got an entry for the user.

But I have no idea how to provide an edit button / linked field on the list which will create a new record in this list with the foreign key populated and username shown.

I am unclear at this point whether I am approaching this from the wrong direction entirely or whether this is just a question of crafting the right URL for the edit button / link.

Any help gratefully received.

 

[Sophist]

Using the PHP Events List Plugin, I created a short piece of code that inserts blank records for any outerjoin rows that have a joomla user row but not a row in my own table and adjusts any links. In case anyone else finds this useful, here is the code:

111$data = $model->getData();

$myDB = NULL;

foreach ($data as $group) {
  foreach ($group as $row) {
    // If user_id is null then insert a row
    if ($row->__pk_val == NULL) {
      if ($myDB == NULL) {
        // Get a db connection.
        $myDB = JFactory::getDbo();
        // Create a new query object.
        $myQry = $myDB->getQuery(true);
      }

      // Prepare insert.
      $myQry
        ->insert($myDB->quoteName('me_stats'))
        ->columns($myDB->quoteName('users_id'))
        ->values($myDB->quote($row->j3_users___id_raw));

      // Execute the query
      $myDB->setQuery($myQry);
      try {
        $result = $myDB->execute();
        $newid = (int) $myDB->insertid(); //get new record id
        foreach ($row as $key => $val) {
          $row->$key = preg_replace('/(&(?:amp;)?rowid=)(["&]|$)/','${1}'.$row->j3_users___id_raw.'${2}',$val);
        }
      }
      catch (Exception $e) {
        JFactory::getApplication()->enqueueMessage('Insert of new empty record failed for '.$row->j3_users___name_raw.': '.$e->getMessage(), 'error');  
      }
    }
  }
}