How to create joins in visual designer with many to many relationship

So I have 3 Data Types. A district can represent more than one county and a county can have more than one district.

County

CountyName (String)

District

DistrictName (String) Countys (Multi Select Dropbox)

Candidate

Name (String) DistrictName (String)

I want the template to generate

County A

-->District 1

--->John

--->Bill

--> District 2

----->Susan

County B

-->District 1

---->John

----->Bill

--> District 3

----->Jack

----->Jane

Is there any way to use the visual editor and or Razor to achieve the many to many relationship needed for this?

Thanks!

Michael

Solution

So here's what I did ... I ended up pulling the full queries then using value filters inside nested loops.

It would be really nice if we could simply pass parameters from razor to a sqldatasource but since that doesn't seem to be available we get something like this:

@using ToSic.Eav.DataSources

var allCounties = App.Query["Counties"]["Counties"];
var allHouseDistricts = App.Query["Districts"]["MDHouseDistricts"];

@foreach (var thisCounty in AsDynamic(allCounties.List)) {
@thisCounty.CountyName

@{
var someHouseDistricts = CreateSource<ValueFilter>(allHouseDistricts);
someHouseDistricts.Attribute = "DistrictCounties";
someHouseDistricts.Value = thisCounty.CountyName;
someHouseDistricts.Operator = "contains";
}

@foreach (var thisHouseDistict in AsDynamic(someHouseDistricts.List)) {
@thisHouseDistict.HouseDistrictName
}
}

Here is the page that we built: https://www.mdrealtor.org/Legislative/Resources/Home-Ballot