Loading content in a View via a sibling entity reference

Question

I think what I need to do is use one Drupal View's results to filter the results of another view. Here is what I am trying to accomplish.

I have 3 content types (node types): Blog, Blog Post, Biography Page (aka Blog Author)

The blog post contains an entity reference to both the blog it should be in and one or more author's biography pages.

What I need to be able to do is take the ID for a given blog and get back all the authors that are associated via an associated blog post. Then remove any duplicates to show a list of all authors under a particular "blog".

I think I might be able to use a relationship. But still fuzzy on how that would work.

How can I accomplish this?

---

UPDATE: To clarify, I need to start with the Blog ID and using that find the authors of any blog posts that associate to said blog entity ID. Then take that resultset and dedupe it. I'm not worried about deduping. I just need help figuring out how to load the authors via that relationship.

Here is a diagram of this:

Thanks!

Answer 1

I'm going to assume that this list of authors would appear, e.g., as a block on a blog post page, as in "Other authors associated with this post's blog." (I'm also assuming you're using Drupal 8 or 9)

You can do this with relationships:

1. The first relationship is through the blog post's blog ID field to get the blog
2. The next relationship uses the first with a "reverse" relationship through the same field to find all blog posts referencing that blog
3. The third relationship uses the second to get the authors through the Author ID

Here's a step-through I recommend using fields.

1. Add the first relationship. Give it a good readable "Administrative Title" because Drupal's built-in names become arcane very fast.
2. To check add the title field of the blog to confirm you've got what you expect. (You can always remove it later if you don't need it.)
3. Add the second relationship, being sure to use the first, and also give it a good name. Again use a field from the new relationship as a sanity check.
4. Add the third relationship being sure to use the second ...

You probably want to check the "require relationship" box in each case.

Yeah, I can see you getting a ton of duplicates that you'll have to remove. You may be able to get rid of some using Views' UI, but that's always dicey. You'll probably have to employ a view hook to clean up things.

Answer 2

Alright, so after dissecting Drupal Views a bit more and the query from it, I did this (which honestly was inspired by the suggestion in the answer from @beltouche).

So I setup a relationship using the blog post authors reference field. Set it as required. Then I setup a second required relationship via the field that referenced the blog and had it use the first relationship. This second relationship then gets used by a contextual filter that accepts the value of the blog ID and finds results that way.

Next, I found that the query was really close and just needed two joins modified. So I hacked it using query alter (details below). Finally, I set "Use aggregation" to "Yes".

Here is my code:

<?php
function my_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'authors' && $view->current_display == 'authors_eva') {
    /*
     * Basically what I did here is forced it to build the relationships in the way I know works
     * which I tested by manually rewriting the query and testing against the database.
     */
    $table_authors_field_data = $query->getTableInfo('blog_post_authors_node_field_data');

    if (!empty($table_authors_field_data)) {
      $table_authors_field_data['join']->leftField = 'blog_post_authors_target_id';
    }

    $table_node_blog = $query->getTableInfo('blog_post_authors_node_field_data__node__blog');

    if (!empty($table_node_blog)) {
      $table_node_blog['join']->field = 'entity_id';
      $table_node_blog['join']->leftField = 'entity_id';
      $table_node_blog['join']->leftTable = 'node__blog_post_authors';
    }
  }
}