How to get result by joining two collections (tables) [Cakephp/MongoDB]

Question

I am using https://github.com/ichikaway/cakephp-mongodb.git plugin for accessing mongodb datasource.

I have two Models: Teachers and Subject. I want joint find result on Teacher and Subject. Here are my two models: Teacher:

<?php
class Teacher extends AppModel {
    public $actsAs = array('Containable');
    public $hasOne = array('Subject');

    public $primaryKey = '_id';
    var $mongoSchema = array(
            'name'=>array('type'=>'string'),
            'age'=>array('type'=>'string'),
            'subjectid'=>array('type'=>'string'),
            'created'=>array('type'=>'datetime'),
            'modified'=>array('type'=>'datetime'),
            );

Subject:

<?php
class Subject extends Model {
    public $actsAs = array('Containable');
    public $belongsTo= array('Teacher');

    public $primaryKey = '_id';
    var $mongoSchema = array(
        'name'=>array('type'=>'String'),
        'code'=>array('type'=>'String'),
        'created'=>array('type'=>'datetime'),
        'modified'=>array('type'=>'datetime')
        );

In Teachers Controller to get joint result, I did:

$results = $this->Teacher->find('all',array('contain'=>array('Subject')));
$this->set('results', $results);

But I am not getting any result from Subjects Collections.

Here is what I am getting:

array(5) {
  [0]=>
  array(1) {
    ["Teacher"]=>
    array(7) {
      ["_id"]=>
      string(24) "52e63d98aca7b9ca2f09d869"
      ["name"]=>
      string(13) "Jon Doe"
      ["age"]=>
      string(2) "51"
      ["subjectid"]=>
      string(24) "52e63c0faca7b9272c09d869"
      ["modified"]=>
      object(MongoDate)#78 (2) {
        ["sec"]=>
        int(1390820760)
        ["usec"]=>
        int(392000)
      }
      ["created"]=>
      object(MongoDate)#79 (2) {
        ["sec"]=>
        int(1390820760)
        ["usec"]=>
        int(392000)
      }
    }
  }

I am a cakephp/mongoDB rookie only, Please guide me to get the desired result.

Edit: I read that mongoDb don't support Join Operation. Then How to manually do it? I mean I can write two find query on each Model, then how to combine both array and set it to view?

or I m being total dumb? I heard that mongodb don't support joins? — varnothing

jmikola jmikola · Accepted Answer · 2014-02-13T17:32:30

As you said, MongoDB does not support joins. Documents in query results come directly from the collection being queried.

In this case, I imagine you would query for Teacher documents and then iterate over all documents and query for the Subject documents by the subjectid field, storing the resulting subject document in a new property on the Teacher. I'm not familiar with this CakePHP module, but you may or may not need to wrap the subjectid string value in a MongoId object before querying. This depends on whether or not your document _id fields in MongoDB are ObjectIds or plain strings.

If Subjects only belong to a single teacher and you find that you never query for Subjects outside of the context of a Teacher, you may want to consider embedding the Subject instead of simply storing its identifier. That would remove the need to query for Subjects after the fact.

How to get result by joining two collections (tables) [Cakephp/MongoDB]

1 Answers