# Related Collections

Related collections add support for editing **many-to-many** relationships with UI Builder. These are found when multiple entities from one collection are associated with multiple entities from another. They are modeled in a database via two tables related to a junction table.

A classic example is with `Students` and `Courses`. Each course has many students, and each student takes many courses.

![Child Collection](https://2059272600-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fj6FmBruCSGbXJIJB2aRu%2Fuploads%2Fgit-blob-dd4d9983acc366aa5f94c3c42b444047340d7bd8%2Frelated_collections_child.png?alt=media)

![Parent Collection](https://2059272600-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fj6FmBruCSGbXJIJB2aRu%2Fuploads%2Fgit-blob-5eb13764b05762cc8d2a0fb56cbc67a76041a1a4%2Frelated_collections_parent.png?alt=media)

![Entity Picker](https://2059272600-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fj6FmBruCSGbXJIJB2aRu%2Fuploads%2Fgit-blob-dc812be81e1e3f0b1cddf1641a352e3090d97533%2Frelated_collections_entity_picker.png?alt=media)

## Collections Representation

A representation of your collections would look like this:

![Related Collections Diagram](https://2059272600-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fj6FmBruCSGbXJIJB2aRu%2Fuploads%2Fgit-blob-007436a85bf855e8c75742d38691f6bacf3543ae%2Frelated_collections_diagram.png?alt=media)

And the entities would be represented using the following Models:

```csharp
[TableName("Students")]
[PrimaryKey("Id")]
public class Student
{
    [PrimaryKeyColumn]
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string Email { get; set; }
}
```

```csharp
[TableName("Courses")]
[PrimaryKey("Id")]
public class Course
{
    [PrimaryKeyColumn]
    public int Id { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }
}
```

```csharp
[TableName("StudentsCourses")]
[PrimaryKey(new[] { "StudentId", "CourseId" })]
public class StudentCourse
{
    [PrimaryKeyColumn]
    public int StudentId { get; set; }

    [PrimaryKeyColumn]
    public int CourseId { get; set; }
}
```

## Defining a related collection

You can get started with related collection through a two step process:

1. Add collection definition
2. Add related collection entity picker and definition

### Collection definition

Define a related collection by calling the `AddRelatedCollection` method on a given collection config builder instance.

### **AddRelatedCollection\<TEntityType, TRelatedEntityType, TJunctionEntityType>(Expression\<Func\<TRelatedEntityType, object>> idPropertyExpression, string nameSingular, string namePlural, Action\<RelationConfigBuilder\<TBuilder, TEntity, TRelatedEntityType, TJunctionEntityType>> relationConfig)**

Adds a related collection to the current collection with the given names, descriptions, and default icons. A property accessor expression is required for the entity ID field of the entity. The relation configuration will define the junction entity by specifying the references to parent and child entities.

```csharp
collectionConfig.AddRelatedCollection<Student, Course, StudentCourse>(x => x.Id, "Student Course", "Students Courses", relationConfig =>
{
    relationConfig
        .SetAlias("studentsCourses")
        .SetJunction<StudentCourse>(x => x.StudentId, y => y.CourseId);
});
```

### Configuring a related collection entity picker

Define the child collection entity picker by calling the `AddRelatedCollectionPickerField` method on the parent collection fieldset config.

### **AddRelatedCollectionPickerField\<TValueType>(string alias, string dataTypeName, string label)**

Adds an entity picker with the specified Data Type name to the editor of the parent collection.

```csharp
collectionConfig.Editor(editorConfig =>
{
    editorConfig.AddTab("General", tabConfig =>
        tabConfig.AddFieldset("General", fieldsetConfig =>
        {
            fieldsetConfig.AddField(x => x.FirstName).MakeRequired();
            fieldsetConfig.AddField(x => x.LastName).MakeRequired();
            fieldsetConfig.AddField(x => x.Email).MakeRequired();

            fieldsetConfig.AddRelatedCollectionPickerField<Course>("studentsCourses", "Courses Related Picker", "Courses");
        }));
});
```

{% hint style="info" %}
**Relation Config Alias:** The relation config alias must correspond to the related collection picker field alias! (e.g. `studentsCourses`)
{% endhint %}

## Defining repository methods

### **IEnumerable GetRelationsByParentIdImpl(int parentId, string relationAlias)**

Retrieves the related collections based on the ID of the parent entity.

```csharp
{
    var db = _scopeProvider.CreateScope().Database;
    var sql = db.SqlContext.Sql()
            .Select(new[] { "StudentId", "CourseId" } )
            .From("StudentsCourses")
            .Where($"studentId = @0", parentId);

    var result = db.Fetch<StudentCourse>(sql);

    return result;
}
```

### **StudentCourse SaveRelationImpl(StudentCourse entity)**

Adds a new related collection to the current parent entity.

```csharp
{
    var db = _scopeProvider.CreateScope().Database;

    var type = entity.GetType();
    var studentId = type.GetProperty("StudentId").GetValue(entity);
    var courseId = type.GetProperty("CourseId").GetValue(entity);

    // delete relation if exists
    db.Execute("DELETE FROM StudentsCourses WHERE StudentId = @0 AND CourseId = @1",
        studentId,
        courseId);

    db.Execute("INSERT INTO StudentsCourses (StudentId, CourseId) VALUES (@0, @1)",
        studentId,
        courseId);

    return entity;
}
```