You actually don't need to perform multiple joins with the same table to gather your desired results. What is best suited is a "pivot" technique. There is no cleaner way to deal with rsform's submission data.
Please note, while you may only have one "form" in your #__rsform_submissions table, it would be more robust to specify the FormId in the WHERE ...
Here is what I believe the mySQL query to be with (1,2,3) substituted in for your imploding of results because I wanted to check the mySql syntax of it. I don't have time to convert into PHP right now, but wanted to try to help. If it doesn't work, let me know in comments and I will try to adjust:SELECT a.FieldValue as Container, b.FieldValue as ...
To debug the issue: $query is an object that generates the SQL statement to be executed. Then, you can echo $query->__toString() and compare the outputs.
In a complex case, it may be necessary to generate sub-queries to create a final big query.
Yes, your queries can certainly use refactoring. Using two separate calls on the same table is asking MySQL to do needless extra work.
To convert your queries into plain English, you want to:
create a result set of containertype1 values sorted by SubmissionId
for each SubmissionId from FormId 28
including only submissions that contain a Status1 value of ...
If you are new, then I'd like to include some basic error checking as well as printing out the generated query and the 2-column resultset.
$db = JFactory::getDBO();
$query = $db->getQuery(true)
"MAX(CASE WHEN v.FieldName = 'Team_Name'
I think you are asking, Joomla code to execute query ?
$db = JFactory::getDBo();
$query = $db->getQuery(true);
$query->select("MAX(CASE WHEN v.FieldName=".$db->q('Team_Name')." THEN v.FieldValue END) as Team_Name, MAX(CASE WHEN v.FieldName = ".$db->q('Manager_Name')." THEN v.FieldValue END) as Manager_Name")->from('#__team_values as v'...
Here you are:
A few points:
You have really made a rod for your back by not "normalizing" your org table. My strong recommendation is to restructure your org table to have a type column versus three static columns -- this means tripling the number of rows in your table. While this new structure looks less condensed, it permits you to perform much more clean/direct ...
You have to create every query like you did ($q1,$q2,$q3,etc..) and make a final query like this:
->from("(".$q1->union($q2)->union($q3).") as tab")
It's well explained in this article: