update_col with upsert

[jo-ka]

Hello

I'm using an update_col plugin on a list to update a status field on that list, as the image shows.
View attachment 17332

I'm also using the ability to email user on update date and update user functions, so I'm happy with it.

But I also need to update a field on a different list when the user click this button. I tried to set an upsert on the form but, as far as I understand, this plugin doesn't use the form post method to execute.

Is there a way to achieve this?

Thanks in advance.

 

[cheesegrits]

You'll have to handle that yourself in the post-update PHP code for the update_col.

The row data for the selected rows will be in $data, as an array of groups of arrays of row objects (even if your list isn't grouped, it'll still be a single entry array). So something like ...

$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach ($data as $group) {
   foreach ($group as $row) {
      // you can now access $row->yourtable___yourelement, PK will be in $row->__pk_val
      $query->clear()->update('sometable')->where('some_fk = ' . (int)$row->__pk_val)->set('some_field = ' . $db->quote($row->yourtable___something));
      $db->setQuery($query);
      $db->execute();
   }
}

return "some informative message";

You can return text which will be shown in a popup on the list when the update_col completes.

As usual, depending on the element type (if it has the concept of 'label' and 'value', like dropdowns or joins, etc), you may need to use the _raw name, $row->yourtable___something_raw.

-- hugh

 

[jo-ka]

Thanks hugh.
I'm trying to accomplish this but I might be missing something.

So, to be clear, I must put the code on the Post Eval field on the update separator of the update_col plugin, right?
Considering that it's correct, let's assume this scenario...

My origin list is fab_originlist and have for example 3 fields: id | number | status
My destination list is fab_destination and have 4 fields: id | number | name | status

So, my SQL query would be something like (I know the query is not correct because I need the JOIN, but it's just to simplify):

update fab_destination set fab_destination.status = fab_origin.status where fab_destination.number = fab_origin.number

So my PK on origin table is fab_origin.number and my fk on destination is fab_destination.number

When using your code:

$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach ($data as $group) {
foreach ($group as $row) {
// you can now access $row->yourtable___yourelement, PK will be in $row->__pk_val
$query->clear()->update('fab_destination')->where('number = ' . (int)$row->__origin.number->set('status = ' . $db->quote($row->fab_origin___status));
$db->setQuery($query);
$db->execute();
}
}

return "some informative message";

I thing I'm making something wrong with the __pk_val placeholder. Is this OK with just 2 __? shall I prepend the PK with the table name like fab_origin___number?

Sorry for this, but I'm not able to make this work.

Thanks in advance.

 

[troester]

If your "joining" column is fab_destination.number = fab_origin.number (so not FK to PK, you couldn't use this as join in a Fabrik list but you can use it in a query) I think your code should be
$query->clear()->update('fab_destination')->where('number = ' . (int)$row->fab_origin___number->set('status = ' . $db->quote($row->fab_origin___status));

 

[jo-ka]

If your "joining" column is fab_destination.number = fab_origin.number (so not FK to PK, you couldn't use this as join in a Fabrik list but you can use it in a query) I think your code should be
$query->clear()->update('fab_destination')->where('number = ' . (int)$row->fab_origin___number->set('status = ' . $db->quote($row->fab_origin___status));

OK, I was able to get this working... sort of.

The problem is that the value that is passed to the destination table is the list loaded value and not the updated one. I need to update the destination table with the new value updated by the update_col plugin.

Is this possible?

 

[troester]

Did you use the Post eval code field?

 

[jo-ka]

Did you use the Post eval code field?

Yes. And I get the loaded value and not the saved value...

 

[cheesegrits]

Ah, yes, the $data array won't have the updated data, it'll just have the data as read from the table prior to applying the updates.

So you'll have to join the original row ...

$query->clear()
   ->update('fab_destination')
   ->leftJoin('fab_origin ON fab_origin.number = fab_destination.number)
   ->where('fab_origin.number = ' . (int)$row->fab_origin___number)
   ->set('fab_destination.status = fab_origin.status');

This assumes 'number' is a unique foreign key, ie. each table only has one, they are a one-to-one relationship.

-- hugh

 

[jo-ka]

OK, I'll try this.
Thank's Hugh.