Querying a many to many relation with out a junction table

Question

I have three entities in my application as below:

class Machine{
    id
    site_id 
    contract_id
    client_id
}

class Site{
    id 
}

class Contract{
    id
}

There is an entity named Machine and it has a one_to_many relationship with Contract and another one_to_many relation with another entity which is called Site. There is no direct relation between Site and Contracts and no junction table exists between them but Machine relates to both Site and Contract and also client_id is common between these entities and it works fine in 99% of times.

Now the business is asking for sth new, it needs the list of contracts of users, every contract should have the list of sites and site have a list of machines. it's would be great to have a raw SQL query or a solution that could be applied on TypeOrm query builder. I should mention that having a many_to_many relation between Site and Contract is not what I'm seeking due to some complexities it brings in my case.

Answer 1

the machine could be used as a junction table in between so in code we might define a relation like this one below

class site{
id
@ManyToMany(type=>Contract,contract=>contract.sites)
contracts:Contract[]
}

class Contract{
id
@ManyToMany(type=>Site,site=>site.contracts)
@JoinTable({name:"machine"})
sites:Site[] 
}