Grails 2.1 createCriteria issue with params

Question

edit-

I am trying to return a list of portfolios, along with the last 5 publications attached to each portfolio from my 2 domain classes. I am getting the last total 5 publications back and each list displays all 5. The query is not returning that particular instances own publications. Kellys great ideas put back into another track.

I have created the method in the portfolio controller which is the hasMany side to the publications which belongsTo the portfolio domain class.

I just cant seem to get the portfolios to list their own publications. If I change the eq portfolios, it all works fine, except each portfolio list shows the same publications.

How do I load each portfolio and list the last 5 publications. This is rendered from the portfolio/list page as a partial.Is this the issue maybe. Should I just render it from a new view which is not associated with the portfolio list action??

Newbie to grails and have read and read the doc's, just cant seem to get the params query to return correctly. Help

def _webList (){
    //def per = Portfolio.properties
    def portfolios = Portfolio.list(params.id)
    def results = Publication.withCriteria {

        eq('published', 'Yes')
        order('lastUpdated', 'desc')
        maxResults(5)
    }

    def reportscount = Publication.count()

    [ portfolios: portfolios, results: results, reportscount: reportscount]
}

I can show the the sql log if needed.

EDIT

The following code is the entire partial from file _webList.gsp. The top div -alert loads on the page, but the content within the div property-list portfolio fails to load. Using Kelly's hibernate criteria produces the query in the sql log but not results or styles or anything are return to the view??. weird.!

<div class="alert alert-info" xmlns="http://www.w3.org/1999/html">Permissions apply to    <strong>editing</strong> publications.<br>
<div style="display: inline;"><p>Click portfolio name to read or edit publications. Total number of sites: <strong>${rsNumb}</strong> | Total number of publications:  <strong>${reportscount}</strong> </p>
</div>
</div>
<div class="property-list portfolio">
<g:each in="${portfolios}" var="portfolioInstance">
<div class="site-listing">
    <div><span class="label">Site Name:</span><g:link action="show" id="${portfolioInstance?.id }">${portfolioInstance?.portfolioName?.encodeAsHTML()}</g:link></div>
    <div><span class="label">Site Description:  </span>${portfolioInstance?.portdescrip?.encodeAsHTML() }</div>   <br>
    <div><span class="label">Site Administrator: </span>${portfolioInstance?.profile?.portfolioAdmin?.encodeAsHTML() }</div>   <br>
    <div><span class="label"> Total publications:</span><span class="badge badge-success"> ${portfolioInstance?.publications?.size()}</span> </div>
 <!-- whatever else you need here -->
 <!-- now iterate through the pubs -->
    <g:if test="${portfolioInstance?.publications}">
        <g:set var="publicationInstance" />
            <ul class="site-publication">
                 <li class="fieldcontain">
                     <span id="publications-label" class="property-label"><g:message code="portfolio.publications.label" default="Last 5 published publications:" /></span>
                         <g:each in="${portfolioInstance.publications}" var="publicationInstance">
                             ${publicationInstance?.id}
                                <span class="property-value" aria-labelledby="publications-label"><g:link controller="publication" action="show" id="${publicationInstance.id}">${publicationInstance?.encodeAsHTML()}</g:link></span>
<!-- and again whatever else you need here -->
                         </g:each>
        </g:if>
</g:each>
</div>

EDIT - sql log below

Hibernate: select this_.id as id5_1_, this_.version as version5_1_, this_.date_created as date3_5_1_, this_.last_updated as last4_5_1_, 
this_.portdescrip as portdesc5_5_1_, this_.portfolio_name as portfolio6_5_1_,   this_.portpublished as portpubl7_5_1_, this_.profile_id as profile8_5_1_, 
this_.status as status5_1_, 
publicatio1_.portfolio_id as portfolio5_5_3_, 
publicatio1_.id as id3_, publicatio1_.id as id2_0_, 
publicatio1_.version as version2_0_, 
publicatio1_.date_created as date3_2_0_, 
publicatio1_.last_updated as last4_2_0_, 
publicatio1_.portfolio_id as portfolio5_2_0_, 
publicatio1_.publication_content as publicat6_2_0_, 
publicatio1_.publication_name as publicat7_2_0_, 
publicatio1_.published as published2_0_, 
publicatio1_.publisheddate as publishe9_2_0_, 
publicatio1_.publishedemail as publish10_2_0_, 
publicatio1_.pubproduct_id as pubproduct11_2_0_ 
from portfolio this_ left outer join publication publicatio1_ 
on this_.id=publicatio1_.portfolio_id where (this_.status=?) 
and (publicatio1_.published=?) order by publicatio1_.last_updated desc

Kelly Kelly · Accepted Answer · 2012-11-29T08:35:59

You are getting the java.lang.ClassCastException because portfolios is a list and portfolio in the Publication class (probably) isn't, it's probably an id (long); can't cast a list in any meaningful way to compare to a long in eq ('portfolio', portfolios)

You should not need two separate queries since the domain classes are related.

--EDIT-- Editing to not use separate action and just use list action. I've not been able to get the include to work either, but below is pretty much what I've in dozens of cases. If there is some reason you can't do this then maybe a new question on just using the include mechanism might generate some attention.

I'm not sure what your current list action looks like. This is how I would code a list method to get ALL Portfolios and their last 5 Publications. No need for any parameters because I'm returning ALL portfolios.

//PortfolioController
def list (){ 
    def portfolios = Portfolio.createCriteria().list {
        //if you needed to filter the list by for example portfolio status or something you could add that here
        or {
            eq('status','ACTIVE')
            eq('status','PENDING')
        }
        publications(org.hibernate.criterion.CriteriaSpecification.LEFT_JOIN) {
            eq("published", "Yes")
            order("lastUpdated", "desc")
            firstResult(5)
        }
    }

    [portfolios: portfolios, portfolioCount:portfolios.size()]
}

Now the Publications come pre-attached to their Portfolio.

The LEFT_JOIN part of the above insures you get back a list of portfolios with ONLY those Publications attached that meet the criteria; if you leave that out it defaults to an inner join and when you iterate you will get ALL of the Publications for that Portfolio (even if they don't meet the criteria).

Then in your gsp iterate through the portfolios - it can either be directly in the list.gsp or in a template rendered in list.gsp. If you put it in a template called _webList.gsp you would render it in the list.gsp as

<g:render template="weblist" model="['portfolios': portfolios]" />

This is either in list.gsp or _webList.gsp - I would start with it directly in list.gsp to make sure it's all working.

<g:each in="${portfolios}" var="portfolioInstance" status="i">
    ${portfolioInstance?.portfolioName?.encodeAsHTML()
    <!-- whatever else you need here -->
    <!-- now iterate through the pubs -->
    <g:each in="${portfolioInstance.publications"} var="publicationInstance" status="j">
        ${publicationInstance.id}
        <!-- and again whatever else you need here -->
    </g:each>
</g:each>

--EDIT-- firstResult(5) seems to do the trick. --EDIT--

You'll notice I have the maxResults(5) commented in there - I'm having trouble getting that to work correctly. It seems to control the number of portfolios that are returned even though it is in the association block. Maybe someone else will see this and add that piece of the puzzle - or tinker with it yourself. I'll keep trying and update if I figure it out.

Grails 2.1 createCriteria issue with params

1 Answers