Is there a DTE way to create 2 matching IDs in 2 separate tables when you add a row

koniahin

I have a table which I refer to as a primary table because it is used in many different forms. It forms the basis on articles, blog posts, FAQ, testimonials other. Many, most of these share use of the same common fields. This works very well.

However I have an events form which the same core but only for this form there are about 30 additional fields. What I would like to do is move all the events-related fields to a separate table and link the 2 tables by a common ID. Something like articles and events tables

articles table:
id
title
slug
publish
etc.

events table:
id
event_title
event_date
event_time
etc.

simple query:

select
articles.id,
articles.title,
articles.slug,
events.event_title,
events.date,
events.time
from articles, events
where articles.ID = events.ID
-- and articles.ID = 100

Pretty simple query. What this does is eliminate a bunch of unneeded rubble from the articles table. The question is, when you create a NEW entry in DTE can you make it create the same ID in 2 tables?

rf1234

However I have an events form which the same core but only for this form there are about 30 additional fields. What I would like to do is move all the events-related fields to a separate table and link the 2 tables by a common ID. Something like articles and events tables

Hope you haven't implemented that yet.
You seem to have two entities: "article" and "event". If an event cannot stand alone AND can only be associated with one article AND an article may have an event but doesn't have to, then it makes sense to have event in a separate table. This would be a 1:1 relationship with event being optional. article = parent and event = child.

If that is your use case the id of "article" would be the foreign key in "event". Of course event would still have its own auto-increment id but the foreign key in addition.

Such a model is supported by a leftJoin.
https://editor.datatables.net/manual/php/joins

if "event" can stand alone without "article" and and event can be associated with multiple articles then you have a N:M relationship and you would require a link table between "event" and "article". That would be a table "article_has_event" or vice versa and this table would only cotain the two id's from "event" and "article".
This is supported by an MJoin.
https://editor.datatables.net/manual/php/mjoin

Code example for leftJoin.
I have a parent table and this parent table has 5 child tables like your "event" table in the first scenario. All of those optional child tables have the parent table id as a foreign key. In addition one of those children has another parent "sub_partner". So "sub" holds two foreign keys "sub.ctr_id" and "sub.sub_partner_id".

Editor::inst( $db, 'ctr' )
....
....
->leftJoin( 'sub',              'ctr.id', '=', 'sub.ctr_id')
->leftJoin( 'sub_app',          'ctr.id', '=', 'sub_app.ctr_id')
->leftJoin( 'sub_exec',         'ctr.id', '=', 'sub_exec.ctr_id')
->leftJoin( 'sub_proof',        'ctr.id', '=', 'sub_proof.ctr_id')
->leftJoin( 'sub_acct_data',    'ctr.id', '=', 'sub_acct_data.ctr_id')
->leftJoin( 'sub_partner',      'sub.sub_partner_id', '=', 'sub_partner.id')        

koniahin

Articles can stand alone, events cannot. With what I described events does not need an auto_increment id, just an ID that corresponds to an identical ID in articles. The relationship is simple. You can call it foreign key.

I am familiar with left joins but not used this way. What you are describing here - when you select to add a new event record will that add the record in both tables simultaneously?

rf1234

With what I described events does not need an auto_increment id, just an ID that corresponds to an identical ID in articles.

Of course you can go without another auto-increment id. It might even be feasible with data tables to have an id field which at the same time is a foreign key. Don't know. Just read the docs please and figure it out!

What you are describing here - when you select to add a new event record will that add the record in both tables simultaneously?

Of course, otherwise Editor would be pretty much useless, I guess (I am not associated with Data Tables and I am not receiving compensation from them.)
In my example above it adds records in up to six tables simultaneously depending on the Editor fields filled out. I also use five different Editor instances for the same Data Table. No problem.

koniahin

In the past when I have used left join it has been with secondary table that may not have a joined entry. Getting closer. When I update an existing row it works perfectly, adds the corresponding id and field values to the events table.

However, if I go to add a new row upon save it give a

Undefined index: articles.rowOrder

error.

In the original form creating new worked fine. I added a single left join line:

->leftJoin('events', 'articles.id', '=', 'events.id')

