Advance ForeignKey Usage !

[deshario]

My Problems :

i wanna access all data_amount values where cat_type = 3 ! ( cat_type was located in table2 but i have one foreign_key in my table1) i dont no how to access it !

These are my tables structure

 Table -> Records
    // These are my Columns
    Id
    category_id : FK
    data_amount
    data_created
    shortnote

  @Column(name = "category_id", onUpdate = Column.ForeignKeyAction.CASCADE, onDelete =  
   Column.ForeignKeyAction.CASCADE)
   public Category category;

Table -> Category
    // These are my Columns
    Id
    cat_name
    cat_topic
    cat_type

I want to access all data_amount where cat_type = 3 (or some value) In my table name "Records" i have category_id as foreign key example : I can access all cat_type like this : but i have not idea how to implement with my problems

   public List<Records> records;
    records = Records.getAllRecords(); 

    for(int i=0; i< records.size(); i++){
          int types = records.get(i).getCategory().getCat_type();
          System.out.println(" Cat_Type : "+types);
    }
    public static List<Records> getAllRecords(){
      return new Select().from(Records.class).execute();
    }

  public static List<Records> joiner() { // I found this on google , It's not working
    int type = 1;
    return new Select()
            .from(Records.class)
            .innerJoin(Category.class)
            .on("Records.Id=Categories.Id")
            .where("Categories.cat_type = ?", type)
            .execute();
 }

if u guyz know how to do it .... please let me know .... i'll feel very thankful ! Thankx

[faisal154]

I know this answer is 2 months late but just in case you are still having such problems. I ran into the same problem recently and I couldn't find any solution other than using a join. The only solution I could come up with was selecting all the records from one table and then used a for loop to parse out the result based on foreign key ref. This might not be what you are looking for but it does solves the problem. Like this:-

List<Records> records = new Select().from(Records.class).execute();
for ( Record record : records )
{
      if (record.getCategory().getId() == filterId) 
                  // add record to new arraylist
}