CODEWITHSUNDEEP
grailsjoinhqlgrails-orm
user2181809
user2181809

Reputation: 21

A oneToMany join in Grails using findAll or HQL

I'm new to Groovy and HQL querying but I can't find a solution to this anywhere so it's driving me nuts.

I have two Domain classes which have a one to many relationship defined (a user can have many companies) and I effectively need to do (what would traditionally be called) 'a table join' but clearly with objects.

The classes go like this:

class User {
    transient springSecurityService

    static hasMany = [company: Company]

    String username
    String password
    boolean enabled
    boolean accountExpired
    boolean accountLocked
    boolean passwordExpired
    ...
    ...
    ...
}

... and the company class

class Company {
    static scaffolding = true

    String name
    String address1
    String address2
    String address3
    String address4
    String postCode
    String telephone
    String mobile // mobile number to receive appointment text messages to
    String email // email address to receive appointment emails to  

    static hasMany = [staff: Staff]
    static belongsTo = [user: User]

    ...
    ...
    ...
}

Gorm has created an user_id field within the company table but any attempt to use this in a query returns an error.

So how would I do something like:

select * from user u, company c, where u.id = c.user_id;

What is the best way to do this?

Upvotes: 0

Views: 4100

Answers (1)

dmahapatro
dmahapatro

Reputation: 50245

You can effectively use join on the association something like:

HQL
select * from User as u inner join fetch u.company as companies where u.id = ?

Note that using fetch in the query would eagerly fetch the associated collections of companies for a user

findAll() 

User.findAll("from User as u inner join fetch u.company as companies where u.id = ?", [1])

Benefit of using findAll instead of HQL is that you can easily implement pagination something like:

User.findAll("from User as u inner join fetch u.company as companies where u.accountExpired = true", [max: 10, offset: 5])

To get a concrete implementation and really understand things in details I would insist to have a look at findAll and HQL associations.

Upvotes: 4

Related Questions