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);
}
}