SOLVED:Dependent database join element and/or other calcs hang when adding this calc element to form

[chrisyork]

I'm trying to find a way to show which items of a list have already been submitted. The idea is to reduce risk/prevent duplicate submissions

I wanted to filter the databasejoin element to exclude items that correspond to any already submitted by the user, but that did my head in and I came up with the idea of using a calc (with AJAX) to show the list of items... But it seems to interfere with other AJAX elements causing the spinner of doom once the first dropdown is selected (FA_Existing_Theme in the code below)...

tables: themes, activities, activities_feedback

activities has a foreign key from themes (and allows the cascading dbjoin elements)

My Calc code:

// Get a db connection in Joomla 4+.
$db = \Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
// Create a new query object.
$myQuery = $db->createQuery(true);

$myQuery
->select(array('activities.id','activities.activity_name', 'activities_feedback.FA_SubmittedBy AS SubmittedBy'))
->from('activities')
-> join('INNER', 'activities_feedback ON activities_feedback.FA_Existing_Activity = activities.id')
->where('activities.themeid = '. $db->quote('{activities_feedback___FA_Existing_Theme_raw} AND activities_feedback.FA_SubmittedBy = {$my->id}'));

// Assign the query to the db
$db->setQuery($myQuery);

// Load the results as an array of objects.
$rows = $db->loadObjectList();


$list = array();

foreach ($rows as $row)
{
$list[] = $row->activity_name . " " . $row->SubmittedBy;
}

return $list;​

If I remove the 'where' clause I can see that the join works as expected, so I'm guessing that might have a little something to do with it! But I'm stumped. And stupid.
Thanks

 

[troester]

e.g.
>where('activities.themeid = '. $db->quote('{activities_feedback___FA_Existing_Theme_raw}') . ' AND activities_feedback.FA_SubmittedBy = {$my->id}');

 

[chrisyork]

Thanks for the amendment. Like I said, stupid. I think I tried all manner of combinations, but failed to find this one.

So that's got the query to work, but it still seems to affect the AJAX databasejoin - spinner of doom if I change the theme (the first dropdown). So maybe there's more to my problem than a where clause?

 

[troester]

Check in your browser dev console for JS errors and in the Network tab for notices/warning/errors. Each of them will break the expected JSON response for the Ajax update (so it may be necessary to set J! system debug off and J! error reporting level to simple or none).

 

[chrisyork]

Thanks - Not sure if I'm looking at / for the right thing in the browser dev tools, but I get an error when I change the first dbjoin dropdown (and the spinner of doom begins.
It is an internal server error 500 on XHR Post (?), and when I look at the Response the error is:

o urldecode(): Argument#1 ($string) must be of type string, array given

I'm so far under the bonnet here I can see darkness shining out the far end! Not even sure where that comes from or refers to. I have switched all debugging and error reporting off before trying this...

I really don't know if I'm making any sense... but if you can understand any of that and have an inkling what might be wrong, I'd love to know!

 

[troester]

The network response will help:
Turn on J! System Debug and try again.
Then this response will contain the error stack.

But I assume it's your calc code.
It's returning an array ($list), try something like
return implode(','$list);

 

[chrisyork]

Thanks. Again.

It seems to be this calc that is causing the problem. I have updated it to use the implode function and it now works. My other calc already used the implode (which you provide as an example in the Wiki).

Really appreciate you hunting this one down. So that's double solved!