Self Referencing Many-to-Many relations
It's not possible to have just one collection with relations. You need two - one with relations the ticket equals TicketFrom and second with relations the ticket equals TicketTo.
Something like this:
Model:
public class Ticket
{
public int Id { get; set; }
public string Title { get; set; }
public virtual ICollection<Relation> RelatedTo { get; set; }
public virtual ICollection<Relation> RelatedFrom { get; set; }
}
public class Relation
{
public int FromId { get; set; }
public int ToId { get; set; }
public virtual Ticket TicketFrom { get; set; }
public virtual Ticket TicketTo { get; set; }
}
Configuration:
modelBuilder.Entity<Relation>()
.HasKey(e => new { e.FromId, e.ToId });
modelBuilder.Entity<Relation>()
.HasOne(e => e.TicketFrom)
.WithMany(e => e.RelatedTo)
.HasForeignKey(e => e.FromId);
modelBuilder.Entity<Relation>()
.HasOne(e => e.TicketTo)
.WithMany(e => e.RelatedFrom)
.HasForeignKey(e => e.ToId);
Note that a solution using Parent is not equivalent, because it would create one-to-many association, while if I understand correctly you are seeking for many-to-many.
Entity Framework Core 3.0 - Creating a self-referencing many to many relationship
In EF Core it is necessary to include an entity in the model to
represent the join table in the M:N relation, and then add navigation properties to either
side of the many-to-many relations that point to the join entity.
The new tableS:
public class InformationSystem
{
public InformationSystem()
{
}
[Key()]
public virtual int InformationSystemID { get; set; }
public virtual string InformationSystemName { get; set; }
public virtual ICollection<InformationSystemRelation> Systems { get; set; }
public virtual ICollection<InformationSystemRelation> ParentSystems { get; set; }
}
public class InformationSystemRelation
{
public int ParentId { get; set; }
public InformationSystem Parent { get; set; }
public int ChildId { get; set; }
public InformationSystem Child { get; set; }
}
The mapping:
modelBuilder.Entity<InformationSystemRelation>()
.HasKey(x => new { x.ParentId, x.ChildId });
modelBuilder.Entity<InformationSystemRelation>()
.HasOne(x => x.Parent)
.WithMany(x => x.Systems)
.HasForeignKey(x => x.ParentId)
.OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<InformationSystemRelation>()
.HasOne(x => x.Child)
.WithMany(x => x.ParentSystems)
.HasForeignKey(x => x.ChildId)
.OnDelete(DeleteBehavior.Restrict);
The whole sample:
class Program
{
static void Main(string[] args)
{
var db = new MyDbContext();
var is1 = new InformationSystem() { InformationSystemName = "is1" };
var is2 = new InformationSystem() { InformationSystemName = "is2" };
var is3 = new InformationSystem() { InformationSystemName = "is3" };
var is4 = new InformationSystem() { InformationSystemName = "is4" };
db.InformationSystems.Add(is1);
db.InformationSystems.Add(is2);
db.InformationSystems.Add(is3);
db.InformationSystems.Add(is4);
db.SaveChanges();
var r1 = new InformationSystemRelation() { ParentId = 1, ChildId = 2 };
var r2 = new InformationSystemRelation() { ParentId = 1, ChildId = 3 };
var r3 = new InformationSystemRelation() { ParentId = 4, ChildId = 2 };
var r4 = new InformationSystemRelation() { ParentId = 2, ChildId = 3 };
var r5 = new InformationSystemRelation() { ParentId = 2, ChildId = 4 };
db.InformationSystemRelations.Add(r1);
db.InformationSystemRelations.Add(r2);
db.InformationSystemRelations.Add(r3);
db.InformationSystemRelations.Add(r4);
db.InformationSystemRelations.Add(r5);
db.SaveChanges();
var o2 = db.InformationSystems.Include(x => x.Systems).Include(x => x.ParentSystems).Single(x => x.InformationSystemID == 2);
}
}
public class InformationSystem
{
public InformationSystem()
{
}
[Key()]
public virtual int InformationSystemID { get; set; }
public virtual string InformationSystemName { get; set; }
public virtual ICollection<InformationSystemRelation> Systems { get; set; }
public virtual ICollection<InformationSystemRelation> ParentSystems { get; set; }
}
public class MyDbContext : DbContext
{
public DbSet<InformationSystem> InformationSystems { get; set; }
public DbSet<InformationSystemRelation> InformationSystemRelations { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<InformationSystem>(entity =>
{
modelBuilder.Entity<InformationSystemRelation>()
.HasKey(x => new { x.ParentId, x.ChildId });
modelBuilder.Entity<InformationSystemRelation>()
.HasOne(x => x.Parent)
.WithMany(x => x.Systems)
.HasForeignKey(x => x.ParentId)
.OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<InformationSystemRelation>()
.HasOne(x => x.Child)
.WithMany(x => x.ParentSystems)
.HasForeignKey(x => x.ChildId)
.OnDelete(DeleteBehavior.Restrict);
});
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("data source=(local)\\SQLEXPRESS;Initial catalog=Test;Integrated security=SSPI");
base.OnConfiguring(optionsBuilder);
}
}
public class InformationSystemRelation
{
public int ParentId { get; set; }
public InformationSystem Parent { get; set; }
public int ChildId { get; set; }
public InformationSystem Child { get; set; }
}
Many-to-many self referencing relationship
The post you are following is definitely wrong.
Every collection or reference navigation property can only be a part of a single relationship. While many to many relationship with explicit join entity is implemented with two one to many relationships. The join entity contains two reference navigation properties, but the main entity has only single collection navigation property, which has to be associated with one of them, but not with both.
One way to resolve the issue is to add a second collection navigation property:
public class WordEntity
{
public long Id { get; set; }
public string Name { get; set; }
public string Json { get; set; }
public virtual List<WordSinonymEntity> Sinonyms { get; set; }
public virtual List<WordSinonymEntity> SinonymOf { get; set; } // <--
}
and specify the associations via fluent API:
modelBuilder.Entity<WordSinonymEntity>()
.HasOne(pt => pt.Sinonym)
.WithMany(p => p.SinonymOf) // <--
.HasForeignKey(pt => pt.SinonymId)
.OnDelete(DeleteBehavior.Restrict); // see the note at the end
modelBuilder.Entity<WordSinonymEntity>()
.HasOne(pt => pt.Word)
.WithMany(t => t.Sinonyms)
.HasForeignKey(pt => pt.WordId);
Another way is to leave the model as is, but map the WordSinonymEntity.Sinonym to unidirectional association (with refeference navigation property and no corresponding collection navigation property):
modelBuilder.Entity<WordSinonymEntity>()
.HasOne(pt => pt.Sinonym)
.WithMany() // <--
.HasForeignKey(pt => pt.SinonymId)
.OnDelete(DeleteBehavior.Restrict); // see the note at the end
modelBuilder.Entity<WordSinonymEntity>()
.HasOne(pt => pt.Word)
.WithMany(t => t.Sinonyms)
.HasForeignKey(pt => pt.WordId);
Just make sure that WithMany exactly matches the presence/absence of the corresponding navigation property.
Note that in both cases you have to turn the delete cascade off for at least one of the relationships and manually delete the related join entities before deleting the main entity, because self referencing relationships always introduce possible cycles or multiple cascade path issue, preventing the usage of cascade delete.
Doctrine's Many-To-Many Self-Referencing and reciprocity
There are a number of ways to solve this problem, all depending on what the requirements for the "friends" relation are.
Unidirectional
A simple approach would be to use a unidirectional ManyToMany association, and treat it as if it where a bidirectional one (keeping both sides in sync):
/**
* @Entity
*/
class User
{
/**
* @Id
* @Column(type="integer")
*/
private $id;
/**
* @ManyToMany(targetEntity="User")
* @JoinTable(name="friends",
* joinColumns={@JoinColumn(name="user_a_id", referencedColumnName="id")},
* inverseJoinColumns={@JoinColumn(name="user_b_id", referencedColumnName="id")}
* )
* @var \Doctrine\Common\Collections\ArrayCollection
*/
private $friends;
/**
* Constructor.
*/
public function __construct()
{
$this->friends = new \Doctrine\Common\Collections\ArrayCollection();
}
/**
* @return array
*/
public function getFriends()
{
return $this->friends->toArray();
}
/**
* @param User $user
* @return void
*/
public function addFriend(User $user)
{
if (!$this->friends->contains($user)) {
$this->friends->add($user);
$user->addFriend($this);
}
}
/**
* @param User $user
* @return void
*/
public function removeFriend(User $user)
{
if ($this->friends->contains($user)) {
$this->friends->removeElement($user);
$user->removeFriend($this);
}
}
// ...
}
When you call $userA->addFriend($userB), $userB will be added to the friends-collection in $userA, and $userA will be added to the friends-collection in $userB.
It will also result in 2 records added to the "friends" table (1,2 and 2,1). While this can be seen as duplicate data, it will simplify your code a lot. For example when you need to find all friends of $userA, you can simply do:
SELECT u FROM User u JOIN u.friends f WHERE f.id = :userId
No need to check 2 different properties as you would with a bidirectional association.
Bidirectional
When using a bidirectional association the User entity will have 2 properties, $myFriends and $friendsWithMe for example. You can keep them in sync the same way as described above.
The main difference is that on a database level you'll only have one record representing the relationship (either 1,2 or 2,1). This makes "find all friends" queries a bit more complex because you'll have to check both properties.
You could of course still use 2 records in the database by making sure addFriend() will update both $myFriends and $friendsWithMe (and keep the other side in sync). This will add some complexity in your entities, but queries become a little less complex.
OneToMany / ManyToOne
If you need a system where a user can add a friend, but that friend has to confirm that they are indeed friends, you'll need to store that confirmation in the join-table. You then no longer have a ManyToMany association, but something like User <- OneToMany -> Friendship <- ManyToOne -> User.
You can read my blog-posts on this subject:
- Doctrine 2: How to handle join tables with extra columns
- More on one-to-many/many-to-one associations in Doctrine 2
Many to Many Self referencing relationship
You could create a related Many-to-Many table like this:
CREATE TABLE RECOMMENDED_BOOKS (
Book_id1 INT NOT NULL, // FK related to BOOK.Book_id
Book_id2 INT NOT NULL), // FK related to BOOK.Book_id
CONSTRAINT pk_RecommendedBooks PRIMARY KEY (Book_id1, Book_id2)
You could then add as many entries as you want. Say id 1 = "Huckleberry Finn" in the Books table, you could add 1 as the value of Book_id1 and 2 as the value of Book_id2, where book 2 is "Tom Sawyer"; then add "The Travels of Jaimie McPheeters" as another connector, etc.
e.g, if these are in the BOOK table:
Book_id 1, Book_name "The Adventures of Huckleberry Finn"
Book_id 2, Book_name "The Adventures of Tom Sawyer"
Book_id 3, Book_name "The Travels of Jaimie McPheeters"
Book_id 4, Book_name "Westward Ho"
Book_id 5, Book_name "Main Traveled Roads"
...you could add these records:
INSERT INTO RECOMMENDED_BOOKS (Book_id1, Book_id2) VALUES (1, 2)
INSERT INTO RECOMMENDED_BOOKS (Book_id1, Book_id2) VALUES (1, 3)
Whether you also inserted "2,1" and "3,1" records would depend on whether you want to be able to say "people who liked Huck Finn recommend Tom Sawyer" and also say, "people who liked Tom Sawyer recommend Huck Finn" (or whether a one-way recommendation is to be considered two-way without explicitly recording that).
It should be thereafter possible to determine that there is a connection between book2 ("Tom Sawyer") and book3 ("The Travels of Jaimie McPheeters"), even though there is no "2,3" record. It's kind of like the proverbial "Seven Degrees of Kevin Bacon" - all books with a mutual recommendation are themselves mutual recommendations. OR, OTOH, you could just keep your connections restricted to direct ones ("Huck Finn" connects to "Tom Sawyer" but "Tom Sawyer" does not connect to "Jamie McPheeters" unless you insert a 2,3 record.
etc. - you get the point, I'm sure.
Self-referencing many-to-many relationship with an association object in SQLAlchemy
The following approach uses an association object instead of an association table to get a self-referencing many-to-many relationship:
from sqlalchemy import Column, Integer, ForeignKey, create_engine, String
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class EntityAssociation(Base):
__tablename__ = 'entity_association'
entity_parent_id = Column(Integer, ForeignKey('entity.id'), primary_key=True)
entity_child_id = Column(Integer, ForeignKey('entity.id'), primary_key=True)
class Entity(Base):
__tablename__ = 'entity'
id = Column(Integer, primary_key=True)
name = Column(String)
entity_childs = relationship('Entity',
secondary='entity_association',
primaryjoin=id==EntityAssociation.entity_parent_id,
secondaryjoin=id==EntityAssociation.entity_child_id,
backref='childs')
entity_parents = relationship('Entity',
secondary='entity_association',
primaryjoin=id==EntityAssociation.entity_child_id,
secondaryjoin=id==EntityAssociation.entity_parent_id,
backref='parents')
def __repr__(self):
return f'<Entity(name={self.name})>'
if __name__ == '__main__':
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
db = Session()
parent1 = Entity(name='parent1')
parent2 = Entity(name='parent2')
child1 = Entity(name='child1')
child2 = Entity(name='child2')
parent1.entity_childs = [child1, child2]
parent2.entity_childs = [child2]
db.add(parent1)
db.add(parent2)
db.add(child1)
db.add(child2)
db.commit()
entities = db.query(Entity).all()
for entity in entities:
print(entity)
print(' Parent: ', entity.entity_parents)
print(' Childs: ', entity.entity_childs)
print()
This will have the following result:
<Entity(name=parent1)>
Parent: []
Childs: [<Entity(name=child1)>, <Entity(name=child2)>]
<Entity(name=child1)>
Parent: [<Entity(name=parent1)>]
Childs: []
<Entity(name=child2)>
Parent: [<Entity(name=parent1)>, <Entity(name=parent2)>]
Childs: []
<Entity(name=parent2)>
Parent: []
Childs: [<Entity(name=child2)>]
Laravel self relation many to many
As you asked for an example.
This answer is only for giving you an example of a many-to-many relationship with the same table. This is actually known as self-referencing table. So let's do it.
First, we need to create two tables. One is for the artifact names and the other one is an intermediary table which is called the pivot table. Here parent_child table is a pivot table.
Schema::create('artifacts', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});
Schema::create('parent_child', function (Blueprint $table) {
$table->unsignedInteger('parent_id');
$table->foreign('parent_id')
->references('id')
->on('artifacts');
$table->unsignedInteger('child_id')->nullable();
$table->foreign('child_id')
->references('id')
->on('artifacts');
$table->timestamps();
});
Now we need to seed those two tables. For the brevity, I'm going to put them into links. Here are ArtifactSeeder.php and ParentChildSeeder.php
Next up, we need to tell the model to build the many-to-many self-referencing relationship. Here is our model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Artifact extends Model
{
public function children()
{
return $this->belongsToMany(
Artifact::class,
'parent_child',
'parent_id',
'child_id'
);
}
}
Now it's time to play with the data. So let's play with that.
$parent = Artifact::where('name', '=', 'D')->first();
// or
$parent = Artifact::find(2);
foreach ($parent->children as $child) {
echo $child->name . '<br>';
}
I think there is no need to use a many-to-many relationship in your case. You can get your expected data using one-to-many relationship as @ZhengYu's answer. Nevertheless, you can explore whatever you want. Thanks! :)
Self-Referencing ManyToMany Relationship TypeORM
I believe I'm 3 years late, but better late than ever. The most upvoted answer does not answer the question, as it only works for tree-like and hierarchical structures, so if you follow that example, this would happen:
Fred
/ \
Albert Laura
/ \
John Foo
In this example, Foo can't be friends with Fred, because he can only have one parent. Friends is not a tree structure, it's like a net. The answer would be the following:
import { Column, Entity, JoinTable, ManyToMany, PrimaryGeneratedColumn } from 'typeorm';
@Entity(UserModel.MODEL_NAME)
export class UserModel {
static MODEL_NAME = 'users';
@PrimaryGeneratedColumn()
id?: number;
@Column({ type: 'varchar', unique: true, length: 50 })
username: string;
@Column({ type: 'varchar', length: 50, unique: true })
email: string;
@ManyToMany(type => UserModel)
@JoinTable()
friends: UserModel[];
@Column({ type: 'varchar', length: 300 })
password: string;
}
This would create a table where relations between people would be saved. Now for the next important stuff. How do you query this and get a user's friends? It's not as easy as it seems, I've played hours with this and haven't been able to do it with TypeORM methods or even query builder. The answer is: Raw Query. This would return an array with the user's friends:
async findFriends(id: Id): Promise<UserModel[]> {
return await this.userORM.query(
` SELECT *
FROM users U
WHERE U.id <> $1
AND EXISTS(
SELECT 1
FROM users_friends_users F
WHERE (F."usersId_1" = $1 AND F."usersId_2" = U.id )
OR (F."usersId_2" = $1 AND F."usersId_1" = U.id )
); `,
[id],
);
}
(users_friends_users is the autogenerated name that typeORM gives to the table where the relations between users are saved)
Related Topics
Using the Iterator Variable of Foreach Loop in a Lambda Expression - Why Fails
What Is Managed or Unmanaged Code in Programming
What Does Missingmanifestresourceexception Mean and How to Fix It
Showing a Windows Form on a Secondary Monitor
Adding Parameters in SQLite with C#
How to Enable Nuget Package Restore in Visual Studio
Entity Framework Self Referencing Loop Detected
Clipboard.Gettext Returns Null (Empty String)
How to Execute Code That Is in a String
Hyphenated HTML Attributes with ASP.NET MVC
Import and Export Excel - What Is the Best Library
Differencebetween Directory.Enumeratefiles VS Directory.Getfiles
Pass-Through Mouse Events to Parent Control
Returning Only Part of Match from Regular Expression
Visual Studio "Could Not Copy" .... During Build
How to Build a Datatemplate in C# Code
Resharper Wpf Error: "Cannot Resolve Symbol "Myvariable" Due to Unknown Datacontext"