How can I build a 'has-many-through' relation linking more than 2 models?

Question

I have 3 models eg;

TABLE `users`
    `id` INT
    `username` VARCHAR(32)
    ...

TABLE `books`
    `id` INT
    `title` VARCHAR(100)
    `author` INT (foreign ket constraint)

TABLE `rights`
    `id` INT
    `name` VARCHAR(32)

Now I want a user to have particular rights to eg. read or edit a book. So the rights table should look like (much like ORM roles table):

|----|------|
| id | name |
|----|------|
| 1  | view |
| 2  | edit |
| .. | ...  |

And I would have a fourth table linking all three;

TABLE user_book_rights
|---------|---------|----------|
| user_id | book_id | right_id |
|---------|---------|----------|
|    1    |    1    |    2     |
|    1    |    2    |    1     |
|    2    |    1    |    1     |
|   ...   |   ...   |   ...    |

So if a user wants to, say, read a book, I want to check if the logged in user with id 1, has the right with id 1 for book with id 2.

But how the heck can I achieve this with ORM? Of course I can just write my own query;

SELECT COUNT(*) as `has_right` FROM `user_book_rights` WHERE user_id=1 AND book_id=2 AND right_id=1

if($result['has_right']) {
    echo 'Yeah, read the book!';
} else {
    echo 'Sorry mate, this book is not for dummies...';
}

But I'd rather do something like:

$has_right = $user->has('book_rights', ORM::factory('user_book_right', array('book_id' => '2', 'right_id' => 1));

Or even better:

$book = ORM::factory('book', 1);
$right = ORM::factory('right', array('name' => 'view'));
$has_right = $user->has('book_rights', ORM::factory('user_book_right', array($book, $right)));

I could not find an answer to my question. Is it weird to want to link three models as a many_through realtionship? Or is ORM just not capable and should I write my own query?

Thanks ia. for your insights!

Answer 1

Maybe my answer does not help you a lot. But i suggest that you encode the right in bit representation.

READ = 00000001 = 1
EDIT = 00000010 = 2
DELETE = 0000100 = 4

than, if a user has the write to read, edit and delete you just do

READ | EDIT | DELETE = 0000111 = 7

If you want to test if a user has a particular right you just do: if ($user_write & READ) { // he can read}

Maybe if you use this design, and eliminate the rights table with those constants, it may help you.