Using MySQL views

Feb 2, 2015
Using MySQL views
  • Fabrik is able to meet many needs for data manipulation and displaying, but not all. However, in particular cases we can do needed things with MySQL views.
    Generally Fabrik considers the views to be readonly, but actually some kind of views are insertable/updateable/deletable - in example simple views pointing to a single table in select statement as SELECT * FROM table_name or SELECT id, t1, t2 FROM table_name WHERE t3='some value' . The updatability of views may be, however affected by the value of the updatable_views_with_limit system variable. Read more in
    And the Fabrik team kindly ask all of us to be very careful when using views for inserting/updating data!
    Updateable views

    CASE 1 - bridge between tables via repeatelements like databasejoin or cascading dropdown in checkbox or multiselect mode
    When creating dbjoin/cdd as checkboxes or multiselect, also fileupload in ajax mode etc, we get a new database table named [your_parent_table_name]_repeat_[your_element_name] containing fields parent_id and [your_element_name]
    When I want that a repeatelement would be a real bridge between data in two tables
    under one list [table_1]
    • I create element [table_2_id] that creates table [table_1]_repeat_[table_2_id] containing fields parent_id and [table_2_id]
    • under second list [table_2] I create element [table_1_id] that creates table [table_2]_repeat_[table_1_id] containing fields parent_id and [table_1_id]
    • now I create in phpmyadmin a third table [table_1_id]_repeat_[table_2_id] with fields id, [table_1_id], [table_2_id], params
    • then I replace the table [table_1]_repeat_[table_2_id] with a MySQL view with the same name as SELECT id, [table_1_id] as parent_id, [table_2_id], params FROM [table_1_id]_repeat_[table_2_id]
    • and I do respectively the same with [table_2]_repeat_[table_1_id]
    note - as both mysql views are simply querying a single table they are updateable so I can edit, submit and delete data
    Both elements are showing another table data related to this table. In form view this data is checked/selected. Any change makes corresponding changes in other list/form.


    CASE 2 - databasejoin element pointing to the same table + frontend add
    Example from real life.
    I have one common table for different kind of works, incl. musical works and literatural works. For musical work entry I use a "bridge" table that creates connections between music and literature works. There I choose the needed text for my musical work. Every text is related to some entry in main table. One main entry could have many texts (original and translations). It could be that the main data exists but not just the needed text version. Then I use databasejoin frontend add to submit the missing text. But it could happen that no data is submitted about this literatural work. Then I click on another frontend add button near a databasejoin element inside this popup form.
    The popup #2 that opens submits data to the same table as my main form. And this could mess things because the field names are the same. Once I noticed that when I ended filling the popup form the text from original title field in main form was removed and the value in another, hidden field was changed.

    Solution; a MySQL view was created as SELECT * FROM work_main under another name (say work_main_add_lit), then list and form on it and finally the the dbjoin element was edited to point to this view instead.

    CASE 3 - multiparent - single child list join

    Actually, we can already create multiparent list joins so that
    • from t1 to t2 from pk1 to fk1
    • from t1 to t2 from el2 to fk2
    • from t3 to t2 from pk3 to fk3
    and then after submission the dataset in child (t2) looks like
    • id fk1 fk2 fk3
    • 1 pk1 el2 pk3
    • 2 pk1 el2 pk3
    that means
    select * from t1 left join t3 on t3.pk1=t1.pk1 left join t2 on (t2.fk1=t1.pk1 AND t2.fk2=t1.el2 AND t2.fk3=t3.pk3) . Let's call it "method AND"
    But in many cases we would need such data set:
    • id fk1 fk2 fk3
    • 1 pk1 NULL NULL
    • 2 NULL NULL pk3
    • 3 NULL el2 NULL
    and make it still insertable/editable via one form.
    We would need then the joins would work rather as left join t2 on (t2.fk1=t1.pk1 OR t2.fk2=t1.el2 OR t2.fk3=t3.pk3). Let's call it "method OR".
    Unfortunately, ATM we can't create a list join that would work this way. We should use more clever ways instead and turn again towards MySQL views.

    Example (that works in reality):
    An online registry contains musical and literatural works (incl song lyrics, sacred texts used in music ect). All main entries of works are in table work_main (where work_type_id equals respectively 1 or 2). Version data for musical works are built in work_music, literature data in work_lit, author data in work_authors. Additionally, we use work_music_title_lang as a bridge between data for musical and literatural works (work_main_id and text_main_id are both pointing to work_main.id, work_lit_id points to work_lit.id). There are more tables involved but for this example it's enough

    Musical works main data:
    select * from work_main LEFT JOIN work_music_title_lang ON work_music_title_lang.work_main_id = work_main.id LEFT JOIN work_music on work_music.work_main_id = work_main.id LEFT JOIN work_authors on work_authors.work_main_id = work_main.id WHERE work_main.work_type_id = 1 (here a default role is composer)

    Musical works version data: select * from work_music LEFT join work_authors on work_authors.work_music_id = work_music.id (here a default role is arranger)

    Literatural works main data
    select * from work_main LEFT JOIN work_lit on work_lit.work_main_id = work_main.id LEFT JOIN work_authors on work_authors.work_main_id = work_main.id [LEFT JOIN work_music_title_lang ON work_music_title_lang.work_main_id = text_main.id ] WHERE work_main.work_type_id = 2 (here a default role is text author)

    Literatural works version data
    select * from work_lit LEFT JOIN work_authors on work_authors.work_lit_id = work_lit_id (default role - translator)

    When an user submits new musical work he can choose corresponding literatural work in joined group (work_music_title_lang). But what if the needed literatural work is not available there? We should then submit one. We can do it via frontend add of dbjoin element.
    But here is a problem - sometimes the popup form produces a js error when submiting repeated joined data there. Anyway - it would be more convenient to submit also text author data via the musical work form. Unfortunately, as said, we can't do it using work_authors again as child table. Now is the time to create 2 MySQL views - work_authors_lit_main and work_authors_lit, both as select * from work_authors (or selecting only id and fields we need) . And 2 new list joins:
    • from work_music_title_lang to work_authors_lit_main from text_main_id to work_main_id
    • from work_music_title_lang to work_authors_lit from work_lit_id to work_lit_id (use of words "from" and "to" is correspondending to the list admin where "to" means foreign key).
    We make them repeated and in group admin we set minimum repeats to 0.
    Now - when we submit new musical work and
    • there already exist a needed literatural work then we need only to select it in the dbjoin dropdown and do nothing else. In authors sections we just submit a composer and don't touch the literatural author groups that are closed.
    • When needed text doesn't exist (let's say even no main data) then we open a frontent add popup, inside the popup another frontend add popup for parent data... when this all is submitted we fill the current form AND we add also text author (and if needed, translator) in corresponding groups.
    after this new musical work is submitted -
    • whoever is an administrator of literatural data, he/she sees that a new work is submitted and could verify whether the author data is full and correct
    • the text authors are displayed also with the musical work data
    Muusikateose_algandmed2.png
  • Loading...