views:

30

answers:

2

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 do it. My SQL-fu isn't the best :p

A: 

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.

Matt H
A: 
  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.

biakaveron
Problem is that this is a many-to-many relationship. Otherwise I would have done it like this. `->with('bookings')` doesn't really do anything it seems.
Svish