Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
ORM inserts with relationships
  • I haven't tried yet, but is it possible for inserts/updates with a relationship for many_many? Also when doing many_many, if the 'table_through' has more than 2 columns is it possible to get all the columns and use only the first two for cross referencing and others additional? Edit: Just noticed when inserting using the ORM if a column value is not given it tries to insert NULL, what if the table has a default value ? so instead of sending NULL skip that column if nothing is set so it will use the table default.
  • 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 ?
  • For me it's not entirely clear what you mean by 'passed from the lists table to the related one on insert'. Could you show some code to illustrate this?
  • 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.
  • Could you post your actual models to scrpyrd.com? This looks like it's been configured wrong.
  • 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.
  • 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.
  • 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!
  • 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?
  • 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.
  • Nice Topic... Now I want to combine relation table with Form Validation. Database: members
    -- id (auto, pk)
    -- email (unique)
    -- password member_details
    -- id (auto, pk)
    -- member_id (unique, fk to members)
    -- firstname
    -- lastname Only one row (has_one) of detail is linked to a member. Controller, Models and View: http://scrp.at/6a362395f0ed22188f22b38f6634169fc17df9f7 I simplified the view and left out the observers etc. I have a few questions: * can line 16 and 17 be done better?
    * do I have to add member_detail[firstname] as input name (also for lastname)? if you have related tables with the same fieldnames for example
    * what is the best way of doing this? In mind that you have more then one relation later on...
    * is there a way to do a real join like natural join? I mean like an extend so it looks like its only one table?
    * is there a way to add protected fields to the model? for example if someone adds a input to the html and submits it... and also as attribute for the add_model() in the validation so that you can have one model to a table but multiple possibilities to edit the table securing only the fields that you want to edit in that particular form
  • 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.
  • 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();
    
  • 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 ?
  • When saving relations like this, the apropriate foreign keys will be set automaticly.
  • 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?
  • No, that just means you didn't make it auto-increment.
  • 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.
  • 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.
  • 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.
  • It was never intended to work like this, a foreign key and primary key aren't expected to be the same thing. You can do this but you'll have to solve all the troubles stemming from that yourself. Each table is expected to have its own pk, if you want to fetch a one-one relation by its foreign key that's no problem (make it unique to be sure) and no reason not to index it. Your way is possible, but you'll have to save the List first and create the Music after and attach the just created List & its ID later.
  • 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.
  • Thanks for all the information both of you. I'll work on restructuring the database. So After inserting to 'List' table, what would be the best method I could use to get the 'list_id' to be inserted into the 'Music' table ?

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion