7 Replies Latest reply on Apr 17, 2007 11:12 AM by Jim Hazen

    Framework: Counting query entities

    Christian Pietsch Newbie

      Hi!

      I'm trying to use the statement "from MediabaseItemCollectible mic left inner join fetch mic.item as item" as a framework:entity-query.
      It works fine as as long as the seam framework does not try to execute the count query generated in o.j.s.framework.Query class (getCountEjbql) (e.g. for pagination) with the following hiberante error:

      org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=item,role=null,tableName=dvdb2_item,tableAlias=item1_,origin=dvdb2_mediabase_item mediabasei0_,colums={mediabasei0_.item_id ,className=de.dvdb.database.domain.Item}}] [select count(mic) from de.dvdb.mediabase.domain.MediabaseItemCollectible mic left outer join fetch mic.item as item where mic.mediabase.id = 43303
      


      It works fine if I remove the "fetch" keyword but that's definitely not the way I want to go.

      I've read that since 3.1 hibernate is more picky on the semantic on hql statements and the generated count statement does not make sense.

      So, what are my options? I hope somebody here has a good idea :D

      Chris


        • 1. Re: Framework: Counting query entities
          Christian Pietsch Newbie

          Hey!

          Bump :D

          I cannot believe nobody else is currently using the great seam framework without state of the art pagination?

          Best,

          Chris

          • 2. Re: Framework: Counting query entities
            Jim Hazen Expert

            Looks like a problem to me. As a work around try something like:

            @Name("myQuery")
            public class MyQuery extends EntityQuery
            {
             protected String getCountEjbql()
             {
             return "select count(*) without fetch";
             }
            
             public String getEjbql()
             {
             return "original query";
             }
            }
            

            Not as great as the components.xml solution, but should keep you functioning until this is sorted out.

            Oh what the heck, you should be able to smack things around a bit. Maybe even even include some components.xml like this.
            <component name="someQuery" auto-create="true" class="x.y.MyQuery">
             <property name="countEjbql">select count(*) no fetch</property>
             <property name="ejbql">original</property>
            </component>
            
            <component name="OtherQuery" auto-create="true" class="x.y.MyQuery">
             <property name="countEjbql"></property>
             <property name="ejbql"></property>
            </component>


            not sure if the countEjbql stuff has a public setter might need to add one. I guess in the end you have something like:
            public class PersonQuery extends EntityQuery
            {
             private String countEjbql;
            
             protected String getCountEjbql()
             {
             return countEjbql;
             }
            
             public void setCountEjbql(String query)
             {
             countEjbql = query;
             }
            }

            I haven't tried any of this, but it sounds about right to me. Let me know.

            • 3. Re: Framework: Counting query entities
              Jim Hazen Expert

              Oops, PersonQuery class in the last code block should be MyQuery.

              • 4. Re: Framework: Counting query entities
                Christian Pietsch Newbie

                Thanks Captain!

                I'll do it your way. Of course a framework:entity-query in components.xml would have been nicer.

                I believe you cannot automatically deviate a correct count query from any ejb/hql query in general, can we Gavin? If this is true we should think about a countEjbql attribute on framework:entity-query.

                Makes sense?

                Chris

                • 5. Re: Framework: Counting query entities
                  Darryl Smith Newbie

                  our solution to this was

                   @Override
                   protected String getCountEjbql() {
                   String countEjbql = super.getCountEjbql();
                   return countEjbql.replaceAll("join fetch", "join" );
                   }
                  


                  • 6. Re: Framework: Counting query entities
                    Christian Pietsch Newbie

                    mmh, interesting. This should work for my quoted query. But you cannot define the query within components.xml anymore, can you? I really love that feature because it saves me countless simple & stupig classes.

                    Chris

                    • 7. Re: Framework: Counting query entities
                      Jim Hazen Expert

                      Lol, should really not drink and make forum posts. :) A combination of dsmith's solution and my components.xml should give you what you're looking for. You only need 1 small class that you reuse in components.xml. You could even write your own namespace and have your own <myframework:entity-query> tag. But that's probably overkill, the <component...> syntax isn't that verbose.

                      Although I haven't tried it, you may even be able to use the class attribute on <framework:entity-query> to tell Seam to use your EntityQuery subclass. If so, great. You have plenty of options.

                      Based on the HQL, you'd think that Hibernate should be able to look at any digested query that has a single return and determine the correct count() syntax. Even eliminate unnecessary joins like the one you have here. Given how frequently this is used I think this would be a great convenience feature, especially for folks that use the Criteria APIs.