Editing left joined tables values

Tronik

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.

Tronik

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

allan

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

Tronik

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?

allan

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

Tronik

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

Tronik

Any thoughts on this?

allan

Thanks for the explanation - apologies for having missed your reply.

What you could do here is make use of the preCreate server-side event to do the insert (or preEdit for the edit action). You'd need to insert into your linked table, get the new primary key from that table and then use that as the value for the column in the host table referencing the other table.

So yes, it is possible, but it wouldn't be straight forward I'm sorry to say.

Allan

Tronik

I think I'll just go for one big table instead, seems like too much workaround for just making it look nice.

Thank you