Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Some problems with the Models relationships

I'v noticed that in the example of offical documents, the data table of relationships has a field of id of NOT NULL and AUTO_INCREMENT, define like this:

create table `posts_categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `posts_id` int(10) unsigned not null,
  `categories_id` int(10) unsigned not null,
  primary key (`id`)
)  ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Can I drop the field id? like this:

create table `posts_categories` (
  `posts_id` int(10) unsigned not null,
  `categories_id` int(10) unsigned not null,
  UNIQUE KEY `pid_cid` (`posts_id`, `categories_id`)
)  ENGINE=MyISAM  DEFAULT CHARSET=utf8;

and the code in the models is the same with the previous vision(the table with id field)?

Well i guess you can drop it.



38.6k
Accepted
answer
edited Nov '15

It would not affect the relation definitions per-se, so you could still use hasOne, hasMany and hasManyToMany. BUT there shouldn't be an id field in the model declaration, otherwise you'll get an error.

You will have problems with creation/update/search though. Phalcon ORM only uses PRIMARY keys to determine whether a model already exists. If you do a save() on the model, it will give back a record already exists error, because it only looks for primary key uniqueness (but not UNIQUE key).

In your example, it would be sufficient to declare the two columns as PRIMARY:

create table `posts_categories` (
  `posts_id` int(10) unsigned not null,
  `categories_id` int(10) unsigned not null,
  PRIMARY KEY `pid_cid` (`posts_id`, `categories_id`)
)  ENGINE=MyISAM  DEFAULT CHARSET=utf8;


9.4k
edited Nov '15

Thank you very much! Your answer is very limpid to the ORM. But I'm not sure what will Phalcon do to the data table posts_categories with the PRIMARY KEY ``pid_cid`` (``posts_id``, ``categories_id``)

For example, table posts_categories has some records:

+----------+----------------+
| posts_id | categories_id  |
+----------+----------------+
|    1     |      1         |
|    1     |      2         |
|    1     |      3         |
+----------+----------------+

Post 1 has three categories, they're 1,2,3, and I want to EDIT the post, and change it's categories to "2, 4, 5", what will happen with the database? delete+insert, or update+insert?

It would not affect the relation definitions per-se, so you could still use hasOne, hasMany and hasManyToMany. BUT there shouldn't be an id field in the model declaration, otherwise you'll get an error.

You will have problems with creation/update/search though. Phalcon ORM only uses PRIMARY keys to determine whether a model already exists. If you do a save() on the model, it will give back a record already exists error, because it only looks for primary key uniqueness (but not UNIQUE key).

In your example, it would be sufficient to declare the two columns as PRIMARY:

```sql create table posts_categories ( posts_id int(10) unsigned not null, categories_id int(10) unsigned not null, PRIMARY KEY pid_cid (posts_id, categories_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ```

You would have to delete+insert records to change the assigned categories. As far as I know, this is the way to go with many-to-many relations. (At least it's working for me flawlessly)

This would result in a new row:

$cat = PostsCategories::findFirstByPostsId(1);
$cat->setCategoriesId(4);
$cat->save();

While this will give an error, because it wouldn't find any records to update:

$cat = PostsCategories::findFirstByPostsId(1);
$cat->setCategoriesId(4);
$cat->update();


9.4k

Thanks a lot!

You would have to delete+insert records to change the assigned categories. As far as I know, this is the way to go with many-to-many relations. (At least it's working for me flawlessly)

This would result in a new row:

```php $cat = PostsCategories::findFirstByPostsId(1); $cat->setCategoriesId(4); $cat->save(); ```

While this will give an error, because it wouldn't find any records to update:

```php $cat = PostsCategories::findFirstByPostsId(1); $cat->setCategoriesId(4); $cat->update(); ```



9.4k
edited Nov '15

Then how to update it?

It seems that $cat->update() can not work! and PHQL also can't work!

$phql = "UPDATE PostsCategories SET categories_id = 11 WHERE categories_id = 2";
$this->modelsManager->executeQuery($phql);

I think PHQL could work, because it doesn't any primary key, but how to correct it?

Thanks!

You would have to delete+insert records to change the assigned categories. As far as I know, this is the way to go with many-to-many relations. (At least it's working for me flawlessly)

This would result in a new row:

```php $cat = PostsCategories::findFirstByPostsId(1); $cat->setCategoriesId(4); $cat->save(); ```

While this will give an error, because it wouldn't find any records to update:

```php $cat = PostsCategories::findFirstByPostsId(1); $cat->setCategoriesId(4); $cat->update(); ```