0 Replies Latest reply on Feb 19, 2008 5:51 PM by Siarhei Dudzin

    Using join in overridden EntityQuery.getEjbql()

    Siarhei Dudzin Apprentice

      Hi all,


      I want to use a join query in getEjbql() in an EntityQuery.


      I can do it in the following way:


      @Override
      public String getEjbql() {
           return "select base from Base base join base.data data";
      }
      



      The problem here is that if no search arguments are used the query is fired 'as is' (no restrictions appended) which results in duplicate result set. Which seems to be easy to fix by adding a distinct keyword:


      @Override
      public String getEjbql() {
           return "select distinct base from Base base join base.data data";
      }
      



      This query fixes the duplicates problem, however after reading
      http://www.hibernate.org/117.html#A12 I am worrying about the performance because it says: All of this filtering of duplicates happens in-memory, when the resultset is marshalled into objects.


      If I have large datasets this may be a big performance hit. Is this still the case or is it safe from the performance view to use distinct?

      Does it make more sense to dynamically construct the query in getEjbql instead? Like this:


           @Override
           public String getEjbql() {
                String query = null;
                if ( condition == null ) {
                     query = "select base from Base base";
                } else {
                     query = "select distinct base from Base base join base.data data";
                }
                return query;
           }
      



      Where condition is coming from a search form via page params (just as seam-gen generates). If this one is better how reliable is it to dynamically construct the query based on the search parameters? I see getEjbql is invoked at least 3 times in my case and each time the search condition is either null, empty string or the search value applied (I am also worrying about upward compatibility here)?


      So which one is better for Seam and is there another/better option?


      Thanks in advance!


      Siarhei