0

I am following this link to bring some positive changes regarding performance in my app.

I have following two classes

class Topic {

    String title
    String body
    String type
    User user

    static mapping = {
        body type:"text"
    }
}

and

class Follower {
    Topic topic
    User user
    static constraints = {
    }
}

Keeping above two classes in mind, is it possible to write equivalent criteria for following query?

SELECT title,follower.user_ID,topic.id  FROM TOPIC 
left outer join follower on topic.id = follower.topic_id
where follower.user_id = 1 or follower.user_id is null;

I came up with following criteria which fails because there is neither reference of follower in topic nor I could find a way to swing this criteria to right join because there is no such criteria specification

def recentQuestions = Topic.createCriteria().list([sort:'lastUpdated',order:'desc',max:5]){
            createAlias  "follower", "f" , CriteriaSpecification.LEFT_JOIN

            or{
                eq('f.user',u)
                isNull('f.user')
            } 
            eq('type','FORUM',[ignoreCase: true])

        }
tim_yates
  • 167,322
  • 27
  • 342
  • 338
Sap
  • 5,197
  • 8
  • 59
  • 101
  • How are you creating an alias for `follower` when there is no `follower` in `Topic`? – dmahapatro Jun 07 '13 at 14:35
  • 1
    @dmahapatro this is what the OP says in his question. He transformed the query in a criteria, but it fails exactly because Follower is not linked with Topic. –  Jun 07 '13 at 18:48
  • @Sap you will use pagination or just show the last 5 topics? A solution is to use HQL to perform your query. –  Jun 07 '13 at 18:50
  • Oops got it, sorry for misreading the question. I know I always screw up on a Friday. :( Thanks @SérgioMichels. Yea, HQL will be a better approach. – dmahapatro Jun 07 '13 at 19:01
  • @dmahapatro Friday should be a beer day and not work :-) –  Jun 07 '13 at 19:11
  • @SérgioMichels Rightly said my dear friend. Something like [this](http://gr8conf.org/uploads/eu2013/Image/blogs/2013-05-09%2009.17.05.jpg) will make my weekend. ;) – dmahapatro Jun 07 '13 at 19:19
  • Friday's over and I have a hangover:) Anyways, I was trying to avoid HQL because I thought they may backfire if I decide to use NOSQL like MongoDB or DynamoDB in future because GORM may not support them out of the box. – Sap Jun 08 '13 at 07:30

0 Answers0