QUERY:
Code: Select all
SELECT a.SubmissionValueId, a.SubmissionId, a.FieldName, a.FieldValue
FROM jos_rsform_submission_values a
INNER JOIN
(SELECT SubmissionId, FieldName
FROM jos_rsform_submission_values b
WHERE FieldName IN ('Status1', 'Status2', 'Status3', 'Status4', 'Status5')
AND FieldValue = 'Pending'
AND FormId = 28
) AS test
ON a.SubmissionId = test.SubmissionId
WHERE a.FieldName = 'portofloading1' AND a.FormId = 28
UNION
SELECT a.SubmissionValueId, a.SubmissionId, a.FieldName, a.FieldValue
FROM jos_rsform_submission_values a
INNER JOIN
(SELECT SubmissionId, FieldName
FROM jos_rsform_submission_values b
WHERE FieldName IN ('Status11', 'Status12', 'Status13', 'Status14', 'Status15')
AND FieldValue = 'Pending'
AND FormId = 28
) AS test
ON a.SubmissionId = test.SubmissionId
WHERE a.FieldName = 'portofloading2' AND a.FormId = 28
UNION
SELECT a.SubmissionValueId, a.SubmissionId, a.FieldName, a.FieldValue
FROM jos_rsform_submission_values a
INNER JOIN
(SELECT SubmissionId, FieldName
FROM jos_rsform_submission_values b
WHERE FieldName IN ('Status21', 'Status22', 'Status23', 'Status24', 'Status25')
AND FieldValue = 'Pending'
AND FormId = 28
) AS test
ON a.SubmissionId = test.SubmissionId
WHERE a.FieldName = 'portofloading3' AND a.FormId = 28
I then wrote a query using JDatabase and UNION. I will post the code below:
Code: Select all
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true)
->select("a.FieldValue")
->from("#__rsform_submission_values a")
->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
->where(["a.FieldName = 'portofloading1'", "a.FormId = 28", "a.FieldValue != ''", "b.FormId = 28", "b.FieldValue = 'Pending'", "b.FieldName IN ('Status1', 'Status2', 'Status3', 'Status4', 'Status5')"]);
$query2 = $db->getQuery(true)
->select("a.FieldValue")
->from("#__rsform_submission_values a")
->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
->where(["a.FieldName = 'portofloading2'", "a.FormId = 28", "a.FieldValue != ''", "b.FormId = 28", "b.FieldValue = 'Pending'", "b.FieldName IN ('Status11', 'Status12', 'Status13', 'Status14', 'Status15')"]);
$query3 = $db->getQuery(true)
->select("a.FieldValue")
->from("#__rsform_submission_values a")
->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
->where(["a.FieldName = 'portofloading3'", "a.FormId = 28", "a.FieldValue != ''", "b.FormId = 28", "b.FieldValue = 'Pending'", "b.FieldName IN ('Status21', 'Status22', 'Status23', 'Status24', 'Status25')"]);
$query->unionAll($query2)->unionAll($query3);
$result = $db->setQuery($query)->loadObjectList();
foreach($result as $value) {
foreach($value as $key => $data) {
echo $data."<br />";
}
}
?>
this is the result I get (I cut off some information from the table for privacy reasons):$query->unionAll($query2);
If I write down all 3 queries and use 2 "unionAll" methods I get the exact same result as in the image posted above, even though I have 3 queries and using 2 unionAll, it is only counting the first unionAll
Logically, we know from the SQL query table image that 'portofloading1', 'portofloading2' AND 'portofloading3' all have a value in the query. This means the query in Joomla is not displaying the correct amount of records.
Any help is appreciated.