CODEWITHSUNDEEP
grailsgrails-orm
s3c3t3
s3c3t3

Reputation: 363

Getting results with association that belongs only to a certain list of categories

I have two small tables like this:

Users:

+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Mike  |
|  3 | Smith |
|  4 | Kurt  |
|  5 | Tim   |
+----+-------+

Resources:

+----+------------+-------+---------+
| id |    name    | type  | user_id |
+----+------------+-------+---------+
|  1 | sunset     | text  |       1 |
|  2 | sunrise    | image |       2 |
|  3 | moon       | image |       1 |
|  4 | earth      | sound |       3 |
|  5 | clouds     | sound |       2 |
|  6 | tree       | image |       4 |
|  7 | flower     | text  |       4 |
|  8 | water      | text  |       4 |
|  9 | wind       | text  |       1 |
| 10 | animal     | image |       1 |
| 11 | open_door  | sound |       5 |
| 12 | close_door | sound |       5 |
+----+------------+-------+---------+

Given this, we can see that

John owns resources of type text and image Mike owns resources of type image and sound Smith owns resources of type sound Kurt owns text and images Tim owns only sounds

The problem is: I want to retrieve the users that owns exclusively text and/or images, if the user owns any other type of resource that is not text or images, the user should not be fetched in the result set.

Is there any way to accomplish this with criteria or HQL?

Currently, my query is returning users that own text or images, but they also own other type of resources:

+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Mike  |
|  4 | Kurt  |
|  5 | Tim   |
+----+-------+

The result set should only show John and Kurt, since they are the only ones that own text and/or images.

Upvotes: 1

Views: 41

Answers (1)

Burt Beckwith
Burt Beckwith

Reputation: 75671

Assuming your user domain class looks something like

class User {
   String name
}

and the resource class looks something like

class Resource {
   String name
   String type
   User user
}

then you can use this HQL:

User.executeQuery("""
   select distinct r.user from Resource r
   where (r.type='image' or r.type='text')
     and r.user not in (
         select distinct r.user from Resource r where r.type<>'image' and r.type<>'text'
     )""")

Upvotes: 1

Related Questions