How to query this with ORM?

Question

I've using Kohana for a couple of weeks. One thing I noticed is that Kohana is missing eager loading (as far as I know). Let's say I have the following tables.

Subjects

id
name

Chapters

id
subject_id
name

Videos

id
chapter_id
name

When a user opens a subject page, I want to display all the chapters and videos. With ORM, I can do

$tutorials = ORM::factory('subject')->where('id','=', 1)->find();

foreach($tutorials as $tutorial) 
{
    $chapters = $tutorial->chapters->find_all();
    foreach($chapters as $chapter) 
    {
        $videos = $chapter->videos->find_all();
    }
}

The above code is not efficient since it makes too many queries.

I thought about using join or database query builder, but both of them do not return a model object as their results. I also looked into with(), but it seems like it only works with one-to-one relationship.

using join on an ORM object returns an OPM object, but it doesn't return the data from the joining tables.

What would be my best option here? I would like to minimize # of queries and also want to get ORM objects a result. Whatever it would be, should return all the columns from tutorials, chapters, and videos.

Did you see [Kohana_Database_Query_Builder::as_object()](http://kohanaframework.org/3.3/guide-api/Kohana_Database_Query_Builder#as_object) ? — Darsstar, Nov 18 '13 at 09:02
@Darsstar // the problem of as_object is that it only accept one class name. What if I have a query that returns data from multiple tables by joining them? That's the main problem I'm having. — Moon, Nov 19 '13 at 23:26
It's been a long time since I've worked with ORM. But it would seem that a colon (':') has a special meaning in selected column names: http://kohanaframework.org/3.3/guide-api/ORM#_load_values — Darsstar, Nov 20 '13 at 10:30

score 3 · Answer 1 · answered Nov 18 '13 at 16:01

3

First of all, your code is excess. ORM method find() returns 1 Model_Subject object. See

$chapters = ORM::factory('subject', 1)->chapters->find_all();
foreach($chapters as $chapter) 
{
    $videos = $chapter->videos->find_all();
}

With DB builder you can make just 2 requests. First get array of all chapters ids:

$chapters = DB::select('id')
   ->from('chapters')
   ->where('subject_id', '=', '1')
   ->execute()
   ->as_array(NULL, 'id');

Second - get all videos by ids as Model_Video object

$videos = DB::select('id')
    ->from('videos')
    ->where('chapter_id', 'IN', $chapters)
    ->as_object('Model_Video') 
    ->execute()
    ->as_array();

answered Nov 18 '13 at 16:01

Vladimir Cherepinskiy

337
2
10

I don't think you got my point. I know that you can use DB query builder or DB::select. The problem of DB::select or DB query builder is that they do not return an ORM object, makign it impossible to call a method in an object. – Moon Nov 18 '13 at 19:28
Do you try execute this code and than print $videos variable? as_object('Model_Video') means that query builder returns ORM object – Vladimir Cherepinskiy Nov 19 '13 at 08:01
Ok...I think I understand your code now. Two problems here. I don't really want to run two queries when I can do it with one. Second, I still need to associate each videos to the chapters. However, I think this is best answer I found so far. Thank you. – Moon Nov 20 '13 at 00:07
2

You could skip the execution of the first query, and leave the rest as-is. So to be more clear remoce `->execute()->as_array(NULL, 'id')` from the first query. The query itself is included in the second query so Kohana will build it as a subquery something like `WHERE chapter_id IN( SELECT .... )` This can probably quite easily be rewritten to something that uses left joins. I'll have a go at it. – AmazingDreams Nov 22 '13 at 14:00

score 1 · Answer 2 · edited Nov 24 '13 at 12:22

So I guess you want something like this.

$videos = ORM::factory('Video')
    ->join(array('chapters', 'chapter'), 'LEFT')->on('video.chapter_id', '=', 'chapter.id')
    ->join(array('subjects', 'subject'), 'LEFT')->on('chapter.subject_id', '=', 'subject.id')
    ->where('subject.id', '=', $id)
    ->find_all();

Come to think of it, if the video belongs_to chapter belongs_to subject, try the following using with():

$videos = ORM::factory('Video')
    ->with('chapter:subject')        // These are the names of the relationships. `:` is separator
                                     // equals $video->chapter->subject;
    ->where('subject.id', '=', $id)
    ->find_all();

With things like this it often helps to think 'backwards'. You need the videos on that subject so start with the videos instead of the subject. :)

EDIT: The drawback of the second function is that it is going to preload all the data, it might be shorter to write but heavier on the server. I'd use the first one unless I need to know the subject and chapter anyway.

How to query this with ORM?

2 Answers2