How can I filter for records with one field equal to another one?

Question

My company has a Drupal 10 site that I have to interact with. I do not know Drupal very well.

I want to create a View showing records where a user's first name is equal to their last name. In this site's database, first and last names are stored in separate tables from the User table and foreign key'd to it via the User ID.

The SQL query to select the records I want is

SELECT DISTINCT
  users_field_data.uid,
  user__field_first_name.field_first_name_value,
  user__field_last_name.field_last_name_value
FROM users_field_data
LEFT JOIN user__field_first_name 
  ON user__field_first_name.entity_id = users_field_data.uid
LEFT JOIN user__field_last_name
  ON user__field_last_name.entity_id = users_field_data.uid
WHERE user__field_first_name.field_first_name_value = user__field_last_name.field_last_name_value

I can't find a method using Filters, Relationships, or Contextual Filters that will allow me to select for one Field being equal to another.

From searching, I've found this Drupal SE question/answer claiming that Contextual Filters will work for D9:

In contextual filter add the field you want to target.

Then: When the filter value is NOT available > Provide default value > Field value from current entity > under Entity Field select field which will filter the view results.

When I try to follow these instructions, I find nothing that I can identify as "Field value from current entity" under "Provide default value."

This Drupal SE answer is similar, saying

you want to add a contextual filter and under default value select php and then do a node_load or db_select to load the field from the node being viewed.

Again, I have "Provide default value" when adding a Contextual Filter, but nothing I can interpret as "select php and then do a node_load or db_select" following it.

This drupal.org comment is from 2016, so likely applying to Drupal 7 or maybe 8:

All you need for this is to add a filter, choose Globals, then find Field Comparison.

When I select "Globals" while adding a filter, there is no option for "Field Comparison."

Is there a way for me to select for one field equal to another using the tools of the web interface?

Answer 1

The Views field comparison module allows you to compare two fields. You can compare the user's first and last name, add a filter, choose Globals, and then find Field Comparison.

https://www.drupal.org/project/views_field_comparison

I updated dench0's sandbox project to work with Drupal 10/11 and published it as a full project. https://www.drupal.org/sandbox/dench0/2729421