Cascading drop-down third level dependency on the first two selections

[molcomp]

I have 4 tables. One table projects, second activities, third subactivity and fourth table named act_subact containing the ids and their relations.

I have created a list named comparison with 3 fields actually 5 but the other 2 are just text fields.

First field projects with dropdown field from table projects.

Second field cascading field name activity from 4th relational table act_subact with concat
(SELECT activity_name FROM activities WHERE id = act_subact.activity_name) to show the actual name of the field.
watch element is set Project name from comparioson (current list) and foreign key is project name from act_subact
relationship table.

Third field is also cascading field named subactivity from 4th relational table act_subact ( I have removed concat at the moment till I resolve the core issue itself.
watch element is set to activity name from comparison (current list) and foreign key is activity name from act_subact relationship table.
Basically is the same setup as second field.

The issue is the following:
First field shows fine the list of the project names in the dropdown.
Second field activity populates fine when some project is selected in the first field.
Third field does not populate its empty when second field is selected.

What I need is to display the third field based on the previous two selections. In the above setup third field watches only activity field which might occur multiple times in the relationship table and cannot produce proper display.
I tried few things but non of them worked.

I believe the proper solution is to add the following in advanced under query clause but I am missing something since its not working.
comparison.project_name=act_subact.project_name
comparison is the current table containing the drop-down fields and act_subact is the relationship table.


Any advise or suggestion is much appreciated.
Thank you

 

[lori19]

Hi there

I usually try to avoid more then one level with cascadingdropdown, I always struggle with the configuration. I rather use a calc field set to AJAX update. This calc field would then contain a full database query that uses the value of the one-level-up field in the where clause.

Speaking of these two element plugins: Has anybody experience how they behave speedwise? Having a shaky internet connection, I observed that the cascading dropdown seems to be more likely to hang for some seconds (spinner) than the calc elemnt.

 

[molcomp]

Thank you lori19 for the help. Can you please tell me do you something like this code from wiki for the calc field ?

// Get a db connection.

$myDb = Factory::getContainer()->get('DatabaseDriver');



// Create a new query object.

$myQuery = $myDb->getQuery(true);



$myQuery

->select(array('fieldA', 'fieldB'));

->from('tablename')

->where('fieldC = ' . $myDb->quote('value'));



// Assign the query to the db

$myDb->setQuery($myQuery);



// Load the results as an array of objects.

$rows = $myDb->loadObjectList();

Thank you again
Alex

 

[molcomp]

Hi,

I am kind of in between solution but I am stuck on where clause. I am missing something but I am not sure how to resolve it. Here is my query:

->select(array('subactivity.subactivity_name'))
->from('subactivity')
->join('INNER', 'intemgroup1 ON intemgroup1.subactivity_name=subactivity.id')
->join('INNER', 'projects ON projects.id=intemgroup1.project_name=projects.id')
->join('INNER', 'activities ON activities.id=intemgroup1.activity_name')
->where(
'intemgroup1.project_name = ' . $myDb->quote('{overlapping.project_1}'));

The problem is in the where statement at least I think its there. Element intemgroup1.project_name is an ID field while element overlapping.project_1 is name string that is retrieved via concat dropdown field. The overlapping.project_1 element should be the dropdown option of the list so I am not sure if this is the correct syntax to use for it as well.

Any thoughts?
Alex

 

[lori19]

Hi there

Yes, that is what I was talking about.

For your where problem: Add a print_r after you build your query to doublecheck what the second value actually contains. Before loading the results add the line:

echo '<pre>'.print_r($myQuery);exit;

Don't forget to remove this line once you have sorted out your query

Kindly,
Lorenz

 

[molcomp]

Hello Lorenz ,

Thank you very much for you help. In the echo command I get error 500 . I am using joomla 4 and fabrik 4 maybe there is different syntax for it ?

Thank you
Alex

 

[molcomp]

Finally got it working the cascaded list , thank you Lorenz. Just need to figure out now how to display the data in a dropdown menu rather then plain text list , hopefully will be easier.

Alex

P.S here is the outcome of the calc field on a list with one dd, one cdd and one calc field.

// Get a db connection.

$myDb = \Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');

// Create a new query object.

$myQuery = $myDb->getQuery(true);

$myQuery
->select(array('subactivity.subactivity_name'))
->from('subactivity')
->join('INNER', 'intemgroup1 ON intemgroup1.subactivity_name=subactivity.id')
->join('INNER', 'projects ON projects.id=intemgroup1.project_name')
->join('INNER', 'activities ON activities.id=intemgroup1.activity_name')
->where('projects.project_name = ' . $myDb->quote('{overlapping___project_1}') )
->where('activities.activity_name = ' . $myDb->quote('{overlapping___activity}'));



// Assign the query to the db

$myDb->setQuery($myQuery);


// Load the results as an array of objects.

$rows = $myDb->loadObjectList();
$list = array();
foreach ($rows as $row)

{
$list[] = "<li>" . $row->subactivity_name . "</li>";
}

return "<ol>" . implode($list) . "</ol>";