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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a problem that they made 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 with each new user ... probably the developer created this new problem, to address a slowness problem, and probably he has never heard of table indexes, however, I have to make a temporary workaround, and then fix the database. So I am trying to solve this through code, it occurs that I am having difficulty implementing a query, I am using Zend Framework 1.12.20.

This is the Dao 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 this is not happening, it is giving error.

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 procedure in the database, to minimize the problem, making only 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 not make it work, could anyone help me with that, thanks.

php mysql sql zend

asked by anonymous 09.01.2017 / 14:17

2 answers

0

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 assembles your query with the UNION ALL fault% extracted from the Information Schema .

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

And I also find it unnecessary to use the GROUP BY clause because you are not doing sum (), nor count (), etc., it seems to me that you just want to retrieve the records from N sibling tables, example I left out the grouping.

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

At the end of the query has the user_id parameter 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');

09.01.2017 / 20:23

jQuery adds class but does not remove Center element image and decrease column size in Table

score 0 · Accepted Answer

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'
                ));

To:

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