Waclock Waclock - 3 months ago 23
SQL Question

ActiveAndroid Many-to-many relationship

I’m currently using ActiveAndroid, and have been trying to get a many-to-many relationship to work for the past few hours, however I just can’t get it to work. I hope you can help me out:

I have the models “Student” and “Course”, a student can have many courses, and a course has many students. Basically this is what I have in the model “StudentCourse”:

@Column(name = COURSE)
public Course course;

@Column(name = STUDENT)
public Student student;

public StudentCourse(Student student, Course course) {
super();
this.student = student;
this.course = course;
}
//
public StudentCourse(){ }

public List<Course> courses(){
return getMany(Course.class, "StudentCourse");
}
public List<Student> students(){
return getMany(Student.class, "StudentCourse");
}


Right now what I’m trying to do is get “all students in course X”,with the following code:

((Student) new Select().from(StudentCourse.class).where("course = ?",selectedCourse.getId()).executeSingle()).students();


However I get the following error:

java.lang.ClassCastException: com.papinotas.models.StudentCourse cannot be cast to com.papinotas.models.Student

If I change the cast of (Student) to (StudentCourse) I get the following error:

android.database.sqlite.SQLiteException: no such column: students.StudentCourse (code 1): , while compiling: SELECT * FROM students WHERE students.StudentCourse=1

My main goal is to hopefully achieve this in just 1 query. Any help would be greatly appreciated. Thanks in advance!

PS: I've already looked at pretty much everything I could find: Active Android many-to-many relationship and https://github.com/pardom/ActiveAndroid/issues/46

Answer

I wouldn't use getMany for this. I'd do this instead:

return new Select()
    .from(Student.class)
    .innerJoin(StudentCourse.class).on("students.id = studentcourses.id")
    .where("studentcourses.course = ?", courseId)
    .execute();
Comments