Loading sub sections with a titles

Question

I’m doing a few left_joins trying to preload some nested associations. I have sections, that have sub_sections, and I want to get all sub_sections that have a title (as opposed to sub_sections with no title). When I add this line, the whole query returns nil, but when I remove it, I get everything (including the sub_sections with no titles). where: not is_nil(sub2.title).

I'm having a hard time finding what I'm doing wrong here? I feel like I should be getting the results with this.

Any help would be much appreciated. Thanks!

from a in Article,
  left_join: s in assoc(a, :sections),
  left_join: d in assoc(s, :definitions),
  left_join: sub in assoc(d, :sub_sections),
  left_join: sub2 in assoc(sub, :sub_sections),
  where: a.id == ^id,
  where: not is_nil(sub2.title),
  preload: [sections: {s, [definitions: {d, [sub_sections: {sub, sub_sections: sub2}]}]}]```

Answer 1

You should apply the condition not is_nil(sub2.title) onto the respective join, not onto the whole query. Somewhat like the below should work.

subs = from sub in SubSection, where: not is_nil(sub.title)

from a in Article,
  left_join: s in assoc(a, :sections),
  left_join: d in assoc(s, :definitions),
  left_join: sub in assoc(d, :sub_sections),
  left_join: sub2 in ^subs, on: sub2.parent == sub.id,
  where: a.id == ^id,
  preload: [sections: {
    s, [definitions:
     {d, [sub_sections: {sub, sub_sections: sub2}]}]}]