Excel-like CRUD in PW using Handsontable

[kongondo]

In some previous posts, I demonstrated a simple proof-of-concept CRUD “application” for PW using jTable. I really wanted to use DataTables since it is older, wiser and with a huge fan base. One thing I wanted was for the CRUD system to be as simple as possible and possibly be Excel-like (more below). With DataTables plugins, you can perform stuff like inline-editing and Auto-fill. The latter works just like in Excel and allows you to quickly populate cells (quick copy). But that’s as far as it goes. Google led me to other Table management systems. Some of the front runners include jqGrid and SlickGrid. SlickGrid is nice and using virtual rendering can display millions of rows with ease. Its support for displaying data delivered on demand (Ajax/server) is minimal; it requires you to download all your data before it starts manipulating it. Someone has created an Excel-like extension for it allowing copy-pasting between SlickGrid and Excel. Awesome! But, it still didn't rock my boat completely, especially the Ajax content issue. Then I stumbled upon Handsontable, a new kid on the block that describes itself as “a minimalistic Excel-like data grid editor for HTML, JavaScript & jQuery”. Though relatively new, it is as Excel-like as you can get. You can copy-paste from/to Excel [single to multiple cells and multiple to multiple cells], use the usual shortcuts (Ctrl-A, Ctrl-X, Ctrl-C, Ctrl-V, Ctrl-Y, Ctrl-Z - i.e., undo/redo, etc., and tab navigation, etc.), use Auto-fill (vertically and horizontally), freeze cells/columns, make cells/columns read only, Right-click context menus, insert/delete rows and columns, perform inline-editing, etc. Double awesome!

Handsontable (HoT) is minimalistic but comes with a rich API. However, most of the implementation is down to you (sounds familiar?). It will render the Table and provide you with Methods/Events/Options to manipulate it. You just have to provide the data as an Array or JSON defining your columns and containing your data. 

Soma has a DataTable module that is currently read only; you cannot edit the displayed data directly in the table. It is also in lazy development . So, I started toying with the idea of using HoT as an Excel-like CRUD system (module) for PW. I wanted something that could quickly and easily do mass editing (Batcher-like). Imagine quickly creating basic pages/records by copy-pasting from Excel. Or quickly changing records by dragging (Auto-fill) or pasting new values over old ones. Well, the result can be seen in the screencast below.

I’d like to hear your thoughts whether this is something that I should consider developing into a module; something that could be used by Superusers. Depending on implementation, it could be used by other users as well. All HoT needs is data. What you do with that data is up to you (the developer) and your code! This can be very dangerous if not implemented correctly, of course! You could easily delete important records! However, HoT does not touch your database! Deletions, insertions, etc., will not alter your database unless you specifically write the PHP code to do that. All it does is send data to the server via a method your specify (post, get, Ajax, etc.), and even that is your implementation. You do not need to implement deletions or additions. You can use it for updating content only or as read only, but where’s the fun in that ?

In the screencast , apart from the table itself, all the other implementations are custom - the buttons, selects, auto-save (can be tricky? can DDOS your server?). The current implementation is not perfect; it is a demo after all. Certain stuff would need to be implemented differently  . I didn't want to spend too much time on something that would not be useful in the end. All CRUD operations are via PW API and/or vanilla PHP. Deleted pages are Trashed and not permanently deleted. If there is interest for a module of this kind, I would request all the help I can get to develop it please. I would put it up on Github of course. In the demo, this is running off Diogo’s ACP module.

Let me hear your thoughts please. Should a module like this see the light of day? Thanks

Wasn't sure in which forum to post this...

[bcartier]

Hi kongondo,

This is awesome! I would love to see a filter for the table that would accept a standard PW Selector rather than just a drop down for Templates. That would allow you to combine multiple templates in a result set, or get as granular as you need in terms of searching large data sets. 

Great work!

[kongondo]

Thanks bcartier.

Yes, I plan to have a filter (if this goes to production ) The filter would be trivial to implement I think - if not, I'll just pinch it off Wanze's Batcher

