error on import view from Mariadb

[pevomjt]

OS Linux CentoS 7
Joomla 4.2.9
Fabrik 4 gamma 2
Php 8.1

When I import a view from MariaDB on create new list I get the following warning:
"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 ."
Go ahead created the list without any elements. I can't even add them manually.

 

[pevomjt]

I try to open list and get this error:
00 Fabrik has generated an incorrect query for the list xxxxxxxxxxxxx: <br />

 

[troester]

Not sure what you mean with "When I import a view from MariaDB".

The info message explains what to do:

You need a MySQL view with a column which can serve as a (fake) primary key in Fabrik (integer).

Then create a new list and link it to the view by selecting it in the list's Data tab "Or database table" and Save.
If your "fake" PK column is not named "id" you'll get the information you posted above. So just goto the Data tab again, select the correct column as "Primary key" and save again.

Keep in mind that a view is readonly. Fabrik will set "Alter field types" to No automatically. But it's up to you to manage the list access (Add/Edit/Delete etc).

To get further information for "Fabrik has generated an incorrect query...":
In Fabrik options/Debugging set "Allow..." to Yes, then open your list data again and append &fabrikdebug=1 to the URL. This will show a more detailed error message.

 

[pevomjt]

Not sure what you mean with "When I import a view from MariaDB".

ok, I Import this view from mariadb ia a nuw Fasbrik list:
CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `UsrSardegnaj4`.`vista_PPC_contaClas` AS
select `vpp`.`CMplesso` AS `CMplesso`, `vpp`.`classe` AS `classe`, `vpp`.`classe` AS `ma`, sum(`vpp`.`concesse`) AS `concesse`, if((max(`vpp`.`classe`) = 1),1,if((max(`vpp`.`classe`) = 2),2,3)) AS `orels`, `vpp`.`idcm` AS `idcm`, `vpp`.`sigla` AS `sigla`
from `UsrSardegnaj4`.`vistaPPCclassi` `vpp`
where `vpp`.`pluriclasse` = 0
group by `vpp`.`idcm`, `vpp`.`classe`;

The info message explains what to do:
You need a MySQL view with a column which can serve as a (fake) primary key in Fabrik (integer).
Then create a new list and link it to the view by selecting it in the list's Data tab "Or database table" and Save.
If your "fake" PK column is not named "id" you'll get the information you posted above. So just goto the Data tab again, select the correct column as "Primary key" and save again.
Keep in mind that a view is readonly. Fabrik will set "Alter field types" to No automatically. But it's up to you to manage the list access (Add/Edit/Delete etc).

I cant't select PK, dropdown is empty!

To get further information for "Fabrik has generated an incorrect query...":
In Fabrik options/Debugging set "Allow..." to Yes, then open your list data again and append &fabrikdebug=1 to the URL. This will show a more detailed error message.

When I do it, I get :

500 Fabrik has generated an incorrect query for the list AAA_newview: <br /><br /><pre>Unknown column 'vista_PPC_contaClas.' in 'field list'</pre>

 

[troester]

I cant't select PK, dropdown is empty!

Yes

You need a MySQL view with a column which can serve as a (fake) primary key in Fabrik (integer).

 

[pevomjt]

I tried with your suggestion,

CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `UsrSardegnaj4`.`vista_PPC_contaClas` AS
select `vpp`.`idc` AS `id`,`vpp`.`CMplesso` AS `CMplesso`, `vpp`.`classe` AS `classe`, `vpp`.`classe` AS `ma`, sum(`vpp`.`concesse`) AS `concesse`, if((max(`vpp`.`classe`) = 1),1,if((max(`vpp`.`classe`) = 2),2,3)) AS `orels`, `vpp`.`idcm` AS `idcm`, `vpp`.`sigla` AS `sigla`
from `UsrSardegnaj4`.`vistaPPCclassi` `vpp`
where `vpp`.`pluriclasse` = 0
group by `vpp`.`idcm`, `vpp`.`classe`;

but I got the same result

 

[troester]

Is vpp`.`idc type INT?

 

[pevomjt]

