Cannot make list from database view

S

SjAndringa

New Member
In my database (connection through fabrik's connection) I have a view (v_komendjaar) which gives me a specific set from the tables t_jaar and t_ditjaar.
In this fabrik version (3.1rc2 on joomla 3.2) I cannot view data of this list: error message (see below).
Also, I receive a message when I try to change some properties of this list (see message below).
In fabrik 3.09 this problem does not occur.

Is this a fabrik 3.1rc2 problem?

Hopefully this can be solved, otherwise I think to sticking to the old version.

Sjoerd Andringa
500 Fabrik has generated an incorrect query for the list v_komendjaar:

Unknown column 'v_komendjaar.' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `v_komendjaar`.`Id` AS `v_komendjaar___Id`,
`v_komendjaar`.`Id` AS `v_komendjaar___Id_raw`,
`v_komendjaar`.`vakcode` AS `v_komendjaar___vakcode`,
`v_komendjaar`.`vakcode` AS `v_komendjaar___vakcode_raw`,
`v_komendjaar`.`afdeling` AS `v_komendjaar___afdeling`,
`v_komendjaar`.`afdeling` AS `v_komendjaar___afdeling_raw`,
`v_komendjaar`.`leerjaar` AS `v_komendjaar___leerjaar`,
`v_komendjaar`.`leerjaar` AS `v_komendjaar___leerjaar_raw`,
`v_komendjaar`.`cohort` AS `v_komendjaar___cohort`,
`v_komendjaar`.`cohort` AS `v_komendjaar___cohort_raw`,
`v_komendjaar`.`` AS slug
, `v_komendjaar`.`` AS `__pk_val`
FROM `v_komendjaar` LIMIT 0, 10

1347 'kvdor_glupta.v_komendjaar' is not BASE TABLE SQL=ALTER TABLE v_komendjaar COLLATE latin1_swedish_ci
​
 
Did you select id as primary key in list's "Data" section?

And as the last error message is showing: a view is not the base table, you can't change DB properies via view.
 
troester said:
Did you select id as primary key in list's "Data" section?

And as the last error message is showing: a view is not the base table, you can't change DB properies via view.
Click to expand...

I tried to select Id as primary key, but you can only select it after saving the list for the first time.than the error message 1347 already appears.
 
Looking at this one now. We added that collation stuff fairly recently, and it may not have been tested against views.

-- hugh
 
Hmmm, this should already be taken care of, as the first thing we do in our function that sets the collation is to test if it's a view, and if so, don't try and set the collation.

Testing to see if there might be an issue with the view testing code.

-- hugh
 
OK, found and (hopefully fixed) a couple of potential issues with our isView() code. So try updating to latest github, then edit and save your List one time. It should now save, and let you set the PK, and no longer blow up with with that COLLATION query.

If it still blows up, do this:

Backup your database, just in case!
With phpMyAdmin (or whatever database editor you use), edit your xxxxx_fabrik_lists table (where xxxxx_ is whatever your J! table prefix is).
Find the row for the List which is built on your view - should be easy to find, it'll have a 'db_table_name' of your view name.
Look at the 'params' field for that row. This should be a long JSON encoded string, which looks like this ...

Code: 
{"show-table-filters":"1","advanced-filter":"0", ... a whole bunch of stuff ... ,"allow_delete2":"","allow_drop":"3","isview":"1"}

If it doesn't have that "isview":"1" on the end, add it, and save the row.
If it has "isview":"0" instead of "1" on the end, change the 0 to 1, and save the row.

If it does already have the "isview":"1" on the end, then it should be working! So let let me know.

-- hugh
 
cheesegrits said:
OK, found and (hopefully fixed) a couple of potential issues with our isView() code. So try updating to latest github, then edit and save your List one time. It should now save, and let you set the PK, and no longer blow up with with that COLLATION query.

If it still blows up, do this:

Backup your database, just in case!
With phpMyAdmin (or whatever database editor you use), edit your xxxxx_fabrik_lists table (where xxxxx_ is whatever your J! table prefix is).
Find the row for the List which is built on your view - should be easy to find, it'll have a 'db_table_name' of your view name.
Look at the 'params' field for that row. This should be a long JSON encoded string, which looks like this ...

Code: 
{"show-table-filters":"1","advanced-filter":"0", ... a whole bunch of stuff ... ,"allow_delete2":"","allow_drop":"3","isview":"1"}

If it doesn't have that "isview":"1" on the end, add it, and save the row.
If it has "isview":"0" instead of "1" on the end, change the 0 to 1, and save the row.

If it does already have the "isview":"1" on the end, then it should be working! So let let me know.

-- hugh
Click to expand...

Thanks Hugh,

I'll look into it this week!

Sjoerd
 
cheesegrits said:
OK, found and (hopefully fixed) a couple of potential issues with our isView() code. So try updating to latest github, then edit and save your List one time. It should now save, and let you set the PK, and no longer blow up with with that COLLATION query.

If it still blows up, do this:

Backup your database, just in case!
With phpMyAdmin (or whatever database editor you use), edit your xxxxx_fabrik_lists table (where xxxxx_ is whatever your J! table prefix is).
Find the row for the List which is built on your view - should be easy to find, it'll have a 'db_table_name' of your view name.
Look at the 'params' field for that row. This should be a long JSON encoded string, which looks like this ...

Code: 
{"show-table-filters":"1","advanced-filter":"0", ... a whole bunch of stuff ... ,"allow_delete2":"","allow_drop":"3","isview":"1"}

If it doesn't have that "isview":"1" on the end, add it, and save the row.
If it has "isview":"0" instead of "1" on the end, change the 0 to 1, and save the row.

If it does already have the "isview":"1" on the end, then it should be working! So let let me know.

-- hugh
Click to expand...


Hi Hugh,

You did it! Great work. Problem solved. (I indeed had to change isview to 1)
Thank you very much

Sjoerd
 
cheesegrits said:
Thanks for the follow-up. Glad that sorted it out.

-- hugh
Click to expand...
Sorry Hugh,

still a small problem: Just to be shure my conversion runs correctly, I dropped my database (glupta) and truncated all of the Fabrik tables (except connections) and started again. After correct conversion I tried to make the list on that view again. After saving en telling the list that Id is the primary key the same error message 1347 appears.

I know now that I can change isview: to 1 manually, but apparently the system doesn't recognize this automatically.
Ideas?

Sjoerd.
 
Yes, I updated from GitHub
I looked up the settings:
I migrated the tables from Access, but I made the views from scratch in Mysql phpmyadmin
settings on local computer (to set up initial lists and forms)
Mysql 5.5
phpmyadmin 3.5.2.2

on test server (www.kvdorestad.nl/glupta)
Mysql 5.5
phpmyadmin 4.1.2

I have an idea: at this moment my database tables are in a different connection kvdor_glupta (so, in fact another database on the same server). Maybe if I change this and place my tables in the same database as Joomla/Fabrik and use the standard connection.

I'll let you know.

Sjoerd.
 
Hello
I work with version Fabrik 3.2. is the problem solved with this version or do I have to do as explained above ?
 
I don't know (your setup and if this special issue was fixed).
But update from GitHub, there was a fix addressing additional Fabrik connections recently.
So maybe it's working now...
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 143 (members: 2, guests: 141)
    Back
    Top