[Manol]

Hello kongondo.

    I don't like it, I just LOVE it.

[Martijn Geerts]

Think it will a great way to batch & import. Great work so far.

[Pete]

Love it

I was about to say that it wouldn't work for my idea in the other thread where I was talking about using something like this for repeater fields, but then I just saw the cell types on offer here and my jaw hit the flaw: http://handsontable.com/demo/renderers.html

What impresses me with the cell types is that the config data for select fields, checkboxes, datepickers and autocompletes etc can be read straight from your field's config data in PW so there is almost certainly a way of making that happen if I ever find the time.

So many possibilities here, and I love what you've done so far!

[kongondo]

@All thanks!

@Pete,

Ye, Marcin Warpechowski has done some amazing work with HoT. 

[Wanze]

This is really awesome!

Never heard of this plugin before.

I'm thinking of integrating Handsontable into Batcher, there are some other feature requests pending I wanted to integrate sooner or later.

Kongondo, are you already developing a separate module?

What do you think?

Cheers

[kongondo]

@Wanze,

Thanks. Ye, HoT it is really awesome . Yes, I am already working on a module for Handsontable but it is at a very early stage. In fact, I am basing my code on yours   (Batcher). Btw, your code has taught me a lot, thanks - well-written and commented and just makes me understand PW even better. 

Hmm, just wondering whether it makes sense to have two separate modules or one (Batcher) to rule them all! I have been planning to have my module handle both PW and external tables (data handling on demand basically), although that could add to the complexity. Like I said in the first post, I would need help in coding the module, especially settings/configuration to allow the user to enter options offered by HoT. It's also a good learning experience for me to better understand PW.

I suggest we mull this over a little and hear other thoughts as well (i.e. one module or two). ATM, I don't see any reason why these should not be combined but I would (if possible) want the ability to edit external tables. What is your opinion about this (external tables)?

So, in a nutshell, yes, I'd love to collaborate with you on this but let's make a final decision once we hear what a few others have to say...just to be sure we are considering all angles

[diogo]

A SuperBatcher! I love the idea

[Wanze]

 Btw, your code has taught me a lot, thanks - well-written and commented and just makes me understand PW even better.

Thanks! Glad it helps others

 Hmm, just wondering whether it makes sense to have two separate modules

or one (Batcher) to rule them all! I have been planning to have my

module handle both PW and external tables (data handling on demand

basically), although that could add to the complexity

With external tables you mean custom tables in the same database as Pw? So that one can select the table and columns to edit, e.g. map the columns to Pw-Fields and then handle the CRUD.

This sounds great, but IMO this would be a different module because the data is stored "outside" of Pw.

[kongondo]

Thanks! Glad it helps others

With external tables you mean custom tables in the same database as Pw? So that one can select the table and columns to edit, e.g. map the columns to Pw-Fields and then handle the CRUD.

This sounds great, but IMO this would be a different module because the data is stored "outside" of Pw.

I meant both actually - custom tables in PW DB and in external DBs. But I see your point now that I've thought about it; you are right, that should be a different module....

[Pete]

Hmm... well if they share most of the code then this might be a candidate for a module that contains the core functionality for CRUD and PW pages, then an add-on module for external DBs?

You wouldn't want to be duplicating too much code - have a look at dependencies if you've not read up on that already: http://processwire.com/talk/topic/778-module-dependencies/ - then you can fire up the main module from the external table version.

You might just find though that it is easier to make it as one module (I suspect this might be the case actually given the chance of code duplication).

[kongondo]

@Pete: Thanks for your thoughts.

@Wanze; I'll PM you later to discuss how we proceed, thanks.

[ryan]

This looks awesome, loved watching the video. Just let me know if there's anything I can do to support further development of this. 

[kongondo]

Hi all,

