Joomla 3.3.6 Union SQL Issue

Question

I am having trouble using the union functions in Joomla. This is the SQL I need:

SELECT 
 * 
    FROM
  (SELECT 
    o.title AS org_name,
    u1.name AS username,
    'president' AS `type`
  FROM
    org o 
    LEFT JOIN users u1 
      ON u1.`id` = o.`president` 
  UNION
  ALL 
  SELECT 
    o.title AS org_name,
    u1.name AS username,
    'secretary' AS `type`
  FROM
    org o 
    LEFT JOIN users u1 
      ON u1.`id` = o.`secretary` 
  UNION
  ALL 
  SELECT 
    o.title AS org_name,
    u1.name AS username,
    'treasurer' AS `type`
  FROM
    org o 
    LEFT JOIN users u1 
      ON u1.`id` = o.`treasurer`) AS a 
ORDER BY a.org_name ;

This is my Joomla code:

    $q2 = $db->getQuery(true);
    $q1 = $db->getQuery(true);
    $query  = $db->getQuery(true);

    $q2
    ->select('o.title AS show_name, u1.name AS name,' . $db->quoteName('president') . 'as' . $db->quoteName('type'))
    ->from($db->quoteName('#__agshows_shows', 'o'))
    ->join('LEFT', $db->quoteName('#__users', 'u1') . ' ON (' . $db->quoteName('u1.id') . ' = ' . $db->quoteName('o.president') . ')')      
    ;
    $q1
    ->select('o.title AS show_name, u1.name AS name')
    ->from($db->quoteName('#__agshows_shows', 'o'))
    ->join('LEFT', $db->quoteName('#__users', 'u1') . ' ON (' . $db->quoteName('u1.id') . ' = ' . $db->quoteName('o.secretary') . ')')      
    ;
    $query
    ->select('*')
    ->from($db->quoteName('#__agshows_shows', 'a'))     
    ->union($q1)
    ->union($q2)
    ->order($db->quoteName('a.title') . ' ASC')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

I keep getting this SQL Error:

1221 Incorrect usage of UNION and ORDER BY SQL=SELECT * FROM ruq1h_agshows_shows AS a ORDER BY a.title ASC UNION ( SELECT o.title AS show_name, u1.name AS name FROM ruq1h_agshows_shows AS o LEFT JOIN ruq1h_users AS u1 ON (u1.id = o.secretary)) UNION ( SELECT o.title AS show_name, u1.name AS name,presidentastype FROM ruq1h_agshows_shows AS o LEFT JOIN ruq1h_users AS u1 ON (u1.id = o.president))

What am I doing wrong?

Answer 1

To debug the issue: $query is an object that generates the SQL statement to be executed. Then, you can echo $query->__toString() and compare the outputs.

In a complex case, it may be necessary to generate sub-queries to create a final big query.

Answer 2

You have to create every query like you did ($q1,$q2,$q3,etc..) and make a final query like this:

$query->select("tab.*")
      ->from("(".$q1->union($q2)->union($q3).") as tab")
      ->order("tab.orderingField");

It's well explained in this article: https://docs.joomla.org/Using_the_union_methods_in_database_queries#Ordering_results

Answer 3

A few points:

1. You have really made a rod for your back by not "normalizing" your org table. My strong recommendation is to restructure your org table to have a type column versus three static columns -- this means tripling the number of rows in your table. While this new structure looks less condensed, it permits you to perform much more clean/direct queries on your data.

2. If you KNOW that no user will ever hold more than one position (in any organization) AND you don't want to see any vacant positions or unseated users, then you can use:

   SELECT
   b.title AS org_name,
   a.name AS username,
   MAX(CASE a.id
       WHEN president THEN 'president'
       WHEN secretary THEN 'secretary'
       WHEN treasurer THEN 'treasurer'
       ELSE NULL END) AS type
   FROM `users` a
   INNER JOIN `org` b ON a.id IN (b.president, b.secretary, b.treasurer)
   GROUP BY a.id
   

3. Otherwise, to craft a MySQL query that will accommodate users holding multiple seats, the following query is a bit more convoluted with the UNION ALL clauses, but slightly cleaner than your posted attempt.

   SELECT o.title AS org_name, u.name AS username, 'president' AS `type`
   FROM org o
   LEFT JOIN users u ON u.id = o.president
   UNION (
       SELECT o.title, u.name, 'secretary'
       FROM org o 
       LEFT JOIN users u ON u.id = o.secretary
   )
   UNION (
       SELECT o.title, u.name, 'treasurer'
       FROM org o 
       LEFT JOIN users u ON u.id = o.treasurer
   )
   ORDER BY org_name, type
   

   Notes:

   • The SELECT columns only need to be expressed in the first SELECT (the columns are implied with subsequent unions).
   • If you do not wish to include empty seats in your result set (where username is NULL for a given seat), then replace all of the LEFT JOINs with INNER JOINs.

PHP/Joomla Syntax for query with UNION ALLs:

$db = JFactory::getDBO();
$query = $db->getQuery(true)
    ->select([
        "o.title AS org_name",
        "u.name AS username",
        $db->q("president") . " AS `type`"
    ])
    ->from('org o')
    ->innerJoin("users u on u.id = o.president")
    ->order("org_name, type");

$types = ['secretary', 'treasurer'];
foreach ($types as $type) {
    $query->union($db->getQuery(true)
                     ->select("o.title, u.name, " . $db->q($type))
                     ->from("org o")
                     ->innerJoin("users u ON u.id = " . $db->qn("o.{$type}"))
    );
}

$db->setQuery($query);
echo $query->dump();  // see the generated query
try {
    if (!$result = $db->loadAssocList()) {
        echo "No Qualifying Rows";
    } else {
        echo "<table>";
            echo "<tr><th>", implode("</th><th>", array_keys($result[0])), "</th></tr>";
            foreach ($result as $row) {
                echo "<tr><td>", implode("</td><td>", $row), "</td></tr>";
            }
        echo "</table>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public
}

Test Schemas with SQL Demo

Output from sample data:

SELECT o.title AS org_name,u.name AS username,'president' AS `type`
FROM org o
INNER JOIN users u on u.id = o.president
UNION (
SELECT o.title, u.name, 'secretary'
FROM org o
INNER JOIN users u ON u.id = `o`.`secretary`)
UNION (
SELECT o.title, u.name, 'treasurer'
FROM org o
INNER JOIN users u ON u.id = `o`.`treasurer`)
ORDER BY org_name, type

| org_name | username               | type      |
| -------- | ---------------------- | --------- |
| ACA Qld  | Majella Fitzsimmons    | president |
| ACA Qld  | Debra North            | secretary |
| ACA Qld  | Doug Burns             | treasurer |
| BMWMCQ   | Paul Hughes            | president |
| BMWMCQ   | Duncan Bennett         | secretary |
| BMWMCQ   | John Eacott            | treasurer |
| Joomla   | Rowan Hoskyns Abrahall | president |
| Joomla   | Luca Marzo             | secretary |
| Joomla   | Jason Rosenbaum        | treasurer |