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