CakePHP select many to many (HABTM) records

Question

after create a many to many (HABTM) relationship between 2 classes (as code bellow) I was expecting to see results including the related records but for some reason this is not happening.

Models

App::uses('AppModel', 'Model');
class NewsCategory extends AppModel{
  public $hasAndBelongsToMany = array('News');
}

App::uses('AppModel', 'Model');
class News extends AppModel{
  public $hasAndBelongsToMany = array('NewsCategory');
}

I created a table called "news_news_categories" with both tables id.

Controller

this->data = $this->News->find('first', array('conditions' => array('News.id' => $id)));

The result from code above doesn't include the related NewsCategory records.

After spend hours trying to make this happen, I couldn't get this done.

What I'm missing here?

Thank you all in advance!

Update

When I dump the SQL code used by CakePHP using

echo $this->element('sql_dump');

the results didn't show any query (or joining) for this relationship. CakePHP is simply ignoring the $hasAndBelongsToMany config.

Pratik Joshi · Answer 1 · 2015-02-21T07:42:08.977

0

You shuold follow coding conventions by cakephp or your code will simply Not work as expected or you will have to use Dirty tricks like $useTable = etc.

Your problem is your table name for HABTM should be

1.All lower case letters

2.It should have both table names(in plural).

3.Plural tables names(i.e. Words) should be in alphabetical order/

4.There should be underscore in between your table names.

As per rules your table name should be news_news_categories .

Also ensure in your table news_news_categories you have Foreign keys like news_id and news_category_id. And ensure you dont have any Primary key (like id) in news_news_categories table as both fields combinely work as primary key.

edited Feb 21 '15 at 07:42

answered Feb 21 '15 at 07:34

Pratik Joshi

11,485
7
41
73

sorry, was a typing mistake (fixed it on my original question). the table was already name lowercased as you mentioned. Why the join table (news_news_categories) can't have an id column? Im asking because it has the id column and Im not having any problems creating the records... only when selecting its not working – PH. Feb 21 '15 at 13:03
BTW, I just got this working and didn't need to remove id column from the relationship table. Thanks for your help! – PH. Feb 21 '15 at 14:00

PH. · Accepted Answer · 2015-02-21T14:06:26.967

Finally found the solution. Basically I did the following steps:

Created a new php file to represent the relationship model (for some reason I had understood that CakePHP would do it automagic behind the scenes)
```
App::uses('AppModel', 'Model');
class NewsNewsCategory extends AppModel{
  var $belongsTo = array('News', 'NewsCategory');
}
```

Changed my query code to bellow:

this->data = $this->News->find('first', array('conditions' => array('News.id' => $id)), 'contain' => array('NewsCategory'));

Finally I was expecting to get the results inside the same array like:
```
$this->data['News']['NewsCategories'] //or anything like that
```

But I discovered the related NewsCategory records was available on another var:

$this->data['NewsCategory']

(and not nested in the News result array, as I first supposed)

Thank you all. I hope this helps other people in the future.

Supravat Mondal · Answer 3 · 2015-02-21T19:16:07.673

-1

I think here is problem for CakePHP Conventions. So you just put the use table name in your models. like as

App::uses('AppModel', 'Model');
class NewsCategory extends AppModel{
    public $useTable = 'news_categories'; 
    // This model uses a database table 'news_categories'
    public $hasAndBelongsToMany = array('News');
}

App::uses('AppModel', 'Model');
class News extends AppModel{
    public $useTable = 'news'; // This model uses a database table 'news'
    public $hasAndBelongsToMany = array('NewsCategory');
}

CakePHP select many to many (HABTM) records

3 Answers3