How to refactor two select queries on RSForm submission data?

Question

I was wondering if anyone could help me JOIN multiple queries into one query. Currently, these 2 blocks of code retrieve information from the DB. I am using the "Sourcerer" plugin to add custom PHP code in my custom module.

However, the full query is much too long, and I need to reduce the size considerably. After a certain amount of queries, the page just doesn't load at all.

I will post two queries I need to have joined as an example.

<?php 
$db = JFactory::getDbo();
$db->getQuery(true);

$db->setQuery("SELECT `SubmissionId`, `FieldValue`, `FormId` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Status1\" AND `FieldValue` = \"Pending\" AND `FormId` = 28");
$results = $db->loadColumn();

$db->getQuery(true);
$db->setQuery("SELECT `FieldValue` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Container1\" AND `SubmissionId` IN ('".implode("', '", $results)."') ORDER BY `jos_rsform_submission_values`.`SubmissionId`"); 

$result = $db->loadObjectList();

foreach($result as $value) {
    foreach($value as $key => $data) { 
        echo $data."<br />"; 
    }
}
?>

Answer 1

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 ContainerType FROM ( SELECT FieldValue FROM jos_rsform_submission_values WHERE FieldName = "Container1" AND SubmissionId IN (1,2,3)) as a LEFT JOIN ( SELECT FieldValue FROM jos_rsform_submission_values WHERE FieldName = "containertype1" AND SubmissionId IN (1,2,3)) as b on a.SubmissionId = b.SubmissionId ORDER BY a.SubmissionId;

I had to remove the tick marks to get StackExchange to format correctly, but I would suggest converting your code to use $db->quote and $dbquotename instead anyway: https://docs.joomla.org/Selecting_data_using_JDatabase
https://docs.joomla.org/Secure_coding_guidelines

Answer 2

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 Pending.

The cleanest, most direct way to perform this task is with a "pivot" technique.

Raw Query (untested):

SELECT
    MAX(CASE WHEN FieldName = 'containertype1' THEN FieldValue ELSE NULL END)
FROM `#__rsform_submission_values`
WHERE FormId = 28
GROUP BY SubmissionId
HAVING MAX(CASE WHEN FieldName = 'Status1' THEN FieldValue ELSE NULL END) = 'Pending'
ORDER BY SubmissionId

GROUP BY creates "aggregate data" for each unique SubmissionId. Extracting specific details from within this collection of consolidated rows requires specialized calls (e.g.MAX(CASE...)).

The HAVING clause requires SubmissionIds to contain a row with a FieldName of Status1 and a FieldValue value of Pending. This relies on the fact that your submission_values table does not allow a SubmissionId to have 2 or more Status1 rows.

For every qualifying SubmissionId, the result set will provide the value that is stored with containertype1. If the qualifying SubmissionId does not have a containertype1 row, a NULL value will be provided as the default (you could change NULL to No Container Type if you wanted).

PHP/Joomla Syntax (untested):

$query = $db->getQuery(true)
    ->select("MAX(CASE WHEN FieldName = 'containertype1' THEN FieldValue ELSE NULL END)")
    ->from("#__rsform_submission_values")
    ->where("FormId = 28")
    ->group("SubmissionId")
    ->having("MAX(CASE WHEN FieldName = " . $db->q("status") . " THEN FieldValue ELSE NULL END) = " . $db->q("pending"));

// echo $query->dump();  // uncomment if you want to confirm the rendered query
try {
    $db->setQuery($query);
    echo "<pre>";
    var_dump($db->loadColumn());
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public
}

If you would like to see this technique in other contexts, here are some other pivot solutions that I have posted:

• https://joomla.stackexchange.com/a/23921/12352
• https://joomla.stackexchange.com/a/24320/12352