Self-referencing MJoins

guidols

Hello,

I have the following data structure: it's a self-referencing many-to-many table:

Samples <--> SampleProductionCells <--> Samples

On the server side, I have the following code:

var editor = new Editor(db, "Samples", "Samples.Id")
    .Model<SampleEditorDto>("Samples")
    // ...

editor = editor
    // ...

    .MJoin(new MJoin("Samples")
        .Name("ProductionCells")
        .Link("Samples.Id", "SampleProductionCells.SampleId")
        .Link("Samples.Id", "SampleProductionCells.ProductionCellId")
        .Model<EditorNameDto>()
        .Field(new Field("Id")
            .Options("Samples", "Id", "Name")
        )
    );

And on the client side:

{
    data: "ProductionCells",
    editField: "ProductionCells[].Id",
    render: function(data, type, row) {
        return data;
    }
}

But I get the following error:

DataTables warning: table id=editorTableSamples – Object reference not set to an instance of an object.

How can I correctly define the self-referencing join?

Thanks!

allan

At the moment I think you would need to use a VIEW I'm afraid. There is a hack available in the PHP libraries, but it is just that, a hack and I've not ported it to the .NET libs (assuming that is .NET and not Typescript - which in fairness, also doesn't have the hack).

Sorry I don't have an easier answer. A VIEW on Samples that just does a SELECT on that table should allow it to work though.

Allan

guidols

Thanks Allan!

Just to clarify: if I create a view on Samples (let's say SampleView) and use that in the .MJoin() to read the self-referencing data, how would I handle updates?

My concern is that with a view I won’t be able to update the many-to-many relationship directly. Is there a way to:

• Keep the current structure (Samples <--> SampleProductionCells <--> Samples)
• Use a view (e.g., SampleView) to read the related Samples
• Still save the N-N relation in SampleProductionCells?

Or would I need to create custom logic to support updates?

Thanks again!

rf1234

In your case it could be something like this. Only the link table is being updated in this case. SampleView could be as simple as "SELECT * FROM Samples".

Mjoin::inst( 'SampleView' )
    ->link( 'Samples.Id', 'SampleProductionCells.SampleId' )
    ->link( 'SampleView.id', 'SampleProductionCells.ProductionCellId' )

allan

Yup, the key is that with an Mjoin you aren't updating the final table (SampleView in this case), rather you are modifying the connections in the junction table (SampleProductionCells).

Allan

guidols

At database level (MS SQL Server) I can't link a table to a View, so this is not feasible right?

Mjoin::inst( 'SampleView' )
    ->link( 'Samples.Id', 'SampleProductionCells.SampleId' )
    ->link( 'SampleView.id', 'SampleProductionCells.ProductionCellId' )

Can you please clarify how can I correctly setup the editor?

This doesn't seems right:

.MJoin(new MJoin("SamplesView")
.Name("ProductionCells")
.Link("Samples.Id", "SampleProductionCells.SampleId")
.Link("Samples.Id", "SampleProductionCells.ProductionCellId")
.Model<EditorNameDto>()
.Field(new Field("Id")
.Options("Samples", "Id", "Name")
)
);

rf1234

Why can you not set up a view? And why can't you "link a table to a view"? What does "at database level" mean?

I can reassure you: You can define a view and you can do anything with it that you like except update the view. However, you can even update a table using a join to a not-updatable view.

https://hasura.io/learn/database/microsoft-sql-server/views/

https://www.w3schools.com/sql/sql_view.asp