ActiveAndroid Many-to-many relationship

Question

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

Popularity

Answer 1

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

Infringe

Answer 2

Work like a charm:

Client class

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

Contract class

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

Relation between Client 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 helper

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

Dominance

Answer 3

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

The cast should actually be to (List<StudentCourse>), but the real problem is in the logic of your model here. You are calling executeSingle(), but you really want multiple StudentCourse objects so that you get every Student-Course relationship for the Course. Your students() and courses() methods don't make much sense, since one StudentCourse object only has one Student and one Course.

I would do it like so instead:

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

Hamrick

I was trying to find a way to get all students with a single query; this would do N queries if I'm not mistaken PS: I understand what your point is, and I agree it doesn't make any sense, I was trying to follow the example on the links I posted above, however they didn't work as I expected I suppose. – Popularity

This is a single SELECT query, but then it does some List manipulation so that you get a list of Students rather than a list of StudentCourses. – Hamrick

Oh, the "enrolment.student" doesn't make a query to the database, then you're right I believe! – Popularity

Depending on what you are using it for, you could just leave it as a List of StudentCourse objects (and maybe change your adapter class or whatever you use it for). If you really need it as a List of Student objects then you could do an SQL JOIN to make it scale more efficiently for large numbers of Students. – Hamrick