Just to let you know that Wanze and I are now working on this (or rather, he's doing most of the coding and am doing the talking ). We concluded it was best to (first) develop a PW Handsontable class that other modules can load, configure settings and use its methods to render CRUD tables. This way, the class can be integrated into other modules as required, e.g. Batcher and use all or only some of its methods, e.g. limited CRUD, readOnly, etc. At this stage, this means that the module is primarily for developers since there is no UI to configure it. However, once the class has been tested, a Process Module should follow. You can follow the project here: https://github.com/wanze/PwHandsontable. Suggestions welcome, thanks!

[Martijn Geerts]

Lookin' great so far. And about the coding & talkin:

 (or rather, he's doing most of the coding and am doing the talking  )

I like it that way, PLEASE stay talkin' on the forums too. (and Wanze coding !)

When Joss returns, we have a whole Social Media Platform going on right here.

Think this module gonna get really useful for lot's of things.  

[bcartier]

That's great news!

I'd love to see the Handsontable class implemented as a read-only InputField for Page Reference fields (as an alternative to AsmSelect etc). It would be great for searching and multi-selecting pages for references  - specifically when there are tons of potential pages.

...lots of possibilities with this one.

[Soma]

Also wanted to dive in and say hello. I like coffee.

Seriously great stuff, and you're right my DataTables module is only an example and it was a rough coding session never found any more love. But actually to just read and list data with quick edit link in a modal, which is nice for when editing sets of items, and I'm using it in some projects. It would also support inline editing and stuff but never found the energy and time to give it a shot. Making everything configurable and modular/abstract is quite some work, so all the love for people taking this "concept" further. I think there's a need for such things and I love seeing other people getting involved with PW module development. There's so many good things that can be done and PW makes it so easy.

But dude that Handsontable is a real beauty and looks like a great concept and API! Keep it up. Thanks for sharing. The right tools make a big difference.

If you need a beta tester...

[kongondo]

@Soma...Thanks! Well you and the other "usual suspects" are squarely to "blame" for slowly turning me into a coder ! I have recently had the "aha" moment regarding module development; I couldn't agree more - PW makes it so very easy to develop modules! The API is consistent and there's tools for almost everything (well-done Ryan!). Looking at the module code written by you guys now makes much more sense to me. Teppo's write-up on Inputfields and Fieldtypes was one of the tipping points for me; that plus I looked up some really nice and short PHP OOP tutorials. Suddenly, it all made sense . I feel like I've learnt a lot in such a short time, thanks to you guys leading the way . Anyway, I will be doing a write-up sometime about my experiences in module development so watch this space. 

About "energy", incidentally, I was thinking about that recently; about that time when you feel you've "coded it all" and need something different to excite you. Anyway, I digress. Thanks for offering to test. Looking forward to your suggestions

[SadlyMistaken]

incredible!!!

The perfect gift to processwire!!!

[pwFoo]

I need such a module to maintain a table of data. Most important feature I need are inline editable fields.

Any useable module available today or in the near future?

[ryan]

I need such a module to maintain a table of data.

You can use repeaters for smallish tables of data. In my own projects I usually create a custom Fieldtype/Inputfield for managing highly custom tables of data like rate tables and such. This is the most efficient way to do it, though not the simplest. But it may be simpler than you'd think. 

Most important feature I need are inline editable fields. Any useable module available today or in the near future?

Most inline editing tools are 3rd party javascript plugins. It is possible to use just about any of these with ProcessWire, especially for text-based fields. But as ProcessWire does not get involved with the markup on the front-end of your site, it's something that you would need to implement. ProcessWire's API makes it a relatively simple job, but it's still development.  You might also want to take a look at the Fredi  module, a nice approach to front-end editing, even if not inline editing. 

[pwFoo]

Thanks Ryan,

inline editor module does the job, but needs some modifications I'll do.

You write an answer at inline module support topic

[renobird]

In my own projects I usually create a custom Fieldtype/Inputfield for managing highly custom tables of data like rate tables and such. This is the most efficient way to do it, though not the simplest. But it may be simpler than you'd think. 

Ryan, any chance you would be willing to share a very basic example?

A "table" Fieldtype/Inputfield would be amazing.