Okay
  Public Ticket #1739453
Filtering remote key field and multi table views
Closed

Comments

  • Richard McKean started the conversation

    Hi

    I have a table of properties  that uses a secondary table with foreign key to populate street names.There are numerous properties on numerous street so we have a many to many relationship. 

    Setting up the lookup table  for a street name in a new property record is just fine.However the user wants a view  to filter by street and to do this it would seem either

    1. the street field in the property table contains the street name . This then breaks the referential integrity of the lookup table - ie if the spelling is changed in the look up table , it would not reflect in the property table  or

    2. the street field holds the street ID and you have to search on the ID number which is not good 

    Is there something I am missing


    Secondly I am having problems creating a view with the main table - say properties  pulling data from a  street table and property type table . 

    I tried creating a new SQL search in the editor but my script seemed to get overwritten  when I tried to create the table. 

    Otherwise the plugin is just great


    Cheers


    Richard

     

  • Richard McKean replied

    Further to above comment on sql script attached two images - One show the script the second shows the rendered query

  • Richard McKean replied

    Just some meore detail on getting the SQL script for three table. Attached image shows setting for getting a view of Masterindex,strett and Full_name from three tables.

    The generated script was 

    SELECT Progress_Tracker_1.`Masterindex`,
           Streets.`Street`,
           Volunteer.`Full_Name`
    FROM Streets
      INNER JOIN Volunteer
         ON Progress_Tracker_1.`VID` = Volunteer.`ID`
      INNER JOIN Progress_Tracker_1
         ON Streets.`ID` = Progress_Tracker_1.`StreetID`

    and generated an error as Progress_Tracker_1 was undefined

    This did not run but this does

    SELECT Progress_Tracker_1.`Masterindex`,
           Streets.`Street`,
           Volunteer.`Full_Name`
    FROM Progress_Tracker_1
      INNER JOIN Volunteer
         ON Progress_Tracker_1.VID = Volunteer.ID
      INNER JOIN Streets
         ON Streets.`ID` = Progress_Tracker_1.`StreetID`

    Help here would be gratefully appreciated


    Cheers

    Richard




  • Richard McKean replied

    Apologies - forgot the image !

  •  1,667
    Miloš replied

    Hi Richard,
    Thank you for your purchase.

    If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.

    Please note some this when working with the server-side processing feature:

    • Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
    • Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also server-side processing feature adds this part of statement automatically when users triggers the sorting on the front-end, and having it in initial statement may cause the table to crash.


    Unfortunately due to our support policy and included support of the plugin , we are not allowed to write a queries for the customers. Included support covers help with bugs and general inquiries for the plugin features, but not writing custom code. 

    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  • Richard McKean replied

    Hi Bogdan

    Many thanks for that - I should have thought it through myself !! Old age  I guess !.

    However I do still have a slight issue. I have created a view from a master table and foreign key tables It imports into WPtables fine however I have a problem with updating.I only wish to update a field from the master table. I can do this with no problem in the excel type editor, ( attached image shows master table fields-grey, foreign fields Yellow and Green, editing Textrec field only)   however when I switch to standard editor I get the attached error saying cannot join more than one table when I apply udates. It would seem the two editors generate different scripts !.  Does the excel editor just update the cell whereas the standard editor tries to update the row even when there are no changes to fields? This will give me a problem as I would like some users to just update using the web page view. Can I get the excel editor to display on the webpage ? Any ideas??


    Cheers


    Richard

  •  1,667
    Miloš replied

    HI Richard,

    Probably the issue is because of the JOIN. 

    If you take a look at our documentation you will see this notice

    • Only one MySQL table can be edited at a time. Queries from multiple tables with joins cannot be used as an editable feature, since SQL UPDATE and INSERT statements are generated automatically, and there currently is no way to update multiple tables – but this problem is being investigated and pursued.
    • Only MySQL tables or tables created with “Create a table manually” and “Create a table by importing data from data source” options of “Create a Table” page can be edited.
    • Server-side processing MUST be turned on for front-end editing – Note: It will turn on automatically if you forget to enable it.
    • MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.


    As for the excel like view you can paste it in the page by choosing to display the table in excel like view ( attachment) 

    Keep in mind that there are some limitations in the Excel like view - link.


    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  • Richard McKean replied

    Thanks Bogdan

    • MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.

    I understood this to mean that although a view came from two tables, one could edit one of them  - but that doesn't seem to be the case ! - Had read all the notes but I guess had misunderstood  - I had assumed a view would be treated as one table !

    Anyway - many thanks and we will work around it somehow. How do you manage an order situation where the order table will require to pull a vendor and an order item type say ? I an sure many of your users would have that type of configuration

    Thanks again

    Richard


  • Richard McKean replied

    Hi aqain Bogdan

    I caanot see where to replicate your screen dump. Where do I find that option box?? !


    Cheers


    Richard

  •  1,667
    Miloš replied

    HI Richard,

    When you are pasting the shortcode in the page and click on this icon ( attachment 1) 

    When you click on that icon you can choose table view like from the attachment below in my previous reply.


    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  • Richard McKean replied

    Yeh - You learn something new very day !!!! - Thanks 

    I am going to have to play about !!


    Many thanks


    Richard


  •  1,667
    Miloš replied

    Hi Richard,

    You are welcome.

    I hope that you will find something that will good use.


    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables