8 – Retrieve the latest published version (moderation state) of the node during the transition to published

I want to get the latest published review of a node whose moderation status of the content is "published." The code below works well until I put a node in the "published" state, at which point it can not find anything

                $ query = Drupal :: database () -> select (& # 39; content_moderation_state_field_data & # 39; ms);

$ query-> join ('node_field_revision', 'nf', 'nf.nid = ms.content_entity_id AND nf.vid = ms.content_entity_revision_id AND nf.langcode = ms. langcode & # 39;);

// Join table so that we can check the type of content this node is an entity.
$ query-> join ('node_field_data', 'nd', 'nd.nid = nf.nid');

$ query-> fields (& # 39; nf), ['nid', 'vid'])

// Compare moderation states in the site workflow.
$ query-> condition (& # 39; ms.workflow & # 39 ;, & # 39; site_content & # 39;);

// The node must be in a state of moderation published in the site workflow.
$ query-> condition (& # 39; ms.moderation_state & # 39 ;, $ moderation_state);

// Content Type of node.
$ query-> condition ('nd.type', $ bundle_id);

return $ query-> execute () -> fetchAll ();

How can I get this to return the latest published version of a node when the state goes to published?

This code is called by a service called from hook_entity_type_update ().