Cant create View with Many to Many Relationship

Question

I have a situation with three content types. (Simplified)

A Person is referenced in many Transactions, and a Transaction also has a reference to a Company. I.e. Person -< Transactions >- Company

Here the Transaction holds the entity reference to a Person, and a reference to a Company. I need to create a view that given a value from the Company content type, returns all the Person entities.

In SQL I would do something like this, to give me Persons who have had Transactions with a specific company:

  SELECT P.*
    FROM Person P
    JOIN Transaction T on T.PersonId = P.Id
    JOIN Company C on C.Id = T.CompanyId
    WHERE C.Name = 'Drupal Co.'

Answer 1

It's quite simple: Create a view which shows content of type Person (or users, if for Person you mean users).

Now let's say that Transaction has an Entity Reference field named field_person and another one named field_company.

In the view's Advanced section click on Add relationship, search for field_student and check "A bridge to the Content entity that is referencing Content via field_person" if Person as a node type or "A bridge to the Content entity that is referencing User via field_person" if Person is a user. In the configuration window that pop ups check "Require this relationship" and save. You have just configured the first join.

Add another relationship. How that you have configured the first one, you can search for field_company and check "A bridge to the Content entity that is referenced via field_company". In the configuration window click on "Require this relationship" and check that the Relationship combobox has the first relationship selected. Now you have configured also the second join.

Finally create a Contextual filter. Search for "Content: Nid" and check it. In the configuration window, the Relationship combobox must have the second relationship selected. Choose what to do "When the filter value is NOT available" (this depends on what you want to do).

Done!

EDIT: I noticed just now that you search Company by name, not by NID. Just change the field in the contextual filter!