Using MySQL View without PK

[dimoss]

Hi

I have created a View using phpMyadmin which returns the results ok.
I created a list using this View as usual. The View hasn't a PK value.
When I tried to run this fabrik list I got an error that there is an Unknown column 'PLAYER_CANCELLATION.' in 'field list'.
So I re-created the View adding an id and the error gone.

However in the past I used exactly the same View with a table in F2.1.x with J!1.5.26 without a PK value and worked like a charm.

It seems that in F3.1.x the PK value is mandatory or there is a problem??

And what about the views which are based in other views where I can't have an id?

In MySQL we can't create an automatic increment id in Views.

Any help on this is appreciated.

Thanks.

 

[Jaanus]

Set the needed field as pk in list admin

 

[dimoss]

Hi Jaanus

There is no PK to set...That is the problem.
In F2.1 that was not a problem but in F3.1.x seems that the pk field is mandatory

 

[Jaanus]

so there is no one pk field involved in the mysql view? Or at least any other field with unique data?

EDIT: ah sorry, didn't notice that you actually said that you added the id column to the view and then the issue was solved...

 

[cheesegrits]

This isn't a 2.1 vs 3.1 thing. Whether you use a "real" table or a view for your List, you have always had to tell Fabrik what field to use as the "PK", so we have some unique identifier to use as the rowid. MySQL views don't really have "Primary Keys", but we still need to know which element should be considered the PK for purposes of uniquely identifying each row.

I think the only difference may be that when creating the List from the MySQL view, 2.1 made an assumption that if it found a 'fabrik_internal_id' field in the view, it automagically used that as the PK. I think we removed (or broke!) that assumption in 3.x when we moved to using just 'id' as our automatically created PK elements in tables we create, so 'id' is less likely to be the field we need.

So bottom line, when creating a List from a View, you just need to be sure that you manually select the "pseudo" PK.

-- hugh

 

[dimoss]

Thanks Hugh..
But what if there is not even one PK in the View? I cannot add a pseudo PK if it's not contains unique identifiers, right?

 

[Jaanus]

But probably the tables you use to create view have unique id-s? Then you can try to concatenate them with a separator that is also a number. Let's say it's 0. Then - select concat_ws(0, table1.id, table2.id,table3.id) as id, ... if table1.id = 30, table2.id = 77 and table3.id = 2 then the concatenated value will be 3007702

Almost the similar trick could be used if your view is created with UNION statement.

 

[dimoss]

Hi Jaanus

You gave me an idea with the UNION statement.
Thanks a lot for the concat_ws function too!

Dimos

 

[cheesegrits]

What he said.

If there isn't a way of getting a unique id by just selecting the PK of one of the tables you are including, for instance if you are left joining other tables such that you will have multiple rows in the view with the ID, then concat something out of the main table's PK and the join'ed PK(s), so you get a unique number.

-- hugh