View for selecting an image associated with a node's child node?

Question

I am trying to compose a certain view. I have a content type lesson that contains exercise content types. Exercises reference a lesson nid through an entity reference field. Exercises have images. For a given lesson node, I am trying to build a view that returns the images associated with a lesson's exercises, and then choose one at random.

I couldn't figure out how to make Views return such a result set. And if someone can just explain this in SQL terms, irrespective of view, there is a fiddle: http://sqlfiddle.com/#!9/dbf63

Regards

Answer 1

Haven't figured out how to make Views return what I want but it seems this works, where I would pass the query the lesson node. But I really want to understand how to make Views produce the desired result!

SELECT * 
FROM file f
INNER JOIN tab t on t.field_tab_fid = f.fid
INNER JOIN lesson l ON l.entity_id = t.entity_id
WHERE l.field_lesson_target_id = 672

http://sqlfiddle.com/#!9/dbf63/4

Answer 2

The problem seems to be back-referencing missing on lesson to exercise content type. You can try any of the two ways.

1. Create view on exercise content type, add relationship on entity reference field to lesson, so that you will get lesson content types fields as well, now in your views you will have option to show field from any of two content types exercise or lesson(using relationship). But problem will comes with lessons who are not referenced by any exercise, those will not be listed.

2. Setup a back referencing field on lesson content type, which automatically creates reference of exercise content type on lesson node when user creates referencing on exercise node. There is module Corresponding Entity Relationship which does the same.