Best Approach Viewing & Editing Single Database Record Twice

Best Approach Viewing & Editing Single Database Record Twice

Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2
edited July 2018 in Editor

I have a single table t_bookings which contains a list of reservations. Each reservation has an arrival date and a departure date. I need to create a datatables view which is a list containing all arrivals and all departures, i.e. I need to show each individual database record twice in my view. I also need to be able to edit the database record.

I am stuck as to how to approach this?

Ideas:
1. Create a VIEW in MySQL which creates the data in the way I need it. However, if I create a 'VIEW' will I still be able to carry out some editing? I don't need to create any records, just add. I know I cannot edit a VIEW directly but if I use a LEFT JOIN somehow can I then edit?
2. Create a new table called t_dates which contains a list of days in the year and then LEFT JOIN onto this using start date to date and end date to date, like this ->leftJoin( 't_booking', 't_booking.dFromDate', '=', 't_date.dDate' ) and ->leftJoin( 't_booking', 't_booking.dToDate', '=', 't_date.dDate' ). However, this wont give me a unique join ID so I assume the editor will fail?

Any ideas greatly appreciated!

Chris

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2018

    "I don't need to create any records, just add."

    You probably mean "edit"?!

    If that is true then my suggestions is this:

    Create a view with UNION ALL and select your list of arrival and departure dates with that view. Display this list in the data table.
    Something like:

    SELECT 
    arrival_date as date, 
    "arrivalDate" as type 
    FROM t_bookings 
    UNION ALL 
    SELECT departure_date as date, 
    "departureDate" as type 
    FROM t_bookings 
    ORDER BY date, type
    

    In your Editor Instance you would need to have the view as the primary table and LEFT JOIN to your real table. Use "->set (false)" for your view fields at the back end.

    In your Editor Javascript instance based on the table record selected display the right field to be edited, either arrival or departure date depending on the record selected for editing. These fields must be the table fields not the view field "date".
    Use "type" as hidden Editor field in Javascript so that you have it available to check its value.

    Something like this:

    fields: [{ .....
    
    {
            type: "hidden",
            name: "type"
        }
    
    yourEditor.on('open', function (e, mode, action) {
        if ( action === 'edit) {
            if ( this.val("type") === "departureDate" )  ) {
                this.set( {'arrival_date': ''} )
                    .hide(['arrival_date']);
                    .show(['departure_date']);
            } else {
                this.set( {'departure_date': ''} )
                    .hide(['departure_date']);
                    .show(['arrival_date']);
            }
        }
    

    At the back end you would need to use something like this to make sure the right field gets edited:

    ->on('preEdit', function ( $editor, $id, $values ) {
        if ($values['t_bookings']['arrival_date'] <= '') {
           $editor->field('t_bookings.arrival_date')->set( false );
        }
        if ($values['t_bookings']['departure_date'] <= '') {
           $editor->field('t_bookings.departure_date')->set( false );
        }
     })
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    How about loading up the table as normal with one row in the database being one row in the DataTable, but hidding some of the columns, and displaying them in a details row. You could have them show automatically, style them to match the parent row and use columns as well.

    Allan

  • Restful Web ServicesRestful Web Services Posts: 202Questions: 49Answers: 2

    Hi rf1234 and Allan,

    Thanks both for your useful ideas. I probably would have gone with the VIEW approach to start with, however the specification has changed now and I no longer need to show both arrival and departure on the same view!

    Thanks again

    Chris

This discussion has been closed.