Using a view

mbrennand

I am trying to update a view, but receiving this....

{"error":"SQLSTATE[HY000]: General error: 1288 The target table course_descriptors of the UPDATE is not updatable","data":[]}

allan

It sounds like there might be a permissions error or possibly course_descriptors might be a VIEW. Is either correct?

Thanks,
Allan

mbrennand

Yes course_descriptors is a VIEW. Do these not work?

allan

Sorry no - not at this time. As the database error is saying, with that setup the view can't be updated. My understanding is that some of the latest versions of SQL Server allow views to be updatable, but it isn't something I have experimented yet with myself.

I don't believe this is a limitation with the Editor libraries, but rather with the database itself. If you can do an UPDATE on the view in raw SQL, then it would probably work with the Editor libraries as well.

Allan

mbrennand

Ok, thank you for the response.
Could I produce this in the editor?

CREATE VIEW course_descriptors AS
SELECT p.ID, p.offeringid, p.post_modified, p.post_type, p.ac_year, p.post_status, p.script_run, p.post_title, p.ProSolutionTitle,
max(case when pm.meta_key = "strapline" then pm.meta_value end) strapline,
max(case when pm.meta_key = "progression_career" then pm.meta_value end) progression_career,
max(case when pm.meta_key = "modules" then pm.meta_value end) modules,
max(case when pm.meta_key = "overview" then pm.meta_value end) overview,
max(case when pm.meta_key = "entry_requirements" then pm.meta_value end) entry_requirements
FROM wp_postmeta as pm 
LEFT JOIN wp_posts as p on pm.post_id = p.ID
WHERE pm.meta_key IN ('strapline','progression_career','modules','overview','entry_requirements') AND p.post_type = 'courses'
group by p.ID, p.post_title

allan

I'm sorry to say that the Editor libraries currently doesn't support SQL functions. However, what you could do is use your VIEW for the data fetch to display in the table (i.e. DataTables' own ajax request) but use the Editor libraries to update the source SQL table directly (assuming that you are only modifying data that is static - i.e. not passing through an SQL method).

Allan