FuelPHP Forums

I tried a few more times even with the structure by WanWizard, but still the list_id which is the FK is not being passed from the lists table to the related one on insert. Is it stored in any specific variable ?

say we are making a new Listing for a new TV Show, this would involve Model_List and Model_Shows. List
-- id (auto,PK)
--title
--content Shows
--id (auto, PK)
--list_id (FK)
--episodes
--airdate Model_List and Model_Shows are in one-to-one relationship where Model_Shows belong to Model_List. Now when we want to insert a new listing for 'Shows' we get a form with fields (title, content, episodes, airdate) and insert it into the database like this:

$list = new Model_List();
$list->title       = Input::post('title');
$list->content     = Input::post('content');

$list->shows = new Model_Shows();
$list->shows->episodes       = Input::post('episodes');
$list->shows->airdate       = Input::post('airdate');

$list->save();

Now as you can see from above the FK needs to be passed from the insert to the 'List' table to the 'Shows' table as the FK "list_id" which is the "List" table's "id" for the newly inserted row. But that doesn't seem to be happening.

Fuel\Core\Database_Exception [ 1048 ]: Column 'list_id' cannot be null [ INSERT INTO `list_shows` (`list_id`, `episodes`, `list_airdate`) VALUES (NULL, '15', '1305893843') ]

That's the error that comes, so the expected behavior that i was hoping for would be that, after a new insert is performed on the parent's table, the PK value would be returned and stored so when the child is inserted it will use that value as the FK for the related field 'list_id'. Based on what i saw of the ORM code it does seem to be following that logic , but either there is a bug, or i just don't know how to access the value of the PK after the parent insert to be used as the FK in the child table.

Controller: http://scrp.at/Z1 Model_List: http://scrp.at/Z2 Model_Shows: http://scrp.at/Z3 DBDump: http://scrp.at/Z4
Note: All row retrieving is working fine with the relationships.

Jelmer Schreuder wrote on Friday 20th of May 2011:

The problem is that the relation needs the primary keys in order to be established, but to get to that the model_to still had to get saved first. This shouldn't be necessary and I fixed it in the develop branch, but for now you can get it to work by allowing list_id to be null - that way it'll work without problems.

Seems to be working well from dev branch. Thanks!

Michael Kreis wrote on Wednesday 28th of September 2011:

Jelmer Schreuder wrote on Friday 20th of May 2011:

The problem is that the relation needs the primary keys in order to be established, but to get to that the model_to still had to get saved first. This shouldn't be necessary and I fixed it in the develop branch, but for now you can get it to work by allowing list_id to be null - that way it'll work without problems.

I'm having the same issue but this workaround won't work for me because I'm using the InnoDB Engine, which means foreign keys are not allowed to be null. Any other ideas how I can get it to work?

Set them to allow NULL? InnoDB doesn't enforce each FK to have a non-empty value unless you tell it to. Only thing the FK enforces is for it to have a value that is a valid PK value in the table to which it relates OR null.

I gave it a try for a $has_one relationship between Model_List and Model_Music and it returns this error:

Orm\Exception [ Error ]: Invalid Model instance added to relations in this model.

Models seem right, so here is the basic code

$list = new Model_List();

$list->list_title        = Input::post('title');
$list->list_content        = Input::post('content');

$list->music->list_tracks        = Input::post('tracks');
$list->music->list_album        = Input::post('album');

$list->save();

Note: It does save to the 'list' table but not to the music table, even with the error.

Jelmer Schreuder wrote on Wednesday 18th of May 2011:

The way you do it it'll be a stdClass object and not a Model_Music object. You need to create the new Model_Music instance yourself:

$list = new Model_List();

$list->list_title        = Input::post('title');
$list->list_content        = Input::post('content');

$list->music = new Model_Music();
$list->music->list_tracks        = Input::post('tracks');
$list->music->list_album        = Input::post('album');

$list->save();

Thanks, since they are related they need to have the same list_id, how do i get the list_id from the insert into the Model_List to the Model_Music ?

Jelmer Schreuder wrote on Wednesday 18th of May 2011:

When saving relations like this, the apropriate foreign keys will be set automaticly.

Unfortunately, it seems to be inserting '0' as the primary key. When I post a second time it gives this error, and it shows in the database that a row was created with '0' as the primary key.

Fuel\Core\Database_Exception [ 1062 ]: Duplicate entry '0' for key 'PRIMARY' [ INSERT INTO `mini_lists_shows` (`list_episodes`, `list_duration`, `list_aired`, `list_age_rate`) VALUES ('10', '10', '10', 'PG-17') ]

Should i post a bug report?

Jelmer Schreuder wrote on Wednesday 18th of May 2011:

No, that just means you didn't make it auto-increment.

Model_List table auto increments, Model_Music does not auto increment since it needs to get the list_id from Model_List, which is its parent, otherwise there will be conflict with other Models that has a one-to-one relationship with Model_List.

Jelmer Schreuder wrote on Wednesday 18th of May 2011:

Each table is expected to have its own unique primary key(s), if you don't auto-increment them you need to set those IDs manually before saving.

If they are in a one-to-one relationship there should be someway for them to share a common key during the saving process, like in my case, which is probably pretty common where the table structures are like this: List
-- list_id (Auto)
-- list_title
-- list_content Music
--list_id (passed)
--list_tracks
--list_albums Shows
--list_id (passed)
--list_episodes
--list_airdates In the above example it allows one-to-one relationships on the 'list_id' for additional information, so when saving an insert from a form, it needs to have the same 'list_id' available to be passed so it can remain consistent with each other. Sure i can save them the way it is now, but id still need to pass the list_is manually. An easier way would be that once the parent model insert is done, it saves the 'list_id' which is the key connecting the relationship between Model_List to Model_Music (or Shows) so when i insert the other fields it will insert that id for that field automatically. Also wouldn't it be possible that like Lazy Loading using $list->music instantiates the new Model_Music() ? then it will simplify the inserting process just like with selecting.

As Jelmer stated, this is not the way it works, nor is it best practice database design. Every table needs it's own id as primary key. period. And it is best practice that the primary key is an abstract auto increment number, and not part of the application data. Using application data as primary key means a serious application change whenever you decide on a change that involves structural changes to your table relations. Or if the meaning of the column changes so that it is no longer unique. So: List
-- id (Auto)
-- list_title
-- list_content Music
-- id (Auto)
-- list_id (FK to List)
- -music_tracks
-- music_albums Shows
-- id (Auto)
-- list_id (FK to List)
-- show_episodes
-- show_airdates Don't make it difficult on yourself.