How to create sub query in Grails

Question

I use Grails 3.2.0.M2 and PostgreSQL 9.4.1.

I'm creating Web API which returns sales data as JSON. Url is like this http //localhost/sales?userId=001&limit=10

There are three domain classes that are sales, sales details and item domain class as below.

class Sales {
    String id
    String userId

    static hasMany = [salesDetail: SalesDetail]
}

class SalesDetail {
    String id
    int count
    int status

    static belongsTo = [sales: Sales, item: Item]
}

class Item {

    String id
    String itemName
    int price
}

I want to join these domain and retrieve data by conditions and limit on sales row count. For example, if I get http //localhost/sales?limit=2 in the case that domain data are as below.

Sales

id       userId 
sales001 user001
sales002 user002

SalesDetails

id              userID  salesId  itemId  count status
salesDetails001 user001 sales001 item001 1     1     
salesDetails002 user001 sales001 item002 2     1     
salesDetails003 user002 sales002 item001 1     1     
salesDetails004 user002 sales002 item002 3     1     

Item

id      itemName price
item001 book     100  
item002 cd       200

I want to get a JSON which contains two sales domain data.

{
    "sales" : [
    {
        "id": "sales001",
        "userId": "user001",
        "salesDetails": [
        {
            "salesId": "sales001",
            "itemId": "item001",
            "itemName": "book",
            "count": 1
        },
        {
            "salesId": "sales001",
            "itemId": "item002",
            "itemName": "cd",
            "count": 2
        }
        ] 
    },   
    {
        "id": "sales002",
        "userId": "user002",
        "salesDetails": [
        {
            "salesId": "sales002",
            "itemId": "item001",
            "itemName": "book",
            "count": 1
        },
        {
            "salesId": "sales002",
            "itemId": "item002",
            "itemName": "cd",
            "count": 3
        }
        ]
    }
    ]
}

If in SQL, I think that a query is like below.

select * from sales s1
where exists (
select s2.id from sales s2
inner join sales_details sd on s2.id = sd.sales_id
inner join item i on sd.item_id = i.id where s1.id = s2.id
where i.name = 'book' and s1.id = s2.id)  // search conditions on    Sales, SalesDetails, or Item
limit 2; // limit Sales

I read http://gorm.grails.org/6.0.x/hibernate/manual/index.html#criteria, but I don't know how to join three table and limit. How to create query in Grails?

You can use hql and tailor it close to your sql query : http://stackoverflow.com/questions/39453576/grails-2-5-1-hibernate-3-criteria-multiple-joins-to-same-table/39468326#39468326 — V H, Sep 14 '16 at 09:18

susi · Accepted Answer · 2016-09-15T09:07:47.943

0

I'm a bit confused about your id-definition as String.

You would have to define in this case the id with the following in the domain-class mapping:

static mapping = {
    id generator: 'assigned'
}

and provide the id by yourself.

the solution for your other question could be:

    def query = Sales.where {
        salesDetail {
            item.itemName == 'Book'
        }
    }

    respond query.list([sort: 'userId', max: 2])

You could also try to limit with:

respond query.list([sort: 'userId'])[0..1]

edited Sep 15 '16 at 09:07

answered Sep 14 '16 at 15:44

susi

493
4
13

Tanky you for aswering my question! > I'm a bit confused about your id-definition as String. Sorry. I forgot to write my static mapping. static mapping = { id generator: 'uuid' } – fanfanta Sep 15 '16 at 05:27
I've tried your answer and gotten the executed query like [select ... from sales this_ inner join sales_detail sales_details1_ on this_.id=sales_details1_.sales_id inner join item item1_ on sales_details1_.item_id=item1_.id where item1_.item_name = ? limit ?] I could join three tables, but limit clause works on joined tables. It limit SalesDetail row count. Is there way to limit Sales row count like above JSON? – fanfanta Sep 15 '16 at 05:42
You could try (but I think suboptimal because it limits not the query, only the resulted List) with **respond query.list([sort: 'userId'])[0..1]** – susi Sep 15 '16 at 09:08
If that's nothing, that helps, you even have the possibility to call native SQL, like described here: http://mrhaki.blogspot.de/2014/03/grails-goodness-using-groovy-sql.html – susi Sep 15 '16 at 09:35

How to create sub query in Grails

1 Answers1