Loopback-datasource-juggler: Is it possible to add conditions on "through" model in "hasManyThrough" relation?

[windyinwind]

I'm using mysql, relation of teachers and students is many-to-many.
I want to select all students of one teacher, below is my table definition:
Teacher table:

| id | name |
| --- | --- |
| 1 | Tom |
| 2 | Jerry |
| ... | ... |

Student table:

| id | name |
| --- | --- |
| 1 | Sam |
| 2 | Hank |
| ... | ... |

and relation table (teacher_student):

| teacher_id | student_id | deleted |
| --- | --- | --- |
| 1 | 1 | 1 |
| 2 | 2 | 0 |
| ... | ... | .... |

The deleted in relation representing if this relation is valid(1 stand for valid, and 0 stand for invalid).

Relation definition in Teacher.json

"students": {
"type": "hasMany",
"model": "Student",
"foreignKey": "teacher_id",
"through": "teacher_student"
},

I want to select all valid students of one teacher.

So I use:

Teacher.findById(id, {
      fields: {
        id: true
      },
      include: {
        relation: 'students',
        where:  {deleted: false}  //  not filter invalid relations if add thin line 
        scope: {
          where: {
            deleted: 0
          }
        }
      }, function(err, result){
        // process result
    }

So is it possible to add conditions on teacher_student model? OR I have to manually filter those invalid relations.

[0candy]

You can try defining a scope method on the model or a custom method.

https://docs.strongloop.com/display/APIC/Querying+related+models#Queryingrelatedmodels-Scope

[windyinwind]

@0candy I defined a custom method as a workaround, but I do think loopback should have this feature.

[Amir-61]

@0candy @windyinwind I believe the easier solution would be querying teacher_student model and then include teacher and student models for more info.

[zbarbuto]

I believe this is possible, but you would have to define relations between student -> teacher_student and teacher_student -> student

That way you can do something like this:

Teacher.findById(id, {
  include: {
    relation: 'teacher_student',
    scope: {
      where: { deleted: false},
      include: 'student'
    }
  }
})

[windyinwind]

@zbarbuto Thanks for your solution, but it won't be the right, cuz teacher and students is many-to-many relation. According to what you suggest, teacher_student will have use has-many relation to find student. has-many relation need to use id of teacher_student(not student_id) table to find students. You can not define relation to your solution work in loopback.

[zbarbuto]

@windyinwind It does work (I've done it before) but it is a bit of a workaround rather than a solution. You just have to ensure that there is actually a teacher_student model define and in the teacher_student's model.json you define belongsTo relations out to the other models:

{
  "name": "teacher_student",
  "relations": {
    "teacher": {
      "type": "belongsTo",
      "model": "teacher",
      "foreignKey": "teacher_id"
    },
   "student": {
      "type": "belongsTo",
      "model": "student",
      "foreignKey": "student_id"
    }
  }
}

You would also need to define the relationship between teacher and teacher_student

{
  "name": "teacher_student",
  ...
  "relations": {
    "teacher_student": {
      "type": "hasMany",
      "model": "teacher_student",
      "foreignKey": "teacher_id"
    },
   "student": {
      "type": "hasMany",
      "model": "student",
      "through": "teacher_student"
    }
  }
}

So even though it is many-to-many each individual teacher_student row "belongs to" exactly one student and exactly one teacher (allowing you to include student on teacher_student and a teacher "hasMany" teacher_students, allowing you to include teacher_student on `teacher. This allows the query defined above to work.

As I said, not a great solution but it does work.

[windyinwind]

@zbarbuto I tried your model definition and it works like a charm.　Though it's not a perfect solution, it still the best workaround I ever met. I will your solution till loopback support it officially. Thanks a lot.