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 clause. (I'll arbrarily use 1
in my snippet to follow.)
The Raw Query that I suggest is:
SELECT
a.SubmissionId,
a.DateSubmitted,
a.status,
MAX(CASE WHEN b.FieldName = 'name' THEN b.FieldValue ELSE NULL END) AS name,
MAX(CASE WHEN b.FieldName = 'email' THEN b.FieldValue ELSE NULL END) AS email
FROM `#__rsform_submissions` a
INNER JOIN `#__rsform_submission_values` b ON a.SubmissionId = b.SubmissionId
WHERE a.FormId = 1
GROUP BY a.SubmissionId
HAVING MAX(CASE WHEN b.FieldName = 'status' THEN b.FieldValue ELSE NULL END) = 'open'
The above joins the two related tables (just once), then groups the submission values based on the SubmissionId
. The GROUP BY clause creates "aggregate" data (a cloud of related data -- if you will) which requires special handling to extract desired values. The MAX(CASE...)
syntax allows you to pull out specific values. The HAVING clause checks for submissions which have a status
value of open
; the SELECT clause pulls the name
and email
to be provided in the result set.
Joomla/PHP code:
$query = $db->getQuery(true)
->select([
"a.SubmissionId",
"a.DateSubmitted",
"a.status",
"MAX(CASE WHEN b.FieldName = 'name' THEN b.FieldValue ELSE NULL END) AS name",
"MAX(CASE WHEN b.FieldName = 'email' THEN b.FieldValue ELSE NULL END) AS email"
])
->from("#__rsform_submissions a")
->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
->where("a.FormId = 1")
->group("a.SubmissionId")
->having("MAX(CASE WHEN b.FieldName = " . $db->q("status") . " THEN b.FieldValue ELSE NULL END) = " . $db->q("open"));
// echo $query->dump(); // uncomment if you want to confirm the rendered query
try {
$db->setQuery($query);
echo "<pre>";
var_dump($db->loadAssocList());
} catch (Exception $e) {
JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error'); // never show getMessage() to public
}
Test Schema:
CREATE TABLE IF NOT EXISTS `#__rsform_submissions` (
`SubmissionId` int(11) NOT NULL,
`FormId` int(11) NOT NULL DEFAULT '0',
`DateSubmitted` datetime NOT NULL,
`UserIp` varchar(15) NOT NULL DEFAULT '',
`Username` varchar(255) NOT NULL DEFAULT '',
`UserId` text NOT NULL,
`Lang` varchar(255) NOT NULL,
`status` tinyint(1) NOT NULL
) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO `#__rsform_submissions` (`SubmissionId`, `FormId`, `DateSubmitted`, `UserIp`, `Username`, `UserId`, `Lang`, `status`) VALUES
(1, 1, '2011-01-01 01:11:11', '60.241.244.9', 'Cam', '0', 'en-GB', 1),
(2, 1, '2012-02-02 02:22:22', '203.59.245.124', 'Pam', '0', 'en-GB', 1),
(3, 1, '2013-03-03 03:33:33', '101.172.255.225', 'Sam', '0', 'en-GB', 1),
(4, 1, '2014-04-04 04:44:44', '55.79.10.25', 'Tam', '0', 'en-GB', 1);
CREATE TABLE IF NOT EXISTS `#__rsform_submission_values` (
`SubmissionValueId` int(11) NOT NULL AUTO_INCREMENT,
`FormId` int(11) NOT NULL,
`SubmissionId` int(11) NOT NULL DEFAULT '0',
`FieldName` text NOT NULL,
`FieldValue` text NOT NULL,
PRIMARY KEY (`SubmissionValueId`),
KEY `FormId` (`FormId`),
KEY `SubmissionId` (`SubmissionId`)
) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO `#__rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
(1, 1, 1, 'name', 'Cam'),
(2, 1, 1, 'email', '[email protected]'),
(3, 1, 1, 'status', 'open'),
(4, 1, 2, 'name', 'Pam'),
(5, 1, 2, 'email', '[email protected]'),
(6, 1, 2, 'status', 'closed'),
(7, 1, 3, 'name', 'Sam'),
(8, 1, 3, 'email', '[email protected]'),
(9, 1, 3, 'status', 'open'),
(10, 1, 4, 'name', 'Tam'),
(11, 1, 4, 'email', '[email protected]'),
(12, 1, 4, 'status', 'open');
Output:
array(3) {
[0]=>
array(5) {
["SubmissionId"]=>
string(1) "1"
["DateSubmitted"]=>
string(19) "2011-01-01 01:11:11"
["status"]=>
string(1) "1"
["name"]=>
string(3) "Cam"
["email"]=>
string(13) "[email protected]"
}
[1]=>
array(5) {
["SubmissionId"]=>
string(1) "3"
["DateSubmitted"]=>
string(19) "2013-03-03 03:33:33"
["status"]=>
string(1) "1"
["name"]=>
string(3) "Sam"
["email"]=>
string(13) "[email protected]"
}
[2]=>
array(5) {
["SubmissionId"]=>
string(1) "4"
["DateSubmitted"]=>
string(19) "2014-04-04 04:44:44"
["status"]=>
string(1) "1"
["name"]=>
string(3) "Tam"
["email"]=>
string(13) "[email protected]"
}
}