A complex model example - many to many and returning counts along with recordset

[Gianna Giavelli]

I thought this might help new people. This is one of my more complicated model files. It has to support many to many join and some additional functions which are quite complex queries (left outer join!). Here's how it ended up: When you consider that all my one to many relationships are automatically bound simply by specifying xxx_ID as a column what activejdbc has done is given me a fairly automatic rendition of a complicated object model involving many tables all without writing boring repository code and typing in all the db properties over and over. SCORE! 

In this example the primary table is category  and then there is a mapping table between it and user containing two columns categoryid and userid and foreign keys mapping categoryid back to category.id and userid back to user.id.

Calling the new functions is simple:

    Category.getSubCategoryCount(strCatId, strMetroAreaId);

Now there is one more trick. What I am doing is getting for each category the list of sub categories along with how many reviews are in each subcategory (childcount) so that I can display it all with one "recordset" coming back all at once. OK the method name isn't perfect since I'm not returning a simple count but records.  In order to make this trick work, simply add a column to your database table for category called "childcount". Now there will be an entry in the model, and activejdbc will happily fill in all the data. 

What I can now see in my app is:

     subcat1 (12)

     subcat2 (1)

     subcat3 (7) 

etc.

Of course you can use this "add a dummy column" approach for any additional info you wish to manage back with a unified result it could be a sum, a calculation, anything.

When you think how much power you have gotten so fast, its really quite a nice framework! I hope this helps people. 

cheers

- Gia

----------------

package app.models;

import org.javalite.activejdbc.LazyList;

import org.javalite.activejdbc.Model;

import org.javalite.activejdbc.annotations.*;

 

@Table("category")

@Many2Many(other = User.class, join = "mtbl_user_category", sourceFKName = "categoryid", targetFKName = "userid")

public class Category extends Model

{ 

   static 

   { 

   }

   

   public static LazyList<Model> getSubCategoryCount(Object parentId, Object metroarea)

   {

      return Category.findBySQL(

       "select category.id, category.parentcategoryid, category.name, reviewitem.metroarea_id, count(reviewitem.id) as childcount from reviewitem right outer join category on  category.id = reviewitem.categoryid  where category.parentcategoryid = ? and category.type = 'sub' and (reviewitem.metroarea_id= ? || reviewitem.metroarea_id is null) group by category.name "

     , parentId, metroarea);

       

   }

   

   public static LazyList<Model> getIntCategoryCount(Object parentId, Object metroarea)

   {

      return Category.findBySQL(

       "select category.id, category.parentcategoryid, category.name, reviewitem.metroarea_id, count(reviewitem.id) as childcount from reviewitem right outer join category on  category.id = reviewitem.categoryid  where category.parentcategoryid = ? and category.type = 'int' and (reviewitem.metroarea_id= ? || reviewitem.metroarea_id is null) group by category.name "

     , parentId, metroarea);

       

   }

    

}

[Igor Polevoy]

This is an interesting approach. However, usually the method Model.findBySQL is used to select all columns of a related table that a model backs. 

This way, when you get a list of models, you will be able to select all attributes from it. In your case: 

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

I'm not sure if selected values correspond to attributes names of Category model

Just my two cents :)

Other than that, cool stuff

tx

[Gianna Giavelli]

I think I may have had an issue due to the complexity of the query that I couldnt use findBySql

[Igor Polevoy]

that could be, looks a bit long to my taste :D

[Gianna Giavelli]

I never claimed it was tasty ! :*>

[Gianna Giavelli]

I think I understand your comment a bit more clearly. I have some other complex queries where I then join to the table and simply return *.  Then the model works.  It's an issue with the count being a synthetic column and computed. there is no data ever in the column, its simply there so that it can re-map back to the model without breaking.  I could list out all the columns for a more full model but I am just careful in this one use case. For other use cases, certainly I return * so its just a normal model coming back and I agree its the preferred pattern.

On Monday, May 4, 2015 at 12:37:44 AM UTC-5, Igor Polevoy wrote:

[Igor Polevoy]

Gianna, I think for cases like that you are better off with Base.find(..) or Base.findAll(). 

Returning results in a model query that do not match the model schema ... smells :)

tx