· 7 years ago · Feb 27, 2019, 01:12 PM
1$query
2 ->select('a.SubmissionId, a.DateSubmitted, a.status, b.SubmissionId, b.FieldName, b.FieldValue')
3 ->from($db->quoteName('#__rsform_submissions', 'a'))
4 ->join('INNER', $db->quoteName('#__rsform_submission_values', 'b') . ' ON (' . $db->quoteName('a.SubmissionId') . ' = ' . $db->quoteName('b.SubmissionId') . ')')
5 ->where($db->quoteName('b.FieldName') . ' = 'status' AND ' . $db->quoteName('b.FieldValue') . ' = 'open'' );
6
7$query
8 ->select(array('a.SubmissionId, a.DateSubmitted, a.status, b.SubmissionId, b.FieldName, b.FieldValue'))
9 ->from($db->qn('#__rsform_submissions', 'a'))
10 ->join('INNER', $db->qn('#__rsform_submission_values', 'b') . ' ON (' . $db->qn('a.SubmissionId') . ' = ' . $db->qn('b.SubmissionId') . ')')
11 ->where($db->qn('b.FieldValue') . ' = ' . $db->qn('open'));
12
13$query->select($db->qn('s.SubmissionId','id'))
14 ->select($db->qn('s.DateSubmitted','date'))
15 ->select($db->qn('sv.FieldValue','status'))
16 ->select($db->qn('nv.FieldValue','name'))
17 ->select($db->qn('ev.FieldValue','email'))
18 ->from($db->qn('#__rsform_submissions','s'))
19 ->leftJoin($db->qn('#__rsform_submission_values','sv').' ON '.$db->qn('sv.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('sv.FieldName').' = '.$db->q('status'))
20 ->leftJoin($db->qn('#__rsform_submission_values','nv').' ON '.$db->qn('nv.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('nv.FieldName').' = '.$db->q('name'))
21 ->leftJoin($db->qn('#__rsform_submission_values','ev').' ON '.$db->qn('ev.SubmissionId').' = '.$db->qn('s.SubmissionId').' AND '.$db->qn('ev.FieldName').' = '.$db->q('email'))
22 ->where($db->qn('sv.FieldValue').' = '.$db->q('open'));
23
24SELECT
25 a.SubmissionId,
26 a.DateSubmitted,
27 a.status,
28 MAX(CASE WHEN b.FieldName = 'name' THEN b.FieldValue ELSE NULL END) AS name,
29 MAX(CASE WHEN b.FieldName = 'email' THEN b.FieldValue ELSE NULL END) AS email
30FROM `#__rsform_submissions` a
31INNER JOIN `#__rsform_submission_values` b ON a.SubmissionId = b.SubmissionId
32WHERE a.FormId = 1
33GROUP BY a.SubmissionId
34HAVING MAX(CASE WHEN b.FieldName = 'status' THEN b.FieldValue ELSE NULL END) = 'open'
35
36$query = $db->getQuery(true)
37 ->select([
38 "a.SubmissionId",
39 "a.DateSubmitted",
40 "a.status",
41 "MAX(CASE WHEN b.FieldName = 'name' THEN b.FieldValue ELSE NULL END) AS name",
42 "MAX(CASE WHEN b.FieldName = 'email' THEN b.FieldValue ELSE NULL END) AS email"
43 ])
44 ->from("#__rsform_submissions a")
45 ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
46 ->where("a.FormId = 1")
47 ->group("a.SubmissionId")
48 ->having("MAX(CASE WHEN b.FieldName = " . $db->q("status") . " THEN b.FieldValue ELSE NULL END) = " . $db->q("open"));
49 // echo $query->dump(); // uncomment if you want to confirm the rendered query
50 try {
51 $db->setQuery($query);
52 echo "<pre>";
53 var_dump($db->loadAssocList());
54 } catch (Exception $e) {
55 JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error'); // never show getMessage() to public
56 }
57
58CREATE TABLE IF NOT EXISTS `#__rsform_submissions` (
59 `SubmissionId` int(11) NOT NULL,
60 `FormId` int(11) NOT NULL DEFAULT '0',
61 `DateSubmitted` datetime NOT NULL,
62 `UserIp` varchar(15) NOT NULL DEFAULT '',
63 `Username` varchar(255) NOT NULL DEFAULT '',
64 `UserId` text NOT NULL,
65 `Lang` varchar(255) NOT NULL,
66 `status` tinyint(1) NOT NULL
67) ENGINE=InnoDB CHARSET=utf8;
68
69INSERT INTO `#__rsform_submissions` (`SubmissionId`, `FormId`, `DateSubmitted`, `UserIp`, `Username`, `UserId`, `Lang`, `status`) VALUES
70(1, 1, '2011-01-01 01:11:11', '60.241.244.9', 'Cam', '0', 'en-GB', 1),
71(2, 1, '2012-02-02 02:22:22', '203.59.245.124', 'Pam', '0', 'en-GB', 1),
72(3, 1, '2013-03-03 03:33:33', '101.172.255.225', 'Sam', '0', 'en-GB', 1),
73(4, 1, '2014-04-04 04:44:44', '55.79.10.25', 'Tam', '0', 'en-GB', 1);
74
75CREATE TABLE IF NOT EXISTS `#__rsform_submission_values` (
76 `SubmissionValueId` int(11) NOT NULL AUTO_INCREMENT,
77 `FormId` int(11) NOT NULL,
78 `SubmissionId` int(11) NOT NULL DEFAULT '0',
79 `FieldName` text NOT NULL,
80 `FieldValue` text NOT NULL,
81 PRIMARY KEY (`SubmissionValueId`),
82 KEY `FormId` (`FormId`),
83 KEY `SubmissionId` (`SubmissionId`)
84) ENGINE=InnoDB CHARSET=utf8;
85
86INSERT INTO `#__rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
87(1, 1, 1, 'name', 'Cam'),
88(2, 1, 1, 'email', 'cam@email.com'),
89(3, 1, 1, 'status', 'open'),
90(4, 1, 2, 'name', 'Pam'),
91(5, 1, 2, 'email', 'pam@email.net'),
92(6, 1, 2, 'status', 'closed'),
93(7, 1, 3, 'name', 'Sam'),
94(8, 1, 3, 'email', 'sam@email.org'),
95(9, 1, 3, 'status', 'open'),
96(10, 1, 4, 'name', 'Tam'),
97(11, 1, 4, 'email', 'tam@email.biz'),
98(12, 1, 4, 'status', 'open');
99
100array(3) {
101 [0]=>
102 array(5) {
103 ["SubmissionId"]=>
104 string(1) "1"
105 ["DateSubmitted"]=>
106 string(19) "2011-01-01 01:11:11"
107 ["status"]=>
108 string(1) "1"
109 ["name"]=>
110 string(3) "Cam"
111 ["email"]=>
112 string(13) "cam@email.com"
113 }
114 [1]=>
115 array(5) {
116 ["SubmissionId"]=>
117 string(1) "3"
118 ["DateSubmitted"]=>
119 string(19) "2013-03-03 03:33:33"
120 ["status"]=>
121 string(1) "1"
122 ["name"]=>
123 string(3) "Sam"
124 ["email"]=>
125 string(13) "sam@email.org"
126 }
127 [2]=>
128 array(5) {
129 ["SubmissionId"]=>
130 string(1) "4"
131 ["DateSubmitted"]=>
132 string(19) "2014-04-04 04:44:44"
133 ["status"]=>
134 string(1) "1"
135 ["name"]=>
136 string(3) "Tam"
137 ["email"]=>
138 string(13) "tam@email.biz"
139 }
140}