Yes it is
The same query in joomla 3.10 and fabrik 3.10 works!

 

[troester]

Link to view is working at my site.

Enable J! System debug and set Fabrik Options/Debugging "Allow Fabrik debug" yes to get the complete error message, query and the error stack.

If you see an error "Prepared statement needs to be re-prepared" (while trying to display the list/veiw data) try to increase table_definition_cache in your database server.

 

[pevomjt]

I send you screenshot
when I try to create list and save it ... I can't choose PK:

when I ckick to view data list I have

this is the some list in J3 e F3 php 8.1

you can see PK(Chiave Primaria) selected is CMPlesso ... and List working fine!

 

[troester]

The error message is obvious if no PK is selected.

What is the structure of you view in the database?
Which elements did it create during list creation?

 

[pevomjt]

sorry for my english, i will try to explain better

The error message is obvious if no PK is selected.

The problem is that in j4 and f4 it is not possible to select the PK because it is not selectable, as shown in figure1

What is the structure of you view in the database?

In database:

CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `UsrSardegnaj4`.`vista_PPC_contaClas` AS
select `vpp`.`CMplesso` AS `CMplesso`, `vpp`.`classe` AS `classe`, `vpp`.`classe` AS `ma`, sum(`vpp`.`concesse`) AS `concesse`, if((max(`vpp`.`classe`) = 1),1,if((max(`vpp`.`classe`) = 2),2,3)) AS `orels`, `vpp`.`idcm` AS `idcm`, `vpp`.`sigla` AS `sigla`
from `UsrSardegnaj4`.`vistaPPCclassi` `vpp`
where `vpp`.`pluriclasse` = 0
group by `vpp`.`idcm`, `vpp`.`classe`;

Which elements did it create during list creation?

I import the list directly from the database, using create list by fabrik, but no element is created! and when I click on the list name view data I get the error displayed in fugura2.
Instead in j3 and f3 I have no problem using the same list. figura 3

 

[troester]

J3/F3 and J4/F4 are different.

The structure:
Something like

The view must show at least one column with type INT.

It's still not clear to me what you mean with "import".
What is your exact workflow for creating the Fabrik list linked to the view?

 

[pevomjt]

this is the view:

It's still not clear to me what you mean with "import".
What is your exact workflow for creating the Fabrik list linked to the view?

From the components menu, select Fabrik->Lists and press new.
In details I insert Label, select data to import the view from the database, assign the name to the table, search and select the table name then press save.
Regularly now I should choose the primary key, but it won't let me do that!

I have over 45 Views in database but only 4/5 have this problem!

 

[troester]

I have over 45 Views in database but only 4/5 have this problem!

So it's working with other views? No general problem?

 

[pevomjt]

So it's working with other views? No general problem?

the same views that worked in j3 f3 now in j4 f4 do not work, also does not even allow creation.

 

[troester]

the same views that worked in j3 f3 now in j4 f4 do not work

That's not what I mean. Do your other views work in F4?

Create list:
After the first Save it should show the ..is view.. AND the ...no primary key... message.
At this point it also must have already created the elements.
If there are no elements for this new list it's clear that it can't show any selection for the PK.

So:
Did it create the elements?
If yes: which ones (element types)?
If no:
Did you get any error during the first save? Any JS error in your browser console? Do you have J! error reporting level set? J! System Debug on?

 

[pevomjt]

No I don't

Did you get any error during the first save? Any JS error in your browser console? Do you have J! error reporting level set? J! System Debug on?

 

[troester]

Ok.
https://fabrikar.com/forums/index.php?threads/fabrik-4-gamma2-now-available-for-download.53699/

There's a typo in the Gamma2 thumbs element (throwing "error - 0 syntax error, unexpected token "private", expecting end of file" if you want to add any new element)
Please install (via J! installer)
element_thumbs_gamma2a.zip
if you want to use it.

Edit: If you don't need thumbs you can just disable it in the Extension manager to fix this issue.

 

[pevomjt]

ok,
now f4 creates the list without the elements. Items must be added manually one at a time.