Dynamic Get - Join DB Tables - no views #251

New Issue

Closed

opened 2018-03-21 17:37:17 +00:00 by cpaschen · 7 comments

cpaschen commented

2018-03-21 17:37:17 +00:00

(Migrated from github.com)

Steps to reproduce the issue

Try to add a table view (a view created within MySQL) to the Dynamic Get in the Join DB Tables area.

Expected result

Views should be listed along with tables

Actual result

No views are visible, just tables

System information (as much as possible)

OS Name & Version: WAMP/LAMP
MySql Version: 5.5.56
Apache Version: 2.4.27
PHP Version: 7.1.9
Joomla Version: 3.8.5
JCB Version: 2.6.18
Browser: Chrome

Additional comments

It IS possible to link to views from Custom field types, but then they don't work properly when trying to display in a site view via a dynamic get because we can't link to them.

### Steps to reproduce the issue Try to add a table view (a view created within MySQL) to the Dynamic Get in the Join DB Tables area. ### Expected result Views should be listed along with tables ### Actual result No views are visible, just tables ### System information (as much as possible) - OS Name & Version: WAMP/LAMP - MySql Version: 5.5.56 - Apache Version: 2.4.27 - PHP Version: 7.1.9 - Joomla Version: 3.8.5 - JCB Version: 2.6.18 - Browser: Chrome ### Additional comments It IS possible to link to views from Custom field types, but then they don't work properly when trying to display in a site view via a dynamic get because we can't link to them.

Llewellyn commented

2018-03-22 15:12:02 +00:00

This is a little confusing, can you give a little more explanation, also some code and in what files you got them. There are so many words that can mean more then one thing. So with each of those, try to be more qualifying of the actual location, being very specific.

cpaschen commented

2018-03-22 17:42:29 +00:00

(Migrated from github.com)

Sorry, this isn't related to 'code generated' but with the actual JCB UI in the Dynamic Get area.
Here are the steps that reproduce the problem:

In PhpMyAdmin create a new VIEW
In JCB create a new Dynamic Get (Main Source: Back-end View OR Joomla Database)
Go to the JOINT tab
Click to add a new line in the "Join DB Tables" area
Click the DB Table drop-down to locate the view that you created in step 1
PROBLEM
The view does not appear available in the drop-down list (only actual 'tables' appear).

Tested on mysql 5.5.x and 5.7.x with same results.
Also upgraded to JCB 2.7.0 with same results.

Sorry, this isn't related to 'code generated' but with the actual JCB UI in the Dynamic Get area. Here are the steps that reproduce the problem: 1. In PhpMyAdmin create a new VIEW 2. In JCB create a new Dynamic Get (Main Source: Back-end View OR Joomla Database) 3. Go to the JOINT tab 4. Click to add a new line in the "Join DB Tables" area 5. Click the DB Table drop-down to locate the view that you created in step 1 PROBLEM The view does not appear available in the drop-down list (only actual 'tables' appear). Tested on mysql 5.5.x and 5.7.x with same results. Also upgraded to JCB 2.7.0 with same results.

Llewellyn commented

2018-03-22 19:41:04 +00:00

Okay the tables must have primary key (very important).

You will see that here line 150 of the dbtables.php field file.

When you are dealing with any field in JCB that is dynamic/custom you can look at how it works in this folder.

Okay the tables must have primary key (very important). You will see that here [line 150](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/models/fields/dbtables.php#L150) of the dbtables.php field file. When you are dealing with any field in JCB that is dynamic/custom you can look at how it works in [this folder](https://github.com/vdm-io/Joomla-Component-Builder/tree/staging/admin/models/fields).

ro-ot commented

2018-03-24 16:22:17 +00:00

(Migrated from github.com)

Assume issue is resolved due to no response.

cpaschen commented

2018-03-24 20:38:15 +00:00

(Migrated from github.com)

I'm sorry that I didn't respond in a timely manner, but I needed time to look into the previous response.
In researching MySQL, according to the docs, if the underlying table has an index, then it is automatically available via the view.
In this case the table has several indexes as well as a index.
So I'm not sure exactly why the code is not 'seeing' the index.
Sorry, that is a bit above my understanding of dealing with MySQL.
Just curious if anyone else tried to test this? (By creating a view on a table that has an index and see if the table is displayed) to make sure it was just me and that there isn't some problem?

I'm sorry that I didn't respond in a timely manner, but I needed time to look into the previous response. In researching MySQL, according to the docs, if the underlying table has an index, then it is automatically available via the view. In this case the table has several indexes as well as a index. So I'm not sure exactly why the code is not 'seeing' the index. Sorry, that is a bit above my understanding of dealing with MySQL. Just curious if anyone else tried to test this? (By creating a view on a table that has an index and see if the table is displayed) to make sure it was just me and that there isn't some problem?

ro-ot commented

2018-03-24 21:20:36 +00:00

(Migrated from github.com)

@cpaschen sure, I just did some house cleaning 🚶‍♂️

Ok he is not talking about index but prime key, prime keys is usually the id. The code that test for the prime key is what @Llewellynvdm pointed you to on line 160 you see it is basic sql statement

$db->setQuery('SHOW FIELDS FROM `'.$tables[$i].'` WHERE LOWER( `Key` ) = \'pri\'');

Here you can see how to make a column prime.

This is important since we need a key that is going to be unique and the prime key is the only type of column that is always using that behavior.

@cpaschen sure, I just did some house cleaning :walking_man: Ok he is not talking about `index` but **prime key**, prime keys is usually the id. The code that test for the prime key is what @Llewellynvdm pointed you to on [line 160](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/models/fields/dbtables.php#L160) you see it is basic sql statement ``` $db->setQuery('SHOW FIELDS FROM `'.$tables[$i].'` WHERE LOWER( `Key` ) = \'pri\''); ``` Here you can see [how to make a column prime](https://stackoverflow.com/a/9702856). This is important since we need a key that is going to be unique and the prime key is the only type of column that is always using that behavior.

ro-ot commented

2018-03-24 21:28:58 +00:00

(Migrated from github.com)

Still having trouble, give me the mySql that you used to create the table, and I will test it.