Announcement

Collapse
No announcement yet.

N:N Relations: On Update problem

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • N:N Relations: On Update problem

    Hi
    i make an N:N Relation, which shows, wich Courses (like modules) are available in different Workshops. Currently, i have 2 Courses (30,31) in Workshop 4. Whenn i press the update / save button without changing any values, scriptcase do the following:

    Code:
    (pdo-mysql): delete from CourseWorkshopLnk where WorkshopId = 4   
    (pdo-mysql): insert into CourseWorkshopLnk (id, CourseId, WorkshopId) values (NULL, '31', 4)   
    (pdo-mysql): insert into CourseWorkshopLnk (id, CourseId, WorkshopId) values (NULL, '30', 4)
    It delete's EVERY record in the "middle" table and then insert every record again.

    BUT i have other relations to this "middle" table. If a Course is available in a workshop, i have a table with course details, which describes the Room Number and Time for the specific Course in the workshop. Means, i have a Detailstable which has a relation to the id of CourseWorkshopLnk. So long, so good. But when scriptcase everytime delete and reinsert the records, my relation get lost. Why? How can i prevent scriptcase delete existing relations.
    YES, of course, when the user removes a Course from the workshop, the details like time and place can also deleted. I defined this in the relation (ON DELETE => CASCADE).

    I think this is a bug in scriptcase. Scriptcase should only delete or update things in the middle table when things changed.

    Or what other's think about that?

    Best regards
    Steve

  • #2
    Steve,

    Yes, I have seen this happen, and I agree it is a bug.

    As an example of this, in our database, three tables: employees, timeclocks, and employee_timeclocks
    We have many columns in the employees table, and many columns in the timeclocks table.
    The employee_timeclocks table is primarily to indicate which timeclocks a particular employee can use.
    It has a column for employee_id, timeclock_id, as you would expect for a N-N relationship, but it also has a few other columns: allow_facial_recognition, allow_fingerprint, etc.

    When editing an employee to allow him to use a new timeclock, scriptcase deletes all of that employee's employee_timeclock records, and rewrites them (rewriting only employee_id and timeclock_id, zeroing all other columns.).

    Bad bug.

    When you have to redesign your relational database structure to compensate for your development tool, it is not a good thing.

    Dave
    Dave Prue
    Code Whisperer
    Lahar International Corp
    www.lahar.net

    Comment


    • #3
      More like a design flaw than a bug if you ask me.

      Regards

      Comment


      • #4
        Originally posted by kafecadm View Post
        More like a design flaw than a bug if you ask me.
        Good point



        Dave
        Dave Prue
        Code Whisperer
        Lahar International Corp
        www.lahar.net

        Comment


        • #5
          Easy to solve...

          Okay, if bug or not, it's nice more when only do database changes when needed. So i did the follow.
          And of course: I not can use the current N:N relation, because this code belongs scriptcase. I made a new Field "DoubleSelect" and do some code around this.
          • Save the starting values from the Double Select Field in "onLoad" Event in a variable, [startValues]={myDoubleSelect}
          • On Update do following:
            Put Start Values and Current Values of the Double Select Field in arrays:
            $_startValues=array_filter(explode(";",[startValues]));
            $_endValues=array_filter(explode(";",{myDoubleSele ct}));
          • // Which Values we have to insert
            $_insertValues=array_values(array_diff($_endValues ,$_startValues));
            // Which Values we have to delete
            $_deleteValues=array_values(array_diff($_startValu es,$_endValues));
          • Do the insert SQL Statement. I use a foreach to make the insert commands.
          • Do the delete Statement like "DELETE from myTable WHERE leftkey={leftkey} AND rightkey IN ".implode(",",$_deleteValues).")";



          I think that's it. But inputs are welcome. Has anyone idea how to tell the scriptcase team to fix, sorry, make more beautiful this behavior?

          Comment


          • #6
            Hi
            can someone tell me, if scriptcase team work on that, when it will be fixed or what we have to do for fix it?

            I need N:N in many relations and my workaround above is not sexy like the implementation in scriptcase N:N could be.... so PLEASE, fix it.

            Thank's for the answer.

            Best regards
            Steve

            Comment


            • #7
              Originally posted by stephanw View Post
              Okay, if bug or not, it's nice more when only do database changes when needed. So i did the follow.
              And of course: I not can use the current N:N relation, because this code belongs scriptcase. I made a new Field "DoubleSelect" and do some code around this.
              • Save the starting values from the Double Select Field in "onLoad" Event in a variable, [startValues]={myDoubleSelect}
              • On Update do following:
                Put Start Values and Current Values of the Double Select Field in arrays:
                $_startValues=array_filter(explode(";",[startValues]));
                $_endValues=array_filter(explode(";",{myDoubleSele ct}));
              • // Which Values we have to insert
                $_insertValues=array_values(array_diff($_endValues ,$_startValues));
                // Which Values we have to delete
                $_deleteValues=array_values(array_diff($_startValu es,$_endValues));
              • Do the insert SQL Statement. I use a foreach to make the insert commands.
              • Do the delete Statement like "DELETE from myTable WHERE leftkey={leftkey} AND rightkey IN ".implode(",",$_deleteValues).")";



              I think that's it. But inputs are welcome. Has anyone idea how to tell the scriptcase team to fix, sorry, make more beautiful this behavior?
              Yes, Stephen, I can write the php to do the N:N myself, but then why did I buy ScriptCase?

              Dave
              Dave Prue
              Code Whisperer
              Lahar International Corp
              www.lahar.net

              Comment


              • #8
                Originally posted by stephanw View Post
                Hi
                can someone tell me, if scriptcase team work on that, when it will be fixed or what we have to do for fix it?

                I need N:N in many relations and my workaround above is not sexy like the implementation in scriptcase N:N could be.... so PLEASE, fix it.

                Thank's for the answer.

                Best regards
                Steve
                Send mail to bugs@scriptcase.net pointing to this thread.
                /Giuseppe

                Professional Scriptcase Services
                Some Customers opinions

                Comment


                • #9
                  Originally posted by stephanw View Post
                  Okay, if bug or not, it's nice more when only do database changes when needed. So i did the follow.
                  And of course: I not can use the current N:N relation, because this code belongs scriptcase. I made a new Field "DoubleSelect" and do some code around this.
                  • Save the starting values from the Double Select Field in "onLoad" Event in a variable, [startValues]={myDoubleSelect}
                  • On Update do following:
                    Put Start Values and Current Values of the Double Select Field in arrays:
                    $_startValues=array_filter(explode(";",[startValues]));
                    $_endValues=array_filter(explode(";",{myDoubleSele ct}));
                  • // Which Values we have to insert
                    $_insertValues=array_values(array_diff($_endValues ,$_startValues));
                    // Which Values we have to delete
                    $_deleteValues=array_values(array_diff($_startValu es,$_endValues));
                  • Do the insert SQL Statement. I use a foreach to make the insert commands.
                  • Do the delete Statement like "DELETE from myTable WHERE leftkey={leftkey} AND rightkey IN ".implode(",",$_deleteValues).")";



                  I think that's it. But inputs are welcome. Has anyone idea how to tell the scriptcase team to fix, sorry, make more beautiful this behavior?
                  Hello,

                  I've read your problem and I saw that it is delete mode 'cascade' data belonging to your table N: N, right? Due to "remove and insert" the data. But the principle that it is not a bug, but we will simulate the same way you use and check that will be necessary to make changes to the table insert mode N: N or not.

                  Thank you and sorry for any problem occurred.
                  Best regards,
                  Thomas Soares.
                  ScriptCase International.

                  Email: t.soares@scriptcase.net
                  Visit our Blog: http://www.scriptcase.net/blog/
                  Visit out fan page: http://www.facebook.com/Scriptcase

                  Comment


                  • #10
                    Originally posted by Thomas Soares View Post
                    Hello,

                    I've read your problem and I saw that it is delete mode 'cascade' data belonging to your table N: N, right? Due to "remove and insert" the data. But the principle that it is not a bug, but we will simulate the same way you use and check that will be necessary to make changes to the table insert mode N: N or not.

                    Thank you and sorry for any problem occurred.
                    Hi
                    i'am not understand what this have to do with the "Delete Mode". It also happens, when you have additional columns in the "middle table", then these information will be deleted every time i add a record.
                    I send a demo project to scriptcase yesterday and hope, this bug will be fixed soon. Because delete every record from a table to reinsert the same records plus the changes again make no sense to me.

                    Comment


                    • #11
                      Originally posted by stephanw View Post
                      Hi
                      i'am not understand what this have to do with the "Delete Mode". It also happens, when you have additional columns in the "middle table", then these information will be deleted every time i add a record.
                      I send a demo project to scriptcase yesterday and hope, this bug will be fixed soon. Because delete every record from a table to reinsert the same records plus the changes again make no sense to me.
                      This problem was already informed our development team so that it can be resolved soon, but I can not say it any specific date.

                      Sorry for any problems, and thanks for listening.
                      Best regards,
                      Thomas Soares.
                      ScriptCase International.

                      Email: t.soares@scriptcase.net
                      Visit our Blog: http://www.scriptcase.net/blog/
                      Visit out fan page: http://www.facebook.com/Scriptcase

                      Comment


                      • #12
                        Originally posted by Thomas Soares View Post
                        ... but I can not say it any specific date. Sorry for any problems, and thanks for listening.
                        Thank's for this information. I got the information, that the ticket is resolved allready. And now i'am waiting for the update in scriptcase? Because i need this fixed.

                        I hope, the changes will be included in the next update!

                        Best regards
                        Steve

                        Comment


                        • #13
                          Two updates later, i am waiting still for this fix...

                          Please give me a date for deploy this fix, the the ticket shows me, it is fixed allready. My work should go on.... but can't until this bug is deployed.

                          Thank's
                          Steve

                          Comment


                          • #14
                            Please give a release date....

                            Today, another "big" release happens in scriptcase... and i see nothing in the changelog that this bug is fixed.

                            Please give a date for release the allready made fix of this bug, so i can plan my work on my project, which need this bug fixed!

                            Best regards and thank's
                            Steve

                            Comment


                            • #15
                              May i ask again? When this fix will be deployed? The ticket is closed since weeks, but the deployment stocks? What happen's?

                              Best regards
                              Steve

                              Comment

                              Working...
                              X