Hello

I've created two "Views" inside my database, which I'm then using wpdatatables to display. The purpose of this was to create a pivot view of various fields stored in the meta_key column and and their associated values from the wp_usermeta table. The first View (hkoda_Users) returns all the correct values, including a bunch of NULL values, and so the purpose of the second View (hkodaUsers_pivot) is to normalize the table, bringing everything into line.

This is how I did it:

create view hkodaUsers as (

  select

    wp_users.ID,

wp_users.user_login,

    case when wp_usermeta.meta_key = "first_name" then wp_usermeta.meta_value end as first_name,

    case when wp_usermeta.meta_key = "last_name" then wp_usermeta.meta_value end as last_name,

case when wp_usermeta.meta_key = "gender" then wp_usermeta.meta_value end as gender,

case when wp_usermeta.meta_key = "dob" then wp_usermeta.meta_value end as dob,

case when wp_usermeta.meta_key = "bornHK" then wp_usermeta.meta_value end as bornHK,

case when wp_usermeta.meta_key = "permResident" then wp_usermeta.meta_value end as permResident,

case when wp_usermeta.meta_key = "wp_capabilities" then wp_usermeta.meta_value end as wp_capabilities

from wp_usermeta

inner join wp_users

    on wp_users.ID = wp_usermeta.user_id

);

create view hkodaUsers_pivot as (

  select

hkodaUsers.ID,    

hkodaUsers.user_login,

    MAX(hkodaUsers.first_name) as first_name,

    MAX(hkodaUsers.last_name) as last_name,

    MAX(hkodaUsers.gender) as gender,

    MAX(hkodaUsers.dob) as dob,

    MAX(hkodaUsers.bornHK) as bornHK,

    MAX(hkodaUsers.permResident) as permResident,

MAX(hkodaUsers.wp_capabilities) as wp_capabilities

  from hkodaUsers

  group by hkodaUsers.user_login

);

And this works fine, up until the point where I enabled front-end editing capabilities in wpdatatables, and at that point, I keep receiving errors and the data will not update. Further research led me to understand that MySQL views are uneditable in circumstances where there is aggregated data using functions such as MAX, like I have above.

What can I do to resolve this?

Many thanks in advance.