[FIXED] Where or how do I tell CakePHP (2.x) to ignore the Views in a database, so that it works only with the Tables?

Issue

I have a CakePHP 2.x app running with a MySQL database.

Another developer, for another purpose in another app (In Asp.Net) created some MySQL Views in the database itself.

These Views seem to be confusing for my CakePHP app, because it is taking them as tables and messing some things up in terms of keys, relationships, etc.

Is there a way or place for me to tell my CakePHP app that it needs not to worry about these Views and just keep working with the Tables as it always had before?

Solution

After continued reading on how CakePHP works with the MySQL database and also studying the MySQL syntaxes, I came up with the solution below and it worked. I post it here for others who may need it and/or for more experienced developers to post on rather or not this is a good idea.

I found the file where CakePHP grabs the tables from the database:

/lib/Cake/Model/Datasource/Database/Mysql.php

I found the line of code that does this job:

$result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']));

Notice the MySQL statement, especifically:

'SHOW TABLES FROM ' . $this->name($this->config['database'])

Reading the SHOW TABLES documentation, I learned what it does and its syntax: https://dev.mysql.com/doc/refman/5.7/en/show-tables.html

So, I changed the statement to:

'SHOW FULL TABLES FROM ' . $this->name($this->config['database']) . ' WHERE Table_Type != "VIEW"'

Basically:

1. I changed SHOW TABLES to SHOW FULL TABLES so that it would include the second column which holds BASE TABLE for regular tables and VIEW for views.

2. Then, I used this second column information in the WHERE statement that I added: WHERE Table_Type != "VIEW"' to filter views out of the results.

The new final line is:

$result = $this->_execute('SHOW FULL TABLES FROM ' . $this->name($this->config['database']) . ' WHERE Table_Type != "VIEW"' );

The app seems to be working perfectly again!

Answered By - vivipoit

Answer Checked By - Timothy Miller (PHPFixing Admin)