Proposal: Virtual Domains Plugin

[Nathan Wells]

I have a use case where it makes a lot of sense to use a database query to generate a list of objects that can be iterated over to generate the view. The objects which are iterated over are a combination of 3 objects. The query uses a left join and some fancy footwork in the select statement to generate a flag based on the existence of data in a column. It would be nice, for a number of reasons (not in the least, testing), if the records generated by the query were, in fact, domain objects.

Reading on the internet, this seems to not be a unique problem:

http://grails.1312388.n4.nabble.com/Database-Views-and-Grails-Domain-Objects-td1389019.html

http://grails.1312388.n4.nabble.com/Grails-Domain-Class-and-Database-View-td3681188.html

http://stackoverflow.com/questions/425294/sql-database-views-in-grails

http://stackoverflow.com/questions/430004/grails-domain-class-without-id-field-or-with-partially-null-composite-field

I'm wondering just how many people deal with this. If it is many, I'm thinking it might be fruitful to create a Virtual Domains plugin, which would allow you to define a domain in terms of the query to be run (SQL, HQL, Grails "where", or otherwise) against objects, or tables themselves, depending on the richness of the query you need to run. So, I'm thinking you'd be able to do this:

@Virtual

class PendingAssignments {

  

  String aName

  String bStatus

  Date bDate

  String cApproval

  static mapping = {

    query hql: """

select

    a.name as aName,

    b.status as bStatus,

    b.date as bDate,

    c.approval as cApproval,

from 

    Assignee as a,

    Booth as b,

    Something as c,

where

    b.status == Pending

"""

  }

}

Hopefully this makes sense... if not, please let me know. There's obviously a lot of nitty gritty details to work out, but I think this could be really useful. Or, at least, it would let me write code that I feel more comfortable with.

Nathan Wells

[Graeme Rocher-4]

Could be useful yes

Cheers

On Thu, Jul 12, 2012 at 7:22 AM, Nathan Wells <[hidden email]> wrote:

> I have a use case where it makes a lot of sense to use a database query to
> generate a list of objects that can be iterated over to generate the view.
> The objects which are iterated over are a combination of 3 objects. The
> query uses a left join and some fancy footwork in the select statement to
> generate a flag based on the existence of data in a column. It would be
> nice, for a number of reasons (not in the least, testing), if the records
> generated by the query were, in fact, domain objects.
>
> Reading on the internet, this seems to not be a unique problem:
> http://grails.1312388.n4.nabble.com/Database-Views-and-Grails-Domain-Objects-td1389019.html
> http://grails.1312388.n4.nabble.com/Grails-Domain-Class-and-Database-View-td3681188.html
> http://stackoverflow.com/questions/425294/sql-database-views-in-grails
> http://stackoverflow.com/questions/430004/grails-domain-class-without-id-field-or-with-partially-null-composite-field
>
> I'm wondering just how many people deal with this. If it is many, I'm
> thinking it might be fruitful to create a Virtual Domains plugin, which
> would allow you to define a domain in terms of the query to be run (SQL,
> HQL, Grails "where", or otherwise) against objects, or tables themselves,
> depending on the richness of the query you need to run. So, I'm thinking
> you'd be able to do this:
>
> @Virtual
> class PendingAssignments {
>
>   String aName
>   String bStatus
>   Date bDate
>   String cApproval
>
>   static mapping = {
>     query hql: """
> select
>     a.name as aName,
>     b.status as bStatus,
>     b.date as bDate,
>     c.approval as cApproval,
> from
>     Assignee as a,
>     Booth as b,
>     Something as c,
> where
>     b.status == Pending
> """
>   }
> }
>
> Hopefully this makes sense... if not, please let me know. There's obviously
> a lot of nitty gritty details to work out, but I think this could be really
> useful. Or, at least, it would let me write code that I feel more
> comfortable with.
>
> Nathan Wells

--
Graeme Rocher
Grails Project Lead
SpringSource - A Division of VMware
http://www.springsource.com

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Jonathan Stott-5]

This would be really useful. I have been meaning to give something similar a go myself since last year!

