A hard SQL Query

Question

I have an unusual SQL question that I'm not quite sure how best to explain so please bear with me. I have three tables, one is of volunteer organisations and one is users and the other is of user details.

#Table 1#
## Name President Secretary Treasurer
----------
ABC 571 123 456
DEF 457 582 444

#Table 2#
## Id Name
----------
571 Joe Blogs
123 Joanne Blogs

#Table 3#
## Id Address City
----------
571 123...... Somewhere
123 456..... Somewhere Else

What I am trying to do is allow a user to assemble PDF address labels in the order of the organisation name, so they might say they want the presidents from every organisation or the presidents and secretaries so it will read the presidents and secretary ids for all the organisations and join them to the address details and assemble them in the order of the organisation. They may choose to do just presidents or presidents and secretaries or any combination of the above. It must sort in organisation order and return user objects in this order.

For example

ABC -> President
ABC -> Secretary
DEF -> President
DEF -> Secretary

I have tried a few options. The first is to,

$query  = $db->getQuery(true);
        $query->select($db->quoteName('a.name'));
        $query->select($db->quoteName($this->show_exec));
        $query->from($db->quoteName('#__agshows_shows', 'a'));
        $tableACount = 'b';
        $tableBCount = 'aa';
        foreach($this->show_exec as $column){
            $query->select($db->quoteName(array($tableACount . '.user_id', $tableACount . '.address_1', $tableACount . '.address_2', $tableACount . '.city', $tableACount . '.region', $tableACount . '.postal_code', $tableBCount . '.name')))
            ->join('LEFT', $db->quoteName('#__agshows_user_profile', $tableACount) . ' ON (' . $db->quoteName($tableACount . '.user_id') . ' = ' . $db->quoteName($column) . ')')
            ->join('LEFT', $db->quoteName('#__users', $tableBCount) . ' ON (' . $db->quoteName($tableBCount . '.id') . ' = ' . $db->quoteName($column) . ')');

            $tableACount ++;
            $tableBCount ++;
        }

        switch($this->show_sort) {

            case 'a.title':
                $query->order($db->quoteName('a.title') . ' ASC');
            break;

            case 'a.groupid':
                $query->order($db->quoteName('a.groupid') . ' ASC');
            break;

            default:
                $query->order($db->quoteName('a.title') . ' ASC');              
        }

        $db->setQuery($query);
        $show_users = $db->loadObjectList();

The problem with this is as it goes through the foreach loop the overwrite each other for each organisation. I want it to be flexible ($this->show_exec holds an array of columns to choose, i.e a.president, a.secretary).

The second option was,

if(!empty($this->show_exec)) {
        // Get the Show Exec user IDs.
        $query  = $db->getQuery(true);
        $query->select($db->quoteName($this->show_exec));
        $query->from($db->quoteName('#__agshows_shows'));
        $db->setQuery($query);
        $show_users = $db->loadAssocList();


        foreach ($show_users as $shows) {
            foreach ($shows as $key=>$value) {
                if($value != 0) {
                    $user_ids[]=$value;

                }
            }
        }
    }

$query = $db->getQuery(true);
    $query->select('a.*');
    $query->from($db->quoteName('#__agshows_user_profile') . ' AS a');

    // Join over the users names.
    $query->select('uc.name')
    ->join('LEFT', '#__users AS uc ON uc.id=a.user_id');

    $query->where('a.user_id IN ('. implode(',', $user_ids) . ')');

    $query->order($db->escape('a.city asc'));

    $db->setQuery($query);
    $recipients = $db->loadObjectList();

The problem with this one is that the $users_id is in the correct order but when it queries the second table it has no reference to sort by and just returns in the order of the second table not the order I want....

Is there a clean way to do it all in one?

Or is there a way I can sort the second query to return the results in the order of the $user_ids array?

I hope that makes sense..

Thanks

Answer 1

I think below query might work for you :

    SELECT 
  * 
FROM
  (SELECT 
    o.name 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.name 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.name 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 ;

I have assumed primary key of users table will be used as foreign key in president, secretary & treasurer columns in organization table.

Below query's output will produce one virtual column "type" which you can use in your code to determine role of that particular user in an organization. Also you need to create this query dynamically to take care of the situation when only president or secretary or treasurer or any combination of them is required.