Best Approach Viewing & Editing Single Database Record Twice

Restful Web Services

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

rf1234

"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 );
    }
 })

rf1234

And here is an important hint for left join editing:
https://datatables.net/forums/discussion/46120/leftjoin-table-not-updating-after-edit#latest

allan

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 Services

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