Suggested fix for CSV import of lists with joined tables

[ghicar]

As reported previously (forum id 53614), if I try to import into a list that has a joined table it just doesn't work. However, I have implemented a couple of code fixes/workarounds that allow me to successfully do my imports to both base and joined table(s). The joined table import being facilitated by making use of some custom PHP code added to the "After Import Row PHP Code" of the listcsv plugin config.

I have to reapply the two code fixes after every Fabrik update and would like some consideration to be made if something similar can be done in the core fabrik code base.

I describe here what I find and how I worked around the issue using a very simple use case.

Use case
I have a simple test case with two Frabik lists

• testbasetable with elements id, date_time, subject
• testjoinedtable with elements id, date_time, base_table_id, category

id and date_time being the standard Frabrik elements, testbasetable.subject & testjoinedtable.category are simple field elements and testjoinedtable.base_table_id is also a field element set to integer format.

testbasetable left joins testjoinedtable on testbasetable.id = testjoinedtable.base_table_id with repeats allowed.

I created a simple CSV file to import two rows into testbasetable only with content:
subject
sub1
sub2

when you try to do the csv import Fabrik bombs out:

count(): Argument #1 ($value) must be of type Countable|array, null given
# Function Location
1 () JROOT/components/com_fabrik/models/form.php:1798
2 FabrikFEModelForm->prepareForCopy() JROOT/components/com_fabrik/models/form.php:1924
3 FabrikFEModelForm->processToDB() JROOT/components/com_fabrik/models/importcsv.php:915
4 FabrikFEModelImportcsv->insertData() JROOT/components/com_fabrik/controllers/import.php:115
5 FabrikControllerImport->doimport() JROOT/libraries/src/MVC/Controller/BaseController.php:730
6 Joomla\CMS\MVC\Controller\BaseController->execute() JROOT/components/com_fabrik/fabrik.php:200
7 require_once() JROOT/libraries/src/Dispatcher/LegacyComponentDispatcher.php:71
8 Joomla\CMS\Dispatcher\LegacyComponentDispatcher::Joomla\CMS\Dispatcher\{closure}() JROOT/libraries/src/Dispatcher/LegacyComponentDispatcher.php:73
9 Joomla\CMS\Dispatcher\LegacyComponentDispatcher->dispatch() JROOT/libraries/src/Component/ComponentHelper.php:361
10 Joomla\CMS\Component\ComponentHelper::renderComponent() JROOT/libraries/src/Application/SiteApplication.php:218
11 Joomla\CMS\Application\SiteApplication->dispatch() JROOT/libraries/src/Application/SiteApplication.php:261
12 Joomla\CMS\Application\SiteApplication->doExecute() JROOT/libraries/src/Application/CMSApplication.php:306
13 Joomla\CMS\Application\CMSApplication->execute() JROOT/includes/app.php:58
14 require_once() JROOT/index.php:32

I found this was due to the import code looking in $this->formData for "testjoinedtable___date_time" which does not exist.
I put in a code fix to mimick PHP7 behaviour on function count($x) where (in php8) $x must be countable around line 1798 of JROOT/components/com_fabrik/models/form.php

    $smallerElHTMLName = $tmpElement->getFullName(true, false);
    // mimick php 7 behaviour watch out for NULL or non-countable
    //$repeatGroup = count($this->formData[$smallerElHTMLName]);  // this is the line that bombs out
    $targetData = $this->formData[$smallerElHTMLName] ?? []; // set to empty array if null giving a count of 0
    $repeatGroup = is_countable($targetData) ? count($targetData) : 1; // set count to 1 if not countable

This allows the import to complete, however the subject field is not imported and all I get are empty rows.

I traced this down to the code path taken in JROOT/components/com_fabrik/models/importcsv.php when table joins are found. The rather brute force workaround I applied simply hard codes no table joins found.
Ie some code like this around line 800 of importcsv.php

    // so lets see if any of $joins are table joins.
    // HACK HACK, import with joins very broken so hard code no joins found       
    //$tableJoinsFound = $this->tableJoinsFound();
    $tableJoinsFound = false;

After these two fixes I can successfully import into the testbasetable table. Additionally if I extended the CSV file to include a column name from the testjoinedtable thus:
subject,category
sub1,"cat10,cat11"
sub2,"cat21,cat22"
sub3,cat31

and with some simple custom PHP to the listcsv plugin in the onAfterImportRow section to parse the categrory column in my example I can correctly import the joined data also.

I don't know if it just me and/or if I am doing something wrong on the structure of the CSV file I am importing or if no one is importing to lists with table joins.

These two fixes are working very well for me but ideally I would move away from having to reapply the fixes on every Fabrik update.

The custom code I added to the onAfterImportRow is below:

defined('_JEXEC') or die();
use Joomla\CMS\Factory;


$formModel = $listModel->getFormModel();
$db = Factory::getContainer()->get('DatabaseDriver');


$baseTableID = $formModel->formData['id_raw'];
$categories = explode(",",$formModel->formData['category']);


if( !empty($categories) )
{
    $values = array();
    $columns = array('base_table_id','category');
    foreach($categories as $category)
    {
        $values[] = implode(',',array($baseTableID, $db->quote($category)));
    }
    $query = $db->createQuery();
    $query
        ->insert($db->quoteName('testjoinedtable'))
        ->columns($db->quoteName($columns))
        ->values($values);
    $db->setQuery($query);
    $db->execute();   
}

Environment is
Fabrik 4.3.1
Joomla 5.2.0
PHP 8.2.24

Happy to test on 4.4.1 if it is felt this will help.