'where' condition in has_many_through in kohana

Question

I have two models Illness and Symptom:

Illness

class Model_Illness extends ORM {

    //protected $_db_group = 'default2';
    protected $_table_name = 'illnesses';

    protected $_has_many = array(
        'symptoms' => array(
            'through' => 'symptoms_illnesses',
            'foreign_key' => 'illness_id',
        )
    );

Symptom

class Model_Symptom extends ORM {

    //protected $_db_group = 'default2';
    protected $_table_name = 'symptoms';
    protected $_has_many = array(
        'illnesses' => array(
            'through' => 'symptoms_illnesses',
            'foreign_key' => 'symptom_id',
        )
    );

The logic is illness may have many symptoms and symptom may have many illnesses. So these two have middle table symptoms_illnesses which stores ids of interconnected illnesses and symptoms. Finally my task: I have to make search of illnesses by symptoms. I send symptom ids in array, and then should get illnesses which only have these symptoms. I tried following function:

public function bySymp($symps){
        $res = array();

        $objs = ORM::factory('Illness')->join('symptoms_illnesses')
            ->on('symptoms_illnesses.illness_id', '=', 'illness.id');

        foreach($symps as $s){

             $objs = $objs->where('symptoms_illnesses.symptom_id', '=', $s);

         }


        foreach($objs->find_all() as $o){
            $res[] = $o;

        }

        return $res;
    }

It returns nothing, when I put more than one value in array. I also tried $objs = $this->where('symptom_id', 'IN', $symptom_ids); it works like 'OR' condition, but I have to output exactly such illnesses that have symtoms in array of symptoms id's.

Answer 1

I think you would need to do multiple joins, one for each sympton. But a quick look into the Kohana documentation shows, that it doesn't allow for aliases inside queries, so a constructing a WHERE clause is difficult/impossible.

The only way I see that this works out of the box would be first finding all illnesses and then checking via has() for the required symptons.

$res = array();
$objs = ORM::factory('Illness')->find_all();
foreach ($objs as $o) {
    if ($o->has('symptom', $symps)) {
        $res[] = $o;
    }
}
return $res;

But I haven't worked with Kohana in some time and might be overlooking something.