The specific use that we would have would be when displaying a table of values which are calculated or span a number of domain objects. We've done this in the past by creating actual domain classes to cache the results but this isn't particularly flexible. If we could create a virtual domain class which may or may not be cached to the database then it would make it much easier to sort and filter the values. Even better if it always ensures that the data is up-to-date without having to recalculate/requery everything.

Jonathan

On 12 July 2012 06:22, Nathan Wells <[hidden email]> wrote:

I have a use case where it makes a lot of sense to use a database query to generate a list of objects that can be iterated over to generate the view. The objects which are iterated over are a combination of 3 objects. The query uses a left join and some fancy footwork in the select statement to generate a flag based on the existence of data in a column. It would be nice, for a number of reasons (not in the least, testing), if the records generated by the query were, in fact, domain objects.

Reading on the internet, this seems to not be a unique problem:

http://grails.1312388.n4.nabble.com/Database-Views-and-Grails-Domain-Objects-td1389019.html

http://grails.1312388.n4.nabble.com/Grails-Domain-Class-and-Database-View-td3681188.html

http://stackoverflow.com/questions/425294/sql-database-views-in-grails

http://stackoverflow.com/questions/430004/grails-domain-class-without-id-field-or-with-partially-null-composite-field

I'm wondering just how many people deal with this. If it is many, I'm thinking it might be fruitful to create a Virtual Domains plugin, which would allow you to define a domain in terms of the query to be run (SQL, HQL, Grails "where", or otherwise) against objects, or tables themselves, depending on the richness of the query you need to run. So, I'm thinking you'd be able to do this:

@Virtual

class PendingAssignments {

  

  String aName

  String bStatus

  Date bDate

  String cApproval

  static mapping = {

    query hql: """

select

    a.name as aName,

    b.status as bStatus,

    b.date as bDate,

    c.approval as cApproval,

from 

    Assignee as a,

    Booth as b,

    Something as c,

where

    b.status == Pending

"""

  }

}

Hopefully this makes sense... if not, please let me know. There's obviously a lot of nitty gritty details to work out, but I think this could be really useful. Or, at least, it would let me write code that I feel more comfortable with.

Nathan Wells

--
Dr Jonathan Stott
Development Team Leader

Axon Limited
"Connecting Healthcare"
2 Venture Road
University of Southampton Science Park
Southampton SO16 7NP

Tel: [phone]
Mobile: [phone]

www.axonuk.com

This message and the information contained therein is intended only for the use of the person(s) to whom it is addressed.  It may contain information that is confidential or privileged within the meaning of applicable law.  If you are not the intended recipient, please contact the sender as soon as possible and delete this message from your system; please also note that any use or disclosure of the information contained in this message (including any attachments) is strictly prohibited and may be unlawful.  This correspondence may include examples or terms based upon current assumptions with any costs shown excluding VAT and cannot be considered as a quotation, offer or commitment in any way.  Whilst reasonable precaution has been taken to minimise the risk, the contents or an attachment to this e-mail may have become corrupted during transmission or contain viruses, we cannot accept liability in this regard, and you should carry out your own virus checks.

Axon Limited is a Company registered in England and Wales number 5728502. Registered Office: Imperial House, 18-21 Kings Park Road, Southampton SO15 2AT

[Nathan Wells]

Jonathan, Graeme,

Thanks for your support. Jonathan actually brings up a good point, and I think I'd want to make the strategy here a matter of creating and managing database views from the virtual domain queries. I'm not sure on the feasibility of any of this yet, but I actually forgot to bring up the piece about views in my original message :)

Nathan Wells

On Thu, Jul 12, 2012 at 2:13 AM, Jonathan Stott <[hidden email]> wrote:

This would be really useful. I have been meaning to give something similar a go myself since last year!

The specific use that we would have would be when displaying a table of values which are calculated or span a number of domain objects. We've done this in the past by creating actual domain classes to cache the results but this isn't particularly flexible. If we could create a virtual domain class which may or may not be cached to the database then it would make it much easier to sort and filter the values. Even better if it always ensures that the data is up-to-date without having to recalculate/requery everything.

Jonathan

On 12 July 2012 06:22, Nathan Wells <[hidden email]> wrote:

I have a use case where it makes a lot of sense to use a database query to generate a list of objects that can be iterated over to generate the view. The objects which are iterated over are a combination of 3 objects. The query uses a left join and some fancy footwork in the select statement to generate a flag based on the existence of data in a column. It would be nice, for a number of reasons (not in the least, testing), if the records generated by the query were, in fact, domain objects.

