How create a custom views contextual filter?

Question

I've a site using Drupal 10.1.x.

I need to implement the following requisites:

1. Let the users choose their "most liked" nodes (bundle product). This bundle has a entity reference field to another node bundle company, field_company, and a entity reference field to a taxonomy vocabulary.

2. Display two views: on one show all the nodes excluding all the "liked" of a specific company, on the other show only the "liked" nodes of a specific company. Those two view are displayed on the taxonomy term via TVI. The taxonomy term has a entity reference field to the same bundle company which represents the value for the above filter.

So far, I've create a flag of module Flag, promoted_products with a field, field_company.

The flag field is correctly filled with the nid of the company referenced in the relative field of the flagged entity with a custom event subscriber.

I've also configured the first view to display on the taxonomy term, it is a copy of the default view taxonomy_term.

In addition, it has a relationship with Flags - "Include only flagged content" is not active for the first view (exclude flagged nodes of company x) and active for the second one (show only flagged nodes of company x).

Now, the problem I'm facing is how to filter only for the value for the company (or negate that)

The idea that came to me is a custom contextual filter to which I pass the default value "Taxonomy term ID from URL" and then add to the query a condition which in pseudo-sql would be

SELECT * from flagging__field_company fc
 JOIN taxonomy_term__field_company tc ON tc.field_company_target_id = fc.field_company_target_id
AND tc.entity_id = {contextual_filter_value} 

but alas I've found little documentation on how implement that.

What I fail to understand is especially how the mapper between Views and the SQL syntax works. My code so far breaks the view with a "SQL syntax error".

And by the way, is there any good documentation\tutorial available on this topic? I couldn't find any.

This is the code of the filter so far. I've taken as example this. I've start to try the query syntax looking at this.

my_module.module:

<?php

/**
 * Implements hook_views_data_alter().
 */
function my_module_views_data_alter(array &$data) {
  $data['flagging']['my_module_promoted_products'] = [
    'title' => t('my_module Promoted products'),
    'real field' => 'flag_id',
    'argument' => [
      'field' => 'flag_id',
      'title' => t('my_module Promoted products'),
      'id' => 'my_module_promoted_products_filter',
    ],
  ];
}

The plugin:

<?php

namespace Drupal\my_module\Plugin\views\argument;

use Drupal\views\Plugin\views\argument\ManyToOne;
use Drupal\views\Views;

/**
 * @ingroup views_filter_handlers
 *
 * @ViewsArgument("my_module_promoted_products_filter")
 */
class MyModulePromotedProductsFlaggingFilter extends ManyToOne {
  public function query($group_by = FALSE) {
    if (!empty($this->value)) {
      //todo what actually write here?
$configuration = [
        'table' => 'flagging_field_company',
        'field' => 'entity_id',
        'left_table' => 'flagging',
        'left_field' => 'id',
        'operator' => '=',
      ];
      $join = Views::pluginManager('join')
        ->createInstance('standard', $configuration);
      $this->query->addRelationship('flagging_field_company', $join, 'flagging');
      $this->query->addWhere('AND', 'flagging_field_company.field_company_target_id', $this->value, 'IN');
    }
  }

}

Thanks.