UNION ALL only working on first two queries in UNION QUERY Topic is solved

[mailblade]

I have a UNION query which collects information from the database.

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

Now, in PHPMYADMIN the results are correct, I will attach an image to show the results.

tablesql.jpg

As you can see, all the values are retrieved correctly. It retrieved all the "portofloading" values where each of the different "Status" values are 'Pending' and SubmissionId is matching.

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 />"; 			
		     }		
		}	
?>

Now here's where the strange part comes in. If I only use two queries and 1 "unionAll" at the end of the query to union the queries

$query->unionAll($query2);

this is the result I get (I cut off some information from the table for privacy reasons):

polvalue.jpg

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.

[mailblade]

The main issue is that Joomla is hiding all the duplicate values. But I have used a longer and more tedious query to solve the issue, even though I didn't want to.

[mailblade]

Well the way I resolved it is the following:

I basically had to duplicate the query depending on the number of "portofloading" fields I have. This means 3 times. I tried literally everything.

I UNIONED and chained the query successfully with JDatabase. I tried using OR on the 'portofloading'. Tried using CASE WHEN, tried to somehow display the GROUP_CONCAT in rows where each value is listed in an individual row, even if it is a duplicate. I tried to use an IF using Javascript but also ran into some issues. Nothing worked.

So this was the only way I could achieve the result I required.

The first query uses 'Status1 - 5' for its values ; second query uses 'Status11 - Status15' ; third query 'Status21 - Status25'. As you can see, I did not UNION this. Each query is set and then displayed individually, though they are all in the same <td>.

If anyone's interested, here is the code:

Code: Select all

<?php 
try {
    $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 AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')")
                ->where(["b.FormId = 28", "b.FieldValue = 'Pending'", "b.FieldName IN ('Status1','Status2', 'Status3', 'Status4', 'Status5')"])
		->order("b.FieldName ASC");
		
    $db->setQuery($query);

     $result = $db->loadObjectList();
                foreach($result as $value) {
                   foreach($value as $key => $data) { 
                     echo $data."<br />"; 
                   }
                }

$query2 = $db->getQuery(true)
                ->select("a.FieldValue")
                ->from("#__rsform_submission_values a")
                ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')")
                ->where(["b.FormId = 28", "b.FieldValue = 'Pending'", "b.FieldName IN ('Status11', 'Status12', 'Status13', 'Status14', 'Status15')"])
		->order("b.FieldName ASC");
	
	$db->setQuery($query2);

        $result2 = $db->loadObjectList();
                foreach($result2 as $value2) {
                   foreach($value2 as $key => $data2) { 
                     echo $data2."<br />"; 
                    }
                }

$query3 = $db->getQuery(true)
                ->select("a.FieldValue")
                ->from("#__rsform_submission_values a")
                ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')")
                ->where(["b.FormId = 28", "b.FieldValue = 'Pending'", "b.FieldName IN ('Status21', 'Status22', 'Status23', 'Status24', 'Status25')"])
		->order("b.FieldName ASC");
		
	$db->setQuery($query3);
  
         $result3 = $db->loadObjectList();
                foreach($result3 as $value3) {
                   foreach($value3 as $key => $data3) { 
                     echo $data3."<br />"; 
                    }
                }
  }
?>

Technically, I know it's not a solution to the original question I posted, but it is literally the only way I can work around the issue. The main problem is that JDatabase syntax doesn't show duplicates. So if you have let's say 2 values split between 20 fields, it will only retrieve the 2 values and not any duplicates. If I have "test" as a value 10 times and it uses the SAME 'FieldName', but have a different SubmissonId, it will still only retrieve one value where "test" = true, ignoring the SubmissionId.

This is what happens when I UNION the above query using JDatabase union and chaining. I only get 2 results even though there should be 20. It doesn't show the value for each 'SubmissionId'. It's almost as if its grouping the values by default. Anyhow, this is how I solved the issue. I now have 20 values showing for the above query and they are all correct in relation to the other fields from the other queries for this table.

[mailblade]

Actually the above code is completely wrong! I copied the wrong piece of code :O

Here is the correct one using 'portofloading'. Apologies!

Code: Select all

<?php 
try {
    $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')"])
->order("b.FieldName ASC");
$db->setQuery($query);
 
$result = $db->loadObjectList();
foreach($result as $value) {
foreach($value as $key => $data) { 
echo $data."<br />"; 
}
}
 
$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')"])
->order("b.FieldName ASC");
$db->setQuery($query2);
 
   $result2 = $db->loadObjectList();
foreach($result2 as $value2) {
foreach($value2 as $key => $data2) { 
echo $data2."<br />"; 
}
}
 
$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')"])
->order("b.FieldName ASC");
   $db->setQuery($query3);
 
   $result3 = $db->loadObjectList();
foreach($result3 as $value3) {
foreach($value3 as $key => $data3) { 
echo $data3."<br />"; 
}
}
?>