Editing left joined tables values

Editing left joined tables values

TronikTronik Posts: 80Questions: 18Answers: 0

I cannot get my head around on how to set up serverside script to be able to edit joined tables values.

Main table: products
Joined table: attributes

Server:

Field::inst( 'attributes.color' )

->leftJoin( 'attributes',     'attributes.id',   '='  , 'products.attributes' )

Column:

{
  label: "color:",
  name:  "attributes.color"
 },

If I manually in database create a record in attributes table, assign thats row ID to products.attributes field, it shows in editor, but when editing value nothing happens, it looks like its ok no error, but value does not change.
I also need to be able to create new attribute-rows if no one exists for that product.

Answers

  • TronikTronik Posts: 80Questions: 18Answers: 0

    After some searching I found that the ID of the joined table must be submitted.
    How would I go about that?

    This doesnt work:

    Field::inst( 'products.attributes' )
    ->setValue('attributes.id'),
    

    I could include hidden field column only the problem is i have formOptions: changed

  • allanallan Posts: 52,836Questions: 1Answers: 8,106 Site admin

    Remove the setValue(). That would try and set it to the string attributes.id (not a lookup of that value).

    Also, you are right, you would have to use formOptions: 'allIfChanged' for this to work.

    Generally I strongly recommend not editing the joined tables directly from the master. Consider for example if you change which row is linked to, but also change values inside it and other fields link to that same row. You'd need to refresh the whole table to make sure you have the latest data.

    Allan

  • TronikTronik Posts: 80Questions: 18Answers: 0

    I see, I guess I’ll change database structure to just one giant table then?

    Or do you have another suggestion to set up database?
    Is it not possible to have linked tables that are all editable with Editor?

  • allanallan Posts: 52,836Questions: 1Answers: 8,106 Site admin

    You can have linked tables for certain - but generally I suggest having one Editor per table. It really depends what the relationships between your tables are - for example this blog post shows how you can edit content for linked "child" values.

    But if you are using 1:1 links across multiple database tables for a single row in a DataTable, with no way to change the reference between the rows (i.e. tableA row 1 can only ever reference tableB row 1, and nothing else can), then no, Editor wouldn't work particularly well for that.

    What is your database structure?

    Allan

  • TronikTronik Posts: 80Questions: 18Answers: 0

    Hi,

    My database consist of products, where I have the basic fields such as name, price, sku and so on.
    There are about 40 more fields, which (at least I tought) would be better to group into other tables, for example attributes, media, specifications etc tables.

    Another reason is that the products are accessible through api, where other applications may GET data. And the JSON response structure would look better that way instead of only one object.

    Using joined table such as the example in the blog post would work I guess, but a little messy having multiple instances like that and I want the edit views to look like one not multiple tables, being edited. Also Im using onpagedisplay plugin.

    Thanks for your inputs

Sign In or Register to comment.