Kohana (ORM) - how to find record from 2 tables

class Model_Category extends ORM
{     
    protected $_has_many = array(
        'film' => array('through' => 'films_categories')
    );
}

class Model_Film extends ORM
{        
    protected $_has_many = array(
        'categories' => array(
            'through' => 'films_categories'
        ),
}


films
-id (pk)
-title
-description

categories
-id (pk)
-name

films_categories
-film_id
-category_id

This is how my tables looks and here is what I need to do:

$films->ORM::factory('film');
$films
    ->where('title', '=', $my_title)
    ->and_where('any of categories name', '=', $category_name)
    ->find_开发者_JAVA百科all();

I need to find record that has $my_category='any of categories from category table'. Any simple way to do this?

---

So you're trying to get a movie by its title and category? I'd leave ORM for this query alone, since you won't benefit from it more then from query builder:

$films = DB::select()
    ->from('films')
    ->where('title', '=', $my_title)
    ->and_where($category_name, 'IN', DB::select()->from('categories')->execute()->as_array('id', 'name'))
    ->execute();

---

I found an answer. It's little different, because I wanted to find movie by its category name (or by many category names). I found easier to find movie by its category_id. If someone would find it helpfull here it is:

$category_id = 13;

$films = ORM::factory('film')
            ->select('category_id')
            ->join('films_categories')
            ->on('films_categories.film_id', '=', 'film.id')
            ->where('category_id', '=', $category_id)
            ->find_all();


foreach($films as $f)
    echo $f->title . " " . $f->category_id . "<br/>";

I dont know how it exactly works, but it is. I invented this by accident. And if someone could tell me why this line is needed:

->select('category_id')

or

->select('*')

without this line it gives and error:

Kohana_Exception [ 0 ]: The category_id property does not exist in the Model_Film class

why join() doesn't join whole tables without select('*') ?

---

If you still want to filter out films by category name this should work:

$films = ORM::factory('film')
    ->join('films_categories')->on('films_categories.film_id', '=', 'film.id')
    ->join(array('categories', 'category'))->on('category.id', '=', 'films_categories.category_id')
    ->where('film.title', '=', $film_title)
    ->where('category.name', '=', $category_name)
    ->find_all();

And I guess you need

->select('category_id')

in your query, cause you don't specify column table in your where statement. Like this:

->where('films_categories.category_id', '=', $category_id)