2

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\'' );
0

2 Answers 2

2

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.