koniahin

rowOrder is there in the controller file:

->fields(

Field::inst('articles.id'),
Field::inst('articles.rowOrder'),
Field::inst('articles.uid'),
Field::inst('articles.author'),
...

koniahin

articles.rowOrder is a valid field in the database. I tried rearranging it sequentially but that makes no difference. Here is what web developer says:

fieldErrors []
error "Undefined index: articles.rowOrder"
data []
ipOpts []
cancelled []

Picture:

rf1234

If I am not mistaken "Undefined index" comes from PHP. So PHP doesn't have the index at some point even though you sent it to the server.
Try ->debug(true) to see the SQL statements generated by Editor.
Might not be possible though in case the PHP error comes to "early" in the processing.

allan

"Undefined index" isn't something that we specifically throw as an error, so it must be the generic PHP error. Perhaps you can so us your PHP code please? I'm wondering if you are referring to $data['articles.rowOrder'] rather than $data['articles']['rowOrder'] some where?

More generally for the rest of the discussion - I haven't used Editor in quite the way you suggest. I always make tables with their own unique primary key and then have a column in the table referring to it as a foreign key (rather than sharing the same value). That said, the sequence the code executes in Editor should be okay for what you are doing - so although I haven't specifically tried that, it looks like it should work.

Allan

koniahin

I tracked the problem down to this text in the controller file:

->on('preCreate', function ($editor, $values) {
  $editor->db()
  ->query('update', 'articles')
  ->set('articles.rowOrder', 'articles.rowOrder+1', false)
  ->where('articles.rowOrder', $values['articles.rowOrder'], '>=')
  ->exec();
})
->on('preRemove', function ($editor, $id, $values) {
  $order = $editor->db()
  ->select('articles.articles', 'rowOrder', array('articles.id' => $id))
  ->fetch();
  $editor->db()
  ->query('update', 'articles')
  ->set('articles.rowOrder', 'articles.rowOrder-1', false)
  ->where('articles.rowOrder', $order['articles.rowOrder'], '>')
  ->exec();
})

When I comment it out the error goes away, however that results in the new record getting assigned the rowOrder ID of 1 and breaks row reordering. In the forms that work without the left join I didn't have to attach the table_name to each field, so like:

Field::inst('id'),
Field::inst('rowOrder'),
Field::inst('uid'),
Field::inst('author'),

rather than

Field::inst('articles.id'),
Field::inst('articles.rowOrder'),
Field::inst('articles.uid'),
Field::inst('articles.author'),

allan

Line 5 in your code there. I can see the issue I mentioned in my previous post. Try changing it to the format I suggested which should help.

Also line 15, $order think might need to just '$order['rowOrder']` since there is no table prefix in the query for the column.

Allan

koniahin

I found a line where I missed adding articles to the field. Added that:

->on('preCreate', function ($editor, $values) {
  $editor->db()
  ->query('update', 'articles')
  ->set('articles.rowOrder', 'articles.rowOrder+1', false)
  ->where('articles.rowOrder', $values['articles.rowOrder'], '>=')
  ->exec();
})
->on('preRemove', function ($editor, $id, $values) {
  $order = $editor->db()
  ->select('articles', 'articles.rowOrder', array('articles.id' => $id))
  ->fetch();
  $editor->db()
  ->query('update', 'articles')
  ->set('articles.rowOrder', 'articles.rowOrder-1', false)
  ->where('articles.rowOrder', $order['articles.rowOrder'], '>')
  ->exec();
})

However, I looked at line 5, also line 15. There is no $order line anywhere in the code. You need to be specific with me as I am not familiar with this style of coding.

I found something else that escapes me - in table view. In table view I display 8 columns (including rowOrder). The columns should be a combination of items from articles and also from the events table. If I add the events items into the form fields it works fine, however if I add an event.$field into a column it errors out with:

DataTables warning: table id=leftJoin - Requested unknown parameter 'events.event_contact_name' for row 0, column 5. For more information about this error, please see http://datatables.net/tn/4

The test line:

  {
    data: "events.event_contact_name"
  },

koniahin

Ignore the other issue, data: "events.event_contact_name", think I have that working.

colin

The $order that Allan was referring to on line 15 of the code you posted above:

  ->where('articles.rowOrder', $order['articles.rowOrder'], '>')

He suggested changing $order['articles.rowOrder'] to be $order['rowOrder']

Colin

koniahin

I made that change, It now reads:

// ROWORDER BLOCK
->on('preCreate', function ($editor, $values) {
  $editor->db()
  ->query('update', 'articles')
  ->set('articles.rowOrder', 'articles.rowOrder+1', false)
  ->where('articles.rowOrder', $values['articles.rowOrder'], '>=')
  ->exec();
})
->on('preRemove', function ($editor, $id, $values) {
  $order = $editor->db()
  ->select('articles.articles', 'rowOrder', array('articles.id' => $id))
  ->fetch();
  $editor->db()
  ->query('update', 'articles')
  ->set('articles.rowOrder', 'articles.rowOrder-1', false)
  ->where('articles.rowOrder', $order['rowOrder'], '>')
  ->exec();
})
// ROWORDER BLOCK

Still getting: Undefined index: rowOrder

Guessing, also changed line 5 to:

  ->where('articles.rowOrder', $values['rowOrder'], '>=')

That as well did not help. This only occurs when creating a new row. Editing an existing row is fine.

koniahin

Also if relevant, here is the rowOrder code in the javascript/html file:

.on("postCreate postRemove", function() { table.ajax.reload(null, false); })
.on("initCreate", function() { editor.field("articles.rowOrder").enable(); })
.on("initEdit", function() { editor.field("articles.rowOrder").disable(); });

koniahin

One other note if in the controller file I enable the ROWORDER BLOCK, rowOrdering correctly works in the form.

Before:

MariaDB> select id, rowOrder, title, page_type from articles;
+----+----------+----------------------+-----------+
| id | rowOrder | title                | page_type |
+----+----------+----------------------+-----------+
|  1 |        1 | My First Event       | event     |
|  2 |        2 | Ali vs Frazier       | event     |
|  3 |        3 | Woodstock            | event     |
|  4 |        4 | World Series of Rock | event     |
|  5 |        5 | carnival             | event     |
|  6 |        6 | Festival             | event     |
+----+----------+----------------------+-----------+
6 rows in set (0.000 sec)

After:

MariaDB> select id, rowOrder, title, page_type from articles;
+----+----------+----------------------+-----------+
| id | rowOrder | title                | page_type |
+----+----------+----------------------+-----------+
|  1 |        4 | My First Event       | event     |
|  2 |        5 | Ali vs Frazier       | event     |
|  3 |        6 | Woodstock            | event     |
|  4 |        3 | World Series of Rock | event     |
|  5 |        2 | carnival             | event     |
|  6 |        1 | Festival             | event     |
+----+----------+----------------------+-----------+
6 rows in set (0.000 sec)

koniahin

I think we have something now. I found some code that my friend used in another form. I copied it in and tested in several different forms including events with left join. So far it seems to be working and that includes row reordering and the left join.

// ROWORDER BLOCK
->on('preCreate', function ($editor, $values) {
  if (!$values['articles']['rowOrder']) {
    $next = $editor->db()->sql('select IFNULL(MAX(rowOrder)+1, 1) as next FROM articles')->fetch();
    $editor->field('articles.rowOrder')->setValue($next['next']);
  } else {
    $editor->db()
    ->query('update', 'articles')
    ->set('rowOrder', 'rowOrder+1', false)
    ->where('rowOrder', $values['articles']['rowOrder'], '>=')
    ->exec();
  }
})
->on('preRemove', function ($editor, $id, $values) {
  $order = $editor->db()
  ->select('articles', 'rowOrder', array('id' => $id))
  ->fetch();

  $editor->db()
  ->query('update', 'articles')
  ->set('rowOrder', 'rowOrder-1', false)
  ->where('rowOrder', $order['rowOrder'], '>')
  ->exec();
})
// ROWORDER BLOCK

allan

It should actually be:

$values['articles']['rowOrder']

i.e. it is based on the field name (which in this case appears to be articles.rowOrder) and split into a 2D array. Sorry I wasn't clear on that before.

Allan