Using a MySQL view as data source for a list

[peterk900]

I have had several goes at trying to use a MySQL view as a list data source. List creation works without any problem but when the primary key is saved this error occurs..
But the list saves ok and it is possible to see the data structure. Trying to display the data however gives a 500 error.

I used this post and created the tables and the view exactly as set out in the post. The 500 error is:
An error has occurred.

500 Fabrik has generated an incorrect query for the list : <br /><br /><pre>Unknown column 'pieview.' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `pieview`.`fabrik_internal_id` AS `pieview___fabrik_internal_id`, `pieview`.`fabrik_internal_id` AS `pieview___fabrik_internal_id_raw`, `pieview`.`date_time` AS `pieview___date_time`, `pieview`.`date_time` AS `pieview___date_time_raw`, `pieview`.`pecan` AS `pieview___pecan`, `pieview`.`pecan` AS `pieview___pecan_raw`, `pieview`.`apple` AS `pieview___apple`, `pieview`.`apple` AS `pieview___apple_raw`, `pieview`.`chess` AS `pieview___chess`, `pieview`.`chess` AS `pieview___chess_raw`, `pieview`.`punkin` AS `pieview___punkin`, `pieview`.`punkin` AS `pieview___punkin_raw`, `pieview`.`totpies` AS `pieview___totpies`, `pieview`.`totpies` AS `pieview___totpies_raw`, `pieview`.`` AS slug , `pieview`.`` AS `__pk_val` FROM `pieview` LIMIT 0, 10</pre>​

Another more recent post suggested editing the fabrik_lists table and changing the end of the parameters field to show isview: 1 , I did this but the 500 error above still occurs.​

What do I need to do to use a MySQL view ( in an external database ) as a list data source ? Thanks.​

 

[troester]

The 2nd error is because the (fake) PK is not set.
For the 1st error:
Check in list's "Advanced" settings which collation is selected.
It seems your DB is not set to the recommended UTF8-general-ci setting.

 

[peterk900]

Thanks Troester. Every time I try and save the PK I get the second error - even though I have changed the Collation as you asked. The PK value is not saved.

I've attached some screen shots of relevant settings.

 

[troester]

editing the fabrik_lists table and changing the end of the parameters field to show isview: 1 , I did this

This should not be necessary in recent versions.
The parameter should be set to "isview":"1" automatically. Did you edit anything? (It must be "isview":"1" with quotes ")

Please upload jpgs, no pdf.
Is your connection the site connection or an alternate one?
What is your DB collation? The collation of the view?
Which exact Fabrik and Joomla version?
Which mySql and php version?

 

[peterk900]

The initial setting was 0 for isview. Yes, I changed the value to 1 . The amended value has the double-quotes ....- allow_drop":"3","isview":"1"}
Sorry about the .pdf.
DB Connection is alternate.
DB Collation is as you suggested utf8_general_ci
MySQL 5.5.33
php 5.5.20
J! 3.4.1
Fabrik is 3.3.2 (read from zip install - can't remember where the Fabrik version is held).
Thanks for your input on this. I hope this information helps.

 

[troester]

DB Collation is as you suggested utf8_general_ci

No, it should match the view's one.
It shouldn't set the collation for views at all but maybe there's an issue with additional connections.
You can try to create the view inside the Joomla DB and use the standard site connection.

 

[peterk900]

The DB Collation and the Fabrik List Collation are the same - utf8_general_ci.

The view that I'm working with cannot be created in the J! DB as the tables used in the view are used by another application and are held in a external db. Are you saying that, with the current version of Fabrik, you cannot build lists based on a view in external databases ? If so, any idea when this issue might get fixed ?

 

[peterk900]

I just had a thought - if the issue is caused by not being able to save the PK value via the Fabrik element form - could I update the value directly in the Fabrik Joomla table ? If so, where is the PK stored ?

 

[peterk900]

Yes, this seems to solve the problem - update the field db_primary_key to db_table_name. pk e.g

`pieview`.`fabrik_internal_id`- before the edit it read ​

`pieview`.` `.​

Do you think there likely to be any unpleasant consequences of doing this ?

(As you suspected - there are no issues with views within the J! database.)

 

[Actucom]

peterk900 : thank you! I had the same issue when using lists that are based on a MySQL view. Editing the value of "db_primary_key" fixes it.
Apparently, no unpleasant consequence since the view is well displayed and Fabrik's ? Edit list ? Page shows the correct PK.

Only problem : each time the list is edited, Fabrik sets its PK back to a number, which generates incorrect queries.
Hint : that number matches with the ID# of the element that is chosen as a PK. Unluckily, it's the "id", not the "name", and the string indicating the "db_primary_key" is lost too.
I tried editing components/com_fabrik/models/list.php , but I could not find where the wrong PK comes from.

Can someone explain what PHP code part generates the PK for the lists that are built on a MySQL view ?