Acces to SQL views via Fabrik

[venekamp]

Hi ,
I created SQLviews in PhpMyAdmin. I figured out that I need a unique key named "id" to access the list-data via fabrik.
Once I save the new fabrik returns the error code 1347 .
<<
1347 'venekamp.qry_#__maandanalyse' is not BASE TABLE
C:\wamp64\www\gezin\libraries\joomla\database\driver\mysqli.php:665
>>
I can see the data in the list and forms. However changes in the list definitions like access permission, sorting, grouping and joins show the same errocode and are ignored. I really want to change at least permissions so the data in the views can not be changed and icons are not shown. Tips and tricks are welcome

The behaviour of fabrik is confusing regarding to the elements in database queries:
I can change elements in the views . Saving elements returns the messages, between << and >> however I can change everything I need in fabrik without changing the database query
<<
Bericht
A join or list has been added which has no Primary Key (PK). Most likely cause is you are creating a new List on a MySQL view, or a join element joining to a MySQL view. Views don't have PKs, but as we found a field called 'id' we are assuming this is the PK. If this is the PK, you can ignore this message. If not, if you are creating a list you should manually designate the PK in the list settings now, in the 'Data' tab. If you are adding a join element, you should create a Fabrik List based on the view you are joining to, in order to specify which element to use as the PK, then edit and save the join element again
Item opslaan geslaagd.

Attentie
De tabel die je wil updaten heeft een prefix van je joomla database, hoewel het geen core Joomla of Fabrik tabel is, kan hij wel gebruikt worden door andere componenten

• Update de veldstructuur voor `subcode` van INT(11) naar INT(4).

• Please read carefully any notifications that may appear regarding the update you are about to perform.
• Then check that the database field's structure and or name are desired.
• If you are happy to continue press 'Save'
• Otherwise press 'Cancel', this will revert the element name and plug-in type to the element's setting previous to being edited

>>
The first part "Bericht" I understand and can be ignored because I use id as Primary Key. I expect this when I create a list and not change an element.
The second part I don't understand because is says I use the prefix of my joomla database. My standard prefix is : "vene" for the joomla tables and in the queryviews I prefix with "qry". This is confusing.
The third part (5th bullet) is weird as I choose cancel (I don't want to change database table) and see after closing the element that my changes seem to be saved in fabrik. For me thjs flexibility is fine however buttons and text are confusing.

 

[troester]

I created SQLviews in PhpMyAdmin. I figured out that I need a unique key named "id" to access the list-data via fabrik.
Once I save the new fabrik returns the error code 1347 .
<<
1347 'venekamp.qry_#__maandanalyse' is not BASE TABLE
C:\wamp64\www\gezin\libraries\joomla\database\driver\mysqli.php:665

You can't change anything of a MySQL view structure via Fabrik, eg. adding elements/columns, changing element/column names, changing element/column definition (like INT(11)->INT(4)) because the underling view is "NO BASE TABLE". You must modify your view definition.

What is your exact view name: really qry_#__maandanalyse, i.e. containing # and multiple _ ?
Did you create the "id" column via your view definition and did you select it in the list "Data" settings as primary key?

 

[venekamp]

Thanks for the reply.

I don't understand this : "What is your exact view name: really qry_#__maandanalyse, i.e. containing # and multiple _ ?"

The sql viewname in phpmyadmin is: qry_vene_maandanalyse.
This view combines two tables using simple sql building blocks like separate grouping per table.
vene_fin_prognose (financial forecast) and vene_fin_boekingregels (bank transactions), This view groupes the single records on subrubriekid and month.
In a left join I have for each subrubriekid and month a record with the forecast and optionally the values derived from the bank transactions.
This is the view layout.
1 id int(11) : The unique Id inherited from the min(id) in vene_fin_prognose
2 jaar int(4) : Inherited from vene_fin_prognose
3 maand int(11): Foreign key to month in vene_fin_prognose
4 subrubriekid int(11) Foreign key to cost catagory in vene_fin_prognose
5 prognose double Sum of amounts in euro in vene_fin_prognose ==> I changed the element to display in decimal 6.2
6 prognoses bigint(21) Number of items in vene_fin_prognose ==> I changed the element to display integer 4
7 realisatie decimal(35,2) Sum of euro in vene_fin_boekingregels ==> I changed the element to display in decimal 6.2
8 posten bigint(21) bigint(21) Number of bank transactions in vene_fin_boekingregels ==> I changed the element to display integer 4
9 verschil double The difference between prognose and realisatie ==> I changed the element to display in decimal 6.2
10 fk_rubriek int(11) Foreign key to super grouping of subrubriekid

For me it is clear that it should be impossible to change the table definitions in the underlying sql table. That is what I like in the formatting tab. But the behaviour in Fabrik was quite confusing witj the save and cancel buttons. Now I know I am happy fabrik does what I wanted on element level.

On list level I see

The button "toevoegen" (add) and change I want to suppress. I tried in the list definition.
What I also expected is that I can zoom in on the individual transctions using the 'subrubriekid' in a join,
What is the approach to achieve this?

 

[troester]

For a "readonly" list (on a MySql view or a normal table) set list access settings.
Best is to add a Joomla access level like "nobody" (assigned to no Joomla group) and use this for add/edit/delete/empty.

Additionally you should set "Alter field types"=no in list settings (Details/Advanced tab) on MySQL views (and also in lists pointing to Joomla core tables or to any other table used somewhere else).

 

[venekamp]

Troester,
Thanks so far. I tried this and figured out that acces and descriptions at list level must be added when creating the list. So I must redo some designing.
To zoom in for details is another challenge that cannot be solved at creation of the list. So I will look for options using buttons in the forms.
Regards
Klaas

 

[troester]

Thanks so far. I tried this and figured out that acces and descriptions at list level must be added when creating the list. So I must redo some designing.
To zoom in for details is another challenge that cannot be solved at creation of the list. So I will look for options using buttons in the forms.

Not sure what you mean.
You can change list access settings, description etc at any time.
"Zoom in for details" = details view = clicking on "View details" button in list view...?

Looking at your error messages: maybe it's no good idea to mix the Joomla table prefix into a view name, I have really no idea if this will be handled correctly.

 

[venekamp]

<<
Not sure what you mean.
>>
I see. My description was not clear . Details are in the original tables not in the sql query. I will use a work around for this using html buttons.
<<
You can change list access settings, description etc at any time.
>>
Yes on lists based on tables. Not if the list is based on SQL query.

I tested on a new basic joomla install and only basic fabrik. First time save is OK, but changes after first save produce the errorcode 1347,
For now I can go on with the functionality I need but will be looking further. So for me the thread can be closed
Thanks for your help, when I find a direction I will share.

 

[troester]

Details are in the original tables not in the sql query. I will use a work around for this using html buttons.

You can add a custom detail URL (List settings/Links) pointing to the original list's details view.

First time save is OK, but changes after first save produce the errorcode 1347

I can save my Fabrik lists pointing to a MySql view as often as I need to.
It must be something with your setup.

 

[venekamp]

Hi Troester,
Doing some more tests I am more and more excited about the possibilties with Fabrik, without the need for coding.
1.
I did not set <<Additionally you should set "Alter field types"=no in list settings (Details/Advanced tab) on MySQL views (and also in lists pointing to Joomla core tables or to any other table used somewhere else).>> Maybe that was the reason why changes could not be saved. In a new list I set this option and now I can change the settings in the list and save. Thanks.
2.
For details I know the option you mentioned and that works fine with only 1 lookup option. I want at least 2 maybe 3 different options the user can choose from.