How to retrieve RSForm submission data grouped by SubmissionId?

Question

I have a table with 3 columns: SubmissionId, FieldName and FieldValue.

1) SubmissionId keeps ID submission.

2) FieldName stores field name of a form.

3) FieldValue stores field value of form field.

I have a form with 3 fields: name, email and status. I have four submissions, in total there are 12 entries: 3 per submission.

How do I get 3 records of a submission where the status field of that submission is equal to the string "open"?

Is it possible? I the query to follow, but it evidently only retrieves the record containing the "status" field. I need the other 2 fields.

UPDATED:

$query
    ->select('a.SubmissionId, a.DateSubmitted, a.status, b.SubmissionId, b.FieldName, b.FieldValue')
    ->from($db->quoteName('#__rsform_submissions', 'a'))
    ->join('INNER', $db->quoteName('#__rsform_submission_values', 'b') . ' ON (' . $db->quoteName('a.SubmissionId') . ' = ' . $db->quoteName('b.SubmissionId') . ')')
    ->where($db->quoteName('b.FieldName') . ' = \'status\' AND ' . $db->quoteName('b.FieldValue') . ' = \'open\'' );

Answer 1

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]"
  }
}

Answer 2

Here you are:

$query->select($db->qn('s.SubmissionId','id'))
        ->select($db->qn('s.DateSubmitted','date'))
        ->select($db->qn('sv.FieldValue','status'))
        ->select($db->qn('nv.FieldValue','name'))
        ->select($db->qn('ev.FieldValue','email'))
        ->from($db->qn('#__rsform_submissions','s'))
        ->leftJoin($db->qn('#__rsform_submission_values','sv').' ON '.$db->qn('sv.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('sv.FieldName').' = '.$db->q('status'))
        ->leftJoin($db->qn('#__rsform_submission_values','nv').' ON '.$db->qn('nv.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('nv.FieldName').' = '.$db->q('name'))
        ->leftJoin($db->qn('#__rsform_submission_values','ev').' ON '.$db->qn('ev.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('ev.FieldName').' = '.$db->q('email'))
        ->where($db->qn('sv.FieldValue').' = '.$db->q('open'));

As output you'll have row with data you need. I believe the code is easy enough to figure out each line.