1. We suggest you do NOT update to Joomla 3.8.10 until we can resolve an issue it causes with caching in Fabrik. If you do install it, you'll need to disable Joomla's "System Cache" in the global System settings.
  2. Apologies for the recent server outage, a planned migration by our host provider to a new location turned into a bit of a nightmare.

update_col with upsert

Discussion in 'Standard Support' started by jo-ka, Jul 11, 2018.

  1. jo-ka

    jo-ka Member

    Level: Standard
    Hello

    I'm using an update_col plugin on a list to update a status field on that list, as the image shows.
    upload_2018-7-11_8-49-39.png

    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.
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    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 ...

    Code (Text):

    $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
     
  3. jo-ka

    jo-ka Member

    Level: Standard
    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.
     
  4. troester

    troester Well-Known Member Staff Member

    Level: Standard
    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));
     
  5. jo-ka

    jo-ka Member

    Level: Standard
    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?
     
  6. troester

    troester Well-Known Member Staff Member

    Level: Standard
    Did you use the Post eval code field?
     
  7. jo-ka

    jo-ka Member

    Level: Standard
    Yes. And I get the loaded value and not the saved value...
     
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    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 ...

    Code (Text):

    $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
     
  9. jo-ka

    jo-ka Member

    Level: Standard
    OK, I'll try this.
    Thank's Hugh.
     

Share This Page