Returning Counts along with Data

[Gianna Giavelli]

This isn't a problem with ActiveJDBC more I'm trying to think of the best way to do this with the activeweb and activejdbc frameworks.

I have some items which are linked into categories and sub categories. 

I display a list of the sub categories. I query them by selecting those who match the parent category id. simple. 

         //Load Sub Categories

         view("subcategories", Category.where("parentcategoryid=? and type=?", strCatId, "sub"));  

      

The issue is, in the UI, I want to display the subcategories along with the counts of items in each category. In effect its a combined result. To display something like:

    subcategory1 (20)

    subcategory2 (1)

    subcategory3 (0)

    subcategory4 (5)

The result of the sub categories comes back as a view. Would I just try to set up a 2nd view for the item counts and step each one to get both parts of data? 

Or create some database level view

Any help appreciated. Thanks. 

        

[Igor Polevoy]

I'm not that good with SQL (reason for writing ActiveJDBC  - LOL!), but I would not use models for that.

Something like this should do: 

Map cats  = Base.find("select name, count(*) total from categories where parentcategoryid = ? and type = ? group by total", strCatId, "sub");

don't hold me accountable if this SQL does not work :) this is just a direction.

Then you can: 

view("categories", cats);

Ideally, this code needs to be inside the Category class: 

public class Category extends Model{
   public static Map getSubCategories(Object parentId){
        return Base.find("select name, count(*) total from categories where parentcategoryid = ? and type = 'sub' group by total", parentId);
   }
}

This way, you contain all code related to a single table inside a single model. 

Sprinkling SQL code across controllers is not desirable for good maintainability. 

tx

[Gianna Giavelli]

ah duh. working too late I guess the ol brain isn't working.  

Thanks for the quick response. It's starting to go faster. I can't imagine going back to DAOs. I'll keep posting issues as they crop up next I'm onto file uploads. got all my ajax working with error messages that trigger toasts and  its groovy. 

Yes I agree. Keeping anything fancy inside the model is pref to sql in a controller. 

Next I will be pondering internationalization of all this. Anyways your support has been really good so I continue with it. 

[Igor Polevoy]

agree, I could never see the value of DAOs 

[Gianna Giavelli]

(This isn't activejdbc. You can skip reading. I am only adding it to show the end of my story)

FYI this was my final SQL.  I have a table category with a parentid to the same table so it has parent child structure. And another table with reviews, pointing to the children ids. 

The frightening sql is:

select category.name, count(reviewitem.id)

   from reviewitem

right outer join

   category

  on

    category.id = reviewitem.categoryid

where 

   category.parentcategoryid = 2

group by category.name;

returns just what I want

having to structure it as a RIGHT outer join threw me a bit! PHEW! ok back to real coding! - Gia

[Gianna Giavelli]

OK, finally got it working. Here is what I did. 

I added a dummy column to my table "childcount"

Then within my model class  I added:

  public static LazyList<Model> getSubCategoryCount(Object parentId)

  {

     return Category.findBySQL(

      "select category.id, category.parentcategoryid, category.name, count(reviewitem.id) as childcount ...."

      , parentId);

      

  }

It is a trick because of course childcount as I create it has nothing to do with the column, but it allows it to pass checks of activeweb instrumentation and classes. If I didn't do this I would get an error illegal / unknown column or some such.  Personally I don't mind the dummy column. It makes doing this very very easy.

I slightly violate the use pattern of a model by not returning ALL the columns but really I could add that. 

The solution is very elegant once you get to the .ftl file because now all I have to do is loop my result and then reference the count column. 

This is much simpler than trying to manage 2 loops one with all the data and the other with the count, or worse trying to inject with javascript somehow. 

It applies when you need a count by row item but I would say in general to only use when you are in a complex situation, otherwise follow the model pattern as close as possible. This is exception case.

[Igor Polevoy]

Gianna, maybe you need to use: http://javalite.github.io/activejdbc/org/javalite/activejdbc/Base.html#findAll-java.lang.String-

for this. 

Using a model to get non-model related data is not 100% semantically correct. 

This:

List<Map> Base.findAll(String query)

will get you a list of maps with exactly the same key names, so no changes on the view. 

thanks

[Igor Polevoy]

sorry,  this one with parameters: http://javalite.github.io/activejdbc/org/javalite/activejdbc/Base.html#findAll-java.lang.String-java.lang.Object...-

[Igor Polevoy]

haha, sorry gain - correct link: http://javalite.github.io/activejdbc/org/javalite/activejdbc/Base.html#find-java.lang.String-java.lang.Object...-