1

I have two tables, but they are installed as plugin in Grails.

Columns in T1 are: a1, b1, c1, d1

Columns in T2 are: a2, b2, c2, d2

I need to select columns a*, b*, c*, d* (=1,2) from both tables in a controller as union and sort all of them by the column d, how can I do that?

Furthermore, how can the pagination work as treating about result as a single table?

Pls help. Appreciate!!

Kevin
  • 2,191
  • 9
  • 35
  • 49
  • You need exactly the same approach as here: http://stackoverflow.com/questions/425294/sql-database-views-in-grails – Victor Sergienko Jul 02 '11 at 09:54
  • Tahnks Victor. But I am wondering how can i do pagination since the results that return from db doesn't support list() function. – Kevin Jul 05 '11 at 14:33
  • I have implemented something like this: http://stackoverflow.com/questions/6598132/question-about-union-two-tables-with-plain-sql-in-grails-and-pagination ... but I donn't think its a good way, would you mind to take a look at it? thanks!! – Kevin Jul 06 '11 at 14:34

2 Answers2

0

Not sure if this is helpful or not. We hit a similar (though not exact) problem in the past, and we solved it by creating a view. You can create the view to do your union and select, and then create the new domain class that maps to the view.

You won't be able to use grails auto create for the table, which is another limitation.

Steven Mastandrea
  • 2,752
  • 20
  • 26
  • Thanks for your explanation. But I can't edit the domain class which is already in the plugin. So, u mean I should create my own domain class then select this in the controller? Would you mind to give me some more hint about this? Thanks. – Kevin Jul 01 '11 at 14:21
0

On pagination:

  1. groovy.sql.Sql rows() and eachRow() have 2nd and 3rd parameters max and offset that you can paginate with all like in ordinary list.

  2. Retrieve total count with another SQL query or some other way. Construct a PagedResultList from a data page rows() and int totalCount - and you got an object that you can use as a model.

Victor Sergienko
  • 13,115
  • 3
  • 57
  • 91
  • Would you mind to take a look at my solution? http://stackoverflow.com/questions/6598132/question-about-union-two-tables-with-plain-sql-in-grails-and-pagination Thanks!! – Kevin Jul 06 '11 at 14:52