Query creation with Union ALL to join tables with indexing by table name

Question

There’s a problem that they did in a database, which I need to solve temporarily, they created several tables with index in the table name, where the system creates a new table for each new user... (This was done badly), probably the developer created this new problem, to meet a problem of slowness, and probably he has never heard of table indexes, however, I have to make a temporary solution, to subsequently correct the database. So I’m trying to solve this through code, it occurs that I’m having difficulty to implement a query, I’m using Zend Framework 1.12.20.

This is Dao’s Model:

class Application_Model_Dao_DataSchoolMembers
{

    private $dbTable;

    public function __construct()
    {
        $this->dbTable = new Application_Model_DbTable_SchoolsMembers();
        $this->dbTable->getDefaultAdapter()->setFetchMode(Zend_Db::FETCH_OBJ);
    } 

    public function getDataTableMembers()
        {
            $selectTopTables = $this->dbTable->select()
                ->setIntegrityCheck(false);
            $selectTopTables->from(array('tb' => 'information_schema.tables'),
                array(
                    'first_number' => 'CONVERT(REPLACE(REPLACE(table_name, "School_",""),"_members",""), UNSIGNED)'
                ));

            $selectTopTables->where('tb.table_name LIKE ? ','%School_%');
            $selectTopTables->where('tb.table_name LIKE ? ','%_members%');
            $selectTopTables->order(array('first_number ASC'));
            $v= $selectTopTables->assemble();
            print_r($v);
            $data = $this->getAllDados($selectTopTables);

            return $data;
        }

        public function getMemberByFilter(array $param = array())
        {
            $dataTables = $this->getDataTableMembers();

            $parseSql = array();

            if (!empty($dataTables)) {
                foreach ($dataTables as $key => $value) {
                    $parseSql[] = 'Select ID, user_id, user_type from School_' . $value['first_number'] . '_members';
                }
                $sql = '('.implode(' UNION ALL ', $parseSql).')';

            $select = $this->dbTable->select()
                ->setIntegrityCheck(false)
                ->from(new Zend_Db_Expr($sql).' AS mb',
                array(
                    'mb.ID',
                    'mb.user_id',
                    'mb.user_type'
                ));

                if (isset($param['user_id'])) {
    //                $w[] = 'mb.user_id = ? ';
    //                $val[] = $param['user_id'];
                   $select->where('mb.user_id = ? ', $param['user_id']);
                }
                if (isset($param['id'])) {
    //                $w[] = 'mb.ID = ? ';
    //                $val[] = $param['id'];
                     $select->where('mb.ID = ? ', $param['id']);
                }
                if (isset($param['user_type'])) {
    //                $w[] = 'mb.user_type = ? ';
    //                $val[] = $param['user_type'];
                    $select->where('mb.user_type = ? ', $param['user_type']);
                }

                $select->group( array(
                    'mb.ID',
                    'mb.user_id',
                    'mb.user_type'
                ))->assemble();
                var_dump($select); die();

                return $this->getAllDados($select);
            }
        }
}

The SQL of the method: getMemberByFilter() should look like this:

SELECT mb.ID, mb.user_id, mb.user_type FROM (
  Select ID, user_id, user_type from School_1_members
  UNION ALL 
  Select ID, user_id, user_type
  from School_2_members 
  UNION ALL 
  Select ID, user_id, user_type from School_3_members 
   UNION ALL Select ID, user_id, user_type 
  from School_3_members
) as mb
WHERE mb.user_id = ? 
  group by mb.ID, 
  mb.user_id, 
  mb.user_type

But it’s not happening, it’s going wrong.

The tables are basically like this:

CREATE TABLE `School_1_members` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` text COLLATE utf8_unicode_ci NOT NULL,
  `user_type` text COLLATE utf8_unicode_ci NOT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=394 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `School_2_members` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` text COLLATE utf8_unicode_ci NOT NULL,
  `user_type` text COLLATE utf8_unicode_ci NOT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=394 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `School_3_members` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` text COLLATE utf8_unicode_ci NOT NULL,
  `user_type` text COLLATE utf8_unicode_ci NOT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=394 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I also tried another alternative, which was to create a view, or a database precedent, to minimize the problem by just doing a direct query:

CREATE OR REPLACE VIEW School_X_Members AS (

/* aqui viria a consulta */

);

SET @resultQuery = NULL;


SELECT
  GROUP_CONCAT(
    DISTINCT
    CONCAT('SELECT ID, user_id, user_type FROM ', table_name)
    SEPARATOR '\r\nUNION ALL\r\n'
  )
INTO
  @resultQuery
FROM
  information_schema.tables as tb
WHERE (tb.table_name LIKE '%School_%' ) AND (tb.table_name LIKE '%_members%' ) order by 
CONVERT(REPLACE(REPLACE(table_name, "School_",""),"_members",""), UNSIGNED);


SELECT @resultQuery;

I just can’t seem to make it work.

Answer 1

Good afternoon Ivan, follow a suggestion.

<?php
$conn = new mysqli('host', 'user', 'password', 'database');
$queryTables = "select * from information_schema.tables where TABLE_NAME like 'School_%_members'";
$result = $conn->query($queryTables);

$query = "SELECT mb.ID, mb.user_id, mb.user_type FROM (\n";
$delimiter = "UNION ALL ";

while ($row = $result->fetch_object()) {
    $query .= "Select ID, user_id, user_type from {$row->TABLE_NAME}\n{$delimiter}";
}

$query = substr($query, 0, strlen($query) - strlen($delimiter))
. ") as mb\n"
. "WHERE mb.user_id = 1\n";

echo $query;

This example mounts your query with the UNION ALL dynamics, extracted from the Information Schema.

All your Model I ignored, tried to focus on SQL...

And it also seems unnecessary to use the clause GROUP BY because you are not doing sum(), or Count(), etc., it seems to me that you just want to retrieve the records of the N tables-sisters, then see that in my example I left out the grouping.

I suggest using only UNION ALL or UNION, do not need to group as I understand it... The difference between the two is that UNION ALL returns duplicate records while UNION does not.

At the end of the query you have the user_id parameter that I used 1, and to test here I created the 3 tables according to your SQL and inserted the records like this:

insert into School_1_members (user_id, user_type, data) values ('1', 'customer', 'my data customer');
insert into School_1_members (user_id, user_type, data) values ('2', 'customer', 'another data');
insert into School_1_members (user_id, user_type, data) values ('3', 'vendor', 'vendor data');
insert into School_2_members (user_id, user_type, data) values ('1', 'customer', 'my data customer');
insert into School_2_members (user_id, user_type, data) values ('2', 'customer', 'another data');
insert into School_2_members (user_id, user_type, data) values ('3', 'vendor', 'vendor data');
insert into School_3_members (user_id, user_type, data) values ('1', 'customer', 'my data customer');
insert into School_3_members (user_id, user_type, data) values ('2', 'customer', 'another data');
insert into School_3_members (user_id, user_type, data) values ('3', 'vendor', 'vendor data');

Answer 2

The way I did it worked, I just had to change that line:

 $select = $this->dbTable->select()
                ->setIntegrityCheck(false)
                ->from(new Zend_Db_Expr($sql).' AS mb',
                array(
                    'mb.ID',
                    'mb.user_id',
                    'mb.user_type'
                ));

for:

 $select = $this->dbTable->select()
                ->setIntegrityCheck(false)
                ->from(array('mb' => new Zend_Db_Expr($sql)),
                array(
                    'mb.ID',
                    'mb.user_id',
                    'mb.user_type'
                ));