0

I am trying to complete a criteria query in Grails that sometimes has to refer to an unassociated table, therefore using a detached query. Depending on a passed in parameter, I need to perform a different subquery.

I have a Customer domain, which is what I'm creating the criteria based on as there are options available to query against the customer fields, name etc..

It should be possible for the user to query against the customer by the statusof customer orders, this is where my trouble begins. There is no hasMany between customers and orders but there is a join table which can be queried using a detached query.

So there is also an Order and a CustomerOrder join table.

The Order table has a status field, which can contains an Enum with possible values of null, OPEN or CLOSED.

I want to give the user the ability (via a select menu) to choose an order status option of either 'No Orders', 'Open Orders', 'Closed Orders' and 'Open or Closed Orders'

translating this to a detached criteria is proving tricky, if anyone can help that would be great.

to get started I have... but its not complete and I don't think I'm going in the right direction with the if block.

def results = Customer.createCriteria().list {

  /* other restrictions omitted */

  // now the order status test
  if (params.orderStatus) {
    if (params.orderStatus == 'NO_ORDERS') {

    } else if (params.orderStatus == 'CLOSED_ORDERS') {
    } else if (params.orderStatus == 'OPEN_ORDERS') {
    } else if (params.orderStatus == 'OPENORCLOSED_ORDERS') {
      def sub = DetachedCriteria.forClass(CustomerOrder.class, 'co').with {
        and {
          order {
            eq('status', params.orderStatus)
          }
          client {
            add(Restrictions.eqProperty('this.id', 'id'))
          }
        }
        //setProjection(Property.forName('id'))
      }
      add(Subqueries.propertyIn('id', sub))
    }
  }
}
David Brown
  • 3,021
  • 3
  • 26
  • 46
  • This is typically when I would resort to HQL as it makes more sense to me in situations like this. It supports joining objects that don't have a defined relation – James Kleeh Dec 09 '15 at 17:49
  • All of the GORM querying methods (Where queries, Criteria queries, and HQL) rely on domain class associations to join the database tables. From my understanding, you have two domain classes: `Customer` and `Order`. And a table without a domain class: `CustomerOrder`. Is this correct? – Emmanuel Rosa Dec 09 '15 at 18:07
  • @EmmanuelRosa Your first statement is not correct. See my comment above – James Kleeh Dec 09 '15 at 20:14
  • 1
    Do you have an HQL example of a join without an association? – Emmanuel Rosa Dec 09 '15 at 20:19

0 Answers0