Using Groovy to sort HTML Table by lastModified() dates

Question

I have a groovy script that pulls a number of fields from a MYSQL database and inserts the data into an HTML table, along with this I find the lastModified() date of the files that are pulled into the database.

I found a fairly similar topic: "Sorting files by lastModified()", however my problem is that I never actually get the data into a map or list, instead I build the xml and call the query using sql.eachRow(query) to insert the proper fields into the table and then find the lastModified() date within the xml itself.

I can't do the sort in my query because I have to get the lastModified() date from the groovy script. So I have two questions, is it possible to do a sort by last modified date using the set up in my code that I posted? And if so where would I need to actually perform the sort so that it is sorted correctly on the HTML table? I should say I am very new to Groovy and Java and am terrible when it comes to trying to figure out these kinds of things, so any hints as to where you found this information would be great.

Similar topics but ones that are not exactly what I am looking for:

How to sort rows of HTML table that are called from MySQL <-- really looking at this but it is in php
http://groovy.329449.n5.nabble.com/Sorting-files-by-lastModified-td362691.html <--this but trying to sort a table
http://www.kryogenix.org/code/browser/sorttable/#totalsrows <--this would be nice but is it possible in groovy?

My code, I had to change some things:

sql = Sql.newInstance("location of database", "username", "password", "driver")
writer = new StringWriter()
def xml = new MarkupBuilder(writer)
rptDate = new java.util.Date()

query = 
"""
query stuff
"""
xml.html(){
xml.head(){
xml.title("Title")
xml.body(){
    xml.h1("Title")
    xml.table(border:1, cellpadding:5){
        xml.tr(){
            xml.th("ID")
            xml.th("Date Added")
            xml.th("Hospital Name")
            xml.th("Total Daily Clients")
            xml.th("Total Daily Pets")
            xml.th("Last Upload Date")//lastModified() date

            }//end headings

//insert data from query into each row of the table
    sql.eachRow(query)
      {row ->
         xml.tr(align:'center'){
            xml.td("${row.ID}")
            xml.td("${row.DateAdded}")
            xml.td("${row.HospitalName}")
            xml.td("${row.TotalDailyClients}")
            xml.td("${row.TotalDailyPets}")
                        //find lastModified() dates for incoming files and format them
            mod = new File("/home/me/folderforfiles/${row.ID}.zip").lastModified()
            fd = new Date(mod).format("EEE MMM dd hh:mm:ss a yyyy")
                       //insert into table
            xml.td(fd)
        }//end table data
    }//end loop
}//end table
}//end body
}//end title
}//end html
println writer.toString()

score 2 · Accepted Answer · answered Aug 03 '11 at 19:45

If you load the SQL results into a List first, then you can sort this map, and iterate through it to generate your XML:

sql = Sql.newInstance("location of database", "username", "password", "driver")
writer = new StringWriter()
def xml = new MarkupBuilder(writer)
rptDate = new java.util.Date()

query = 
"""
query stuff
"""

// Load the results into a list
List rows = sql.rows( query )

// Then manipulate the list to add the mod and fd fields
rows.collect { 
  mod = new File("/home/me/folderforfiles/${row.ID}.zip").lastModified()
  fd  = new Date(mod).format("EEE MMM dd hh:mm:ss a yyyy")
  it << [ mod:mod, fd:fd ]
}

// Then sort it based on this field
rows = rows.sort { it.mod }

xml.html(){
  head {
    title "Title"
  }
  body {
    h1 "Title"
    table(border:1, cellpadding:5) {
      tr {
        th "ID"
        th "Date Added"
        th "Hospital Name"
        th "Total Daily Clients"
        th "Total Daily Pets"
        th "Last Upload Date"
      } //end headings
      rows.each { row ->
        tr( align:'center' ) {
          td row.ID
          td row.DateAdded
          td row.HospitalName
          td row.TotalDailyClients
          td row.TotalDailyPets
          td row.fd
        }//end table data
      }//end loop
    }//end table
  }//end body
}//end html
println writer.toString()

I haven't tried that, but it should do what you want to do... Let me know if you get any errors, and I'll sort them out...

Of course, loading all the rows into memory will not work if you have thousands and thousands of rows... If that is the case, then you should store the lastModified Date in the database, and sort the results using the sql query

Thanks a lot, I feared I would have to do something like this, just for the sake of clarity do I need to append mod to the list as well? I ask because I am using mod to get the lastModified time and fd to convert those times into the proper datetime format which is all I really want to display. Sorry I can't vote this up I apparently do not have enough reputation. — ThisIsImpossible, Aug 03 '11 at 19:57
Because you want to sort by mod, but then display fd, so you need mod in the list if maps. You could avoid sticking fd in there, and just do the conversion to string in the loop based on row.fd — tim_yates, Aug 03 '11 at 20:01
Ah got it, so the way I formatted the date is unsortable then. — ThisIsImpossible, Aug 03 '11 at 20:05
Yeah, string based sorting for this sort of thing isn't going to work, unless you change to something like a yyyy-mm-dd hh:mm:ss style (which is then arguably less human friendly) — tim_yates, Aug 03 '11 at 20:09

Using Groovy to sort HTML Table by lastModified() dates

1 Answers1