Reading on the internet, this seems to not be a unique problem:

http://grails.1312388.n4.nabble.com/Database-Views-and-Grails-Domain-Objects-td1389019.html

http://grails.1312388.n4.nabble.com/Grails-Domain-Class-and-Database-View-td3681188.html

http://stackoverflow.com/questions/425294/sql-database-views-in-grails

http://stackoverflow.com/questions/430004/grails-domain-class-without-id-field-or-with-partially-null-composite-field

I'm wondering just how many people deal with this. If it is many, I'm thinking it might be fruitful to create a Virtual Domains plugin, which would allow you to define a domain in terms of the query to be run (SQL, HQL, Grails "where", or otherwise) against objects, or tables themselves, depending on the richness of the query you need to run. So, I'm thinking you'd be able to do this:

@Virtual

class PendingAssignments {

  

  String aName

  String bStatus

  Date bDate

  String cApproval

  static mapping = {

    query hql: """

select

    a.name as aName,

    b.status as bStatus,

    b.date as bDate,

    c.approval as cApproval,

from 

    Assignee as a,

    Booth as b,

    Something as c,

where

    b.status == Pending

"""

  }

}

Hopefully this makes sense... if not, please let me know. There's obviously a lot of nitty gritty details to work out, but I think this could be really useful. Or, at least, it would let me write code that I feel more comfortable with.

Nathan Wells

--
Dr Jonathan Stott
Development Team Leader

Axon Limited
"Connecting Healthcare"
2 Venture Road
University of Southampton Science Park
Southampton SO16 7NP

Tel: [phone]
Mobile: [phone]

www.axonuk.com

This message and the information contained therein is intended only for the use of the person(s) to whom it is addressed.  It may contain information that is confidential or privileged within the meaning of applicable law.  If you are not the intended recipient, please contact the sender as soon as possible and delete this message from your system; please also note that any use or disclosure of the information contained in this message (including any attachments) is strictly prohibited and may be unlawful.  This correspondence may include examples or terms based upon current assumptions with any costs shown excluding VAT and cannot be considered as a quotation, offer or commitment in any way.  Whilst reasonable precaution has been taken to minimise the risk, the contents or an attachment to this e-mail may have become corrupted during transmission or contain viruses, we cannot accept liability in this regard, and you should carry out your own virus checks.

Axon Limited is a Company registered in England and Wales number 5728502. Registered Office: Imperial House, 18-21 Kings Park Road, Southampton SO15 2AT

[bksaville]

It seems that you could do this already by simply defining the views and
then creating domain classes to model the data in them.  Of course, being
able to create these views from a domain object and especially generating
them from HQL or especially criteria would be extremely useful.

I have need of this as well, or at least I want it :)

-Brian

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[lucastex]

Great.

I solve this today by creating domain classes and using formulas to map fields, but this approach seems even better.

[]s,

 

Lucas Teixeira

2012/7/12 Brian Saville <[hidden email]>

It seems that you could do this already by simply defining the views and
then creating domain classes to model the data in them.  Of course, being
able to create these views from a domain object and especially generating
them from HQL or especially criteria would be extremely useful.

I have need of this as well, or at least I want it :)

-Brian

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[johnrengelman]

I've long wished for better support for thing like database views through GORM. This would be awesome. I envision it as a new base Grails artifact (like domain, controller, service, and tag lib). Would these virtual objects be read-only? If this was a new artifact and it's supposed to be read-only, then the AST could just not put methods like "save" on it.

I'm looking forward to see what you come up with.

--

John

On Thursday, July 12, 2012 at 9:33 AM, Lucas F. A. Teixeira wrote:

Great.

I solve this today by creating domain classes and using formulas to map fields, but this approach seems even better.

[]s,

 

Lucas Teixeira

2012/7/12 Brian Saville <[hidden email]>

It seems that you could do this already by simply defining the views and
then creating domain classes to model the data in them.  Of course, being
able to create these views from a domain object and especially generating
them from HQL or especially criteria would be extremely useful.

I have need of this as well, or at least I want it :)

-Brian

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email