ActiveAndroid Many-to-Many Relationship

Question

I'm currently using ActiveAndroid and trying to get many-to-many relationships to work in the last few hours, however I just can't get it to work. Hope you can help me:

I have the Student and Course models, a student can have many courses, and the course has many students. This is basically what I have in the StudentCourse model:

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

Now I'm trying to do "all students in X course" 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 attributed to com.papinotas.models.Student

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

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

My main goal is to hope to achieve this in only one request. Any help would be greatly appreciated. Thanks in advance!

PS: I already covered almost everything I could find: an active many-to-many relationship for Android and https://github.com/pardom/ActiveAndroid/issues/46

Answer 1

I would not use getMany for this. I would do this instead:

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

Answer 2

If I change the (Student) cast to (StudentCourse), I get the following error ...

The cast should actually match (List<StudentCourse>) , but the real problem is the logic of your model here. You call executeSingle (), but you really need several StudentCourse objects to get all Student-Course relationships for the course. The methods of your students () and courses () do not make much sense, since one StudentCourse object has only one Student and one course.

I would do it like this:

 List<StudentCourse> enrolments = new Select().from(StudentCourse.class).where("course = ?",selectedCourse.getId()).execute(); List<Student> studentsInCourse = new ArrayList<Student>(); for(StudentCourse enrolment:enrolments) studentsInCourse.add(enrolment.student); 

Answer 3

Work like a charm:

Customer class

  @Table(name = "Client") public class Client extends Model{} 

Contract class

  @Table(name = "Contract") public class Contract extends Model{} 

Relationship between customer and contract

  @Table(name = "ClientContract") public class ClientContract extends Model { @Column(name = "Client", onDelete = Column.ForeignKeyAction.CASCADE) public Client client; @Column(name = "Contract", onDelete = Column.ForeignKeyAction.CASCADE) public Contract contract; } 

Database Assistant

 public class DBHelper { public List<Contract> getRelatedContracts(Client client) { List<Contract> contracts = null; if (client != null && client.isCreated()) { contracts = new Select() .from(Contract.class) .innerJoin(ClientContract.class).on("ClientContract.Contract = Contract.id") .where("ClientContract.Client = ?", client.getId()) .execute(); } return contracts; } public List<Client> getRelatedClients(Contract contract) { List<Client> clients = null; if (contract != null && contract.isCreated()) { clients = new Select() .from(Client.class) .innerJoin(ClientContract.class).on("ClientContract.Client = Client.id") .where("ClientContract.Contract = ?", contract.getId()) .execute(); } return clients; } // get relation public ClientContract getClientContract(Client client, Contract contract) { ClientContract clientContract = null; if (client != null && contract != null && client.isCreated() && contract.isCreated()) { clientContract = new Select().from(ClientContract.class) .where("Client = ?", client.getId()) .where("Contract = ?", contract.getId()) .executeSingle(); } return clientContract; } // add relation public ClientContract addClientContract(Client client, Contract contract) { ClientContract clientContract = getClientContract(client, contract); if (client != null && contract != null && clientContract == null) { if (client.getId() == null) client.save(); if (contract.getId() == null) contract.save(); clientContract = new ClientContract(); clientContract.client = client; clientContract.contract = contract; clientContract.save(); } return clientContract; } // delete relation public void deleteClientContract(Client client, Contract contract) { ClientContract clientContract = getClientContract(client, contract); if (clientContract != null && contract.isCreated()) clientContract.delete(); } }