Fabrik error generated incorrect query for a MySQL view

[maleman]

I have a MySQL view that is made up of two fields: County, Permit Count.
The Permit Count is a MySQL count() that gives me the count of permits by county, and the view is grouped by County and Ordered by Count.

Fabrik creates the list connection without error, but when I try to view the data from the admin it generates the error:
500 Fabrik has generated an incorrect query for the list Permits Needing Reviewed NEW: <br /><br /><pre>Unknown column 'v_permits_noreview_cnt.' in 'field list'</pre><br /><pre> SELECT SQL_CALC_FOUND_ROWS DISTINCT `v_permits_noreview_cnt`.`property_county` AS `v_permits_noreview_cnt___property_county`, `v_permits_noreview_cnt`.`property_county` AS `v_permits_noreview_cnt___property_county_raw`, `v_permits_noreview_cnt`.`permit_count` AS `v_permits_noreview_cnt___permit_count`, `v_permits_noreview_cnt`.`permit_count` AS `v_permits_noreview_cnt___permit_count_raw`, `v_permits_noreview_cnt`.`` AS slug , `v_permits_noreview_cnt`.`` AS `__pk_val` FROM `v_permits_noreview_cnt`</pre><br />
/var/www/html/cmr/permits/components/com_fabrik/models/list.php:948

I have tried creating this connection in multiple ways but cannot seem to figure out why this is happening.
Any ideas?

Thanks for your help!

 

[maleman]

I was able to get this working. As you can see with the above SQL statement that Fabrik was generating, it was void of a field name that it assumed would be a PRIMARY KEY. As you may know, views in MySQL do not have an assigned PRIMARY KEY because... they are views not tables.

My work around was to pull the primary key field from my County table and added it to my Select AND Group By statement in my SQL code that creates the view.
Once the new version of the view was created, I deleted my Fabrik connection to this view and created a new connection (ie list) and saved it. I re-edited the list connection and set the PRIMARY KEY field to my new county id field.

One side note, Fabrik does not support any field types for the Primary Key other than integer.

Hope this helps others!

 

[henk]

Well the error tells you that `v_permits_noreview_cnt`.`` AS slug is an unknown column. You need to specify a column name instead of just ``. This has nothing to do with a primary key.
Fabrik expects a primary key for a view indeed. If not specified, `id` is assumed, but you can select any other field.
I need to check if the primary key should be an integer. I doubt this is true. I never had an issue with that and I use many views.

 

[henk]

Just checked primary key for a view: date & text = not accepted, integer & decimal = accepted.
Value does not need to be unique.

 

[troester]

It's also accepting varchar (so I assume text should do, too). You may have to set autoincrement=no in this case.

If you are creating a list linked to a MySQL view you'll get the message what to do

Message
We detected that you are creating a list on a MySQL view, so we have set 'Alter Existing Fields' to No, to prevent Fabrik from attempting to alter the table schema, for example when editing elements
A join or a list has been added which has no Primary Key. Most likely cause is you are creating a new list on a MySQL view, or a join element joining to one. Views don't have PKs, and we couldn't find an 'id' element to use. If you are creating a list, you will need to manually designate the PK in the list settings now, in the 'Data' tab. If you are creating a join element, make sure that a Fabrik list has been created on that view, and the PK designated. Once that is done, Fabri will know what field to use as the PK when you create a join to it.
Item saved.

 

[henk]

After another look at one of my views, it seems that selection of primary key depends on the type of element.
A jdate and calc elemend cannot be selected. This may be logic for a calc, but a jdate should work, I think.
In Fabrik you need to select any of the primary keys for a view to work, although a primary key has no function in a view and should be ignored.