List from SQL View

[burghard]

I created a list from a SQL View. The List is- and the Elements seem to be populated well, but when I try to have a look at the data, I get the error message: "500 Fabrik has generated an incorrect query for the list LSB Meldung: <br />"

My SQL view does not include a primary key of any table.

Are there any constraints on creating fabrik lists out of SQL Views? I found nothing in the docs.

 

[lcollong]

I used them a lot.
Fabrik needs a primary index (in the data tab of the list) in order to display it.
Usually I add a "whatever index" named "id" in my view when I build it and use this "id" ad the main key for Fabrik.
But it also works using an existing columns of your view. I assume it's better if it is a integer column.

 

[burghard]

Yes, I saw that you have referenced a SQL View in some post here. Unfortunately, I have a view with a group by clause. So it seemed, that it is not possible to use the primary key of the underlying table. But I tried a trick. I added the max(id) as column and gave it an alias which reflects the name of the primary key. Now it works! For sure there is no insert and update possible with this view. But a readonly view has been all I wanted!

 

[burghard]

Hmm... once created, the ist is no more modifiable for ever. In the backend data is shown but in the frontend no data is shown. :-(

 

[cheesegrits]

You can modify it, you just have to kind of do it by hand. Recreate your view in phpMyAdmin (or whatever) with the new fields you need (or whatever your changes are). In Fabrik, make sure "Alter table" on the List's advanced settings is set to No, then add / remove / change the elements as required. That way Fabrik won't try and update the actual underlying "table", it'll just change the element configuration on top of it.

Depending on how the data is modified in the tables in your view, you may also need to turn caching off in the List settings for the view based list as well. Fabrik maintains a query cache for displaying lists, whereby if we get an identical query, we'll use cached results. And we only clear the cache for a list when we detect a change for it (like a form for that list being submitted). But because your view based list is never actually modifying the data, you can wind up using old cache data, that has been modified through another list.

If it works on the backend, there's no reason it shouldn't work on the front end. Try enabling Fabrik debug (in the Fabrik global options) and append &fabrikdebug=1 to your list URL on the front end. Then look at the getData() query, see if there's any obvious issues (or copy and paste and run it by hand in phpMyAdmin).

I use a lot of views as read-only "reports" on client sites, it definitely works.

-- hugh

 

[burghard]

OK! After recreating the list with "Alter table" set to "NO" leaves the list be alterable for future changes. But once created with "Alter table=YES" is is no more changeable, because the error "No Base Table".

The getData() query gets the desired data but the list does not show them even the page links shows 10 pages for the list the page rows does not show. In the table:data there are the rows as expected (10 at a time).
Other Lists on this site are rendered well. There are no js errors.

 

[burghard]

The table is submitted to the browser but it is display:none. The most table cells are empty. Here is a snipped of the rendered html:

 

[cheesegrits]

I'll need to access your site.

-- hugh

 

[troester]

K! After recreating the list with "Alter table" set to "NO" leaves the list be alterable for future changes. But once created with "Alter table=YES" is is no more changeable, because the error "No Base Table".

I assume you've translated this from German?

I think you've set the form to "In Datenbank speichern"(Record in database)=nein. This is the wrong way (it's for forms like search form, creating no database table) and changing this setting on existing forms with lists will mess up things (you can't use the list/database table any longer).

If you want to set a list readonly you must set the list "Access" settings.
If you want database column types not to be changed via Fabrik you must set "Alter field types"=no in list "Advanced" settings.

 

[burghard]

The list is visible under https://mitglieder.spyc.de/index.php/de/cg

 

[troester]

Create a new list on this view and don't touch "In Datenbank speichern".

 

[cheesegrits]

I keep saying that one day I'll disable that setting once a list/form has been created.

-- hugh

 

[burghard]

OK! I never touched the form. I only created a list. "In Datenbank speichern" means "save to db" that is an attribute for the form for lists it is "Existierende Felder ?ndern" which means "change existing fields". I think you mean that.
So here is what led to success:

1. Create the SQL View in the Database.
2. Create the List and "link to table" (to your view). Set the "Change existing fields" to "NO" but "DO NOT CHANGE ANOTHER OPTION". Save it.
3. Change the options as desired. All write permissions (Add, Change, Delete) should be set to "nobody".

I do not know which option can be changed in step 2. But changing no option worked for me, and changing some options didn't.
CG: Thanks a lot! You lead me to the right path, again.

 

[cheesegrits]

The other thing you sometimes have to do is tell Fabrik which field to use as the PK. Normally we do this automatically when creating a list from an existing table, by just looking at the table schema and finding the designated PK. But of course views don't have one. We look for a field called 'id' and use that for views if there is one, but if a) that isn't what you want to use, or b) there isn't one, you'll need to tell us which field to use.

Of course we can't then actually set it as the PK in MySQL, but we still need to know which field to use as the 'rowid' for uniquely identifying rows.

-- hugh

 

[gabriel.deluca]

OK! I never touched the form. I only created a list. "In Datenbank speichern" means "save to db" that is an attribute for the form for lists it is "Existierende Felder ?ndern" which means "change existing fields". I think you mean that.
So here is what led to success:

1. Create the SQL View in the Database.
2. Create the List and "link to table" (to your view). Set the "Change existing fields" to "NO" but "DO NOT CHANGE ANOTHER OPTION". Save it.
3. Change the options as desired. All write permissions (Add, Change, Delete) should be set to "nobody".

I do not know which option can be changed in step 2. But changing no option worked for me, and changing some options didn't.
CG: Thanks a lot! You lead me to the right path, again.

Just ADD in the view a field like this
create view as "select null as id, ....your fields from your table"
And them create the list