What will be GORM(grails) criteria below's SQL?

user_6002139 0
Viewed 73

What will be grails GORM criteria below's SQL

Orders table:

  • OrderID
  • CustomerID
  • OrderDate

Customer table:

  • CustomerID
  • CustomerName
  • ContactName
  • Country

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:strong text

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

the Sql will produce those data with those column

  • OrderID
  • CustomerName
  • OrderDate

GORM MODEL:

class Order{

Long orderId
Long customerId
def orderDate
}

class Customer{

Long customerID
Long customerName
def contactName
def country
}
grails-orm grails hibernate sql
1 Answers

I am making some guesses to create an object model that matches the persistence model that you describe there.

class Customer {
    String customerName
    String contactName
    String country

    static mapping = {
        table 'Customers'
        version false
        id column: 'CustomerID'
        customerName column: 'CustomerName'
        contactName column: 'ContactName'
        country column: 'Country'
    }
}

class Order {
    Customer customer
    Date orderDate

    static mapping = {
        table 'Orders'
        version false
        id column: 'OrderID'
        customer column: 'CustomerID'
        orderDate column: 'OrderDate'

    }
}

Execute a query like this:

Order.findAll {
    createAlias 'customer', 'cust'
    projections {
        property 'id'
        property 'orderDate'
        property 'cust.customerName'
    }
}

That will result in SQL like this:

select this_.OrderID as y0_, this_.OrderDate as y1_, cust1_.CustomerName as y2_ from Orders this_ inner join Customers cust1_ on this_.CustomerID=cust1_.CustomerID
user_35335840
Related