Kohana: How to load all many-to-many relationships in one query

I have a database relationship that looks something like this:

booking -> person <-> option

->  : one-to-many
<-> : many-to-many

I now need to list all the persons in a booking, with all their options. Using ORM in kohana I can load all persons like this:

$persons = ORM::factory('booking', $id)->persons->find_all();

I could then loop over the persons and get all their options, but that would mean one query per person. Is there a clever way to load this without having to do that? What I would like to end up with is something like this:

booking
 └ id
 └ created
 └ persons
    └ 0
      └ id
      └ name
      └ options
         └ 0
           └ id
           └ price
         └ n
           └ id
           └ price
    └ n
      └ id
      └ name
         └ options
         └ 0
           └ id
           └ price
         └ n
           └ id
           └ price

I probably have to write a custom database query for this, but even then I am not quite sure how to开发者_StackOverflow社区 do it. My SQL-fu isn't the best :p

If you're using ORM you do not need to write any SQL at all.

You'll find this guide helpful.

http://www.scribd.com/doc/5022685/Kohana-PHP-ORM-Guide-Volume-1- I think this is for ORM that was in version 2 of Kohana, but I don't believe it's terribly different. Your mileage may vary.

Be aware that ORM requires quite specific naming conventions. If you've got an old database that has a different naming convention than ORM can handle you'll have to ditch ORM and resort back to using SQL. There are lots of examples in the Kohana help for that.

1. Try $persons = ORM::factory('booking', $id)->persons->with('booking')->find_all();

2. You can add booking relation for autoloadnig using load_with property:

   class Model_Person extends ORM {

   protected $_belongs_to = array('booking');
   protected $_load_with = array('booking');
   

   }

So, when you load Person object it will automatically join with related Booking model with one query.

Based on my experience, I can say that you should use ORM only when you need it; sometimes you better use plain SQL queries (Using Database Module)

Now, when you want the result to be an object, you can use ->as_object('Model_Name') method, right before using ->execute() method on a Database query.

Hope this helps :)