[.Net] Upload - Issue with multiple MJoin Alias on same Upload table

Vincent.P

Hi,

As we want to use the same upload table for 2 different editor fields, we are using an alias on the MJoin (so it appears as 2 different tables).

When uploading a file it works fine without any issue. However, when we want to retrieve the different fields, it doesn't work as expected.

Here is the code to explain a bit more what happens:

            {
                label: "Files NL:",
                name: "uploaded_files_nl[].id",
                type: "uploadMany",
                display: function (file_id, counter) {
                    const uFile = editor.file("uploaded_files", file_id);
                    return `<a href='/Download?filename=${uFile.system_path}&path=articles' target='_blank' download='${uFile.file_name}'>${uFile.file_name}</a>`;
                },
                clearText: "Clear",
            },
            {
                label: "Files FR:",
                name: "uploaded_files_fr[].id",
                type: "uploadMany",
                display: function (file_id, counter) {
                    const uFile = editor.file("uploaded_files", file_id);
                    return `<a href='/Download?filename=${uFile.system_path}&path=articles' target='_blank' download='${uFile.file_name}'>${uFile.file_name}</a>`;
                },
                clearText: "Clear",
            },

                    .MJoin(
                        new MJoin("uploaded_files")
                            .Name("uploaded_files_nl")
                            .Link("scraping_consult.id","assigned_files_articles.article_id")
                            .Link("uploaded_files.id","assigned_files_articles.file_id")
                            .Field(new Field("id")
                                .Upload(
                                    new Upload((file, id) => {
                                          // file upload
                                    })
                                    .Db("uploaded_files", "id", new Dictionary<string, object>
                                    {
                                        {"file_size", Upload.DbType.FileSize},
                                        {"extension", Upload.DbType.Extn},
                                        {"file_name", Upload.DbType.FileName},
                                        {"system_path", string.Empty},
                                        {"web_path", string.Empty},
                                        {"file_lang", "nl"}                                     // this part actually doesn't work, but this is updated in the PostUpload, so it is fine
                                    })
                                )
                               .SetFormatter(Format.NullEmpty())
                            )
                            .Field(new Field("file_name"))
                            .Field(new Field("web_path"))
                            .Field(new Field("system_path"))
                            .Field(new Field("extension"))
                            .Field(new Field("file_lang"))
                            .Where("file_lang", "nl")
                    )
                    .MJoin(
                        new MJoin("uploaded_files")
                            .Name("uploaded_files_fr")
                            .Link("scraping_consult.id","assigned_files_articles.article_id")
                            .Link("uploaded_files.id","assigned_files_articles.file_id")
                            .Field(new Field("id")
                                .Upload(
                                    new Upload((file, id) => {
                                          // file upload
                                    })
                                    .Db("uploaded_files", "id", new Dictionary<string, object>
                                    {
                                        {"file_size", Upload.DbType.FileSize},
                                        {"extension", Upload.DbType.Extn},
                                        {"file_name", Upload.DbType.FileName},
                                        {"system_path", string.Empty},
                                        {"web_path", string.Empty},
                                        {"file_lang", "fr"}                                     // this part actually doesn't work, but this is updated in the PostUpload, so it is fine
                                    })
                                )
                               .SetFormatter(Format.NullEmpty())
                            )
                            .Field(new Field("file_name"))
                            .Field(new Field("web_path"))
                            .Field(new Field("system_path"))
                            .Field(new Field("extension"))
                            .Field(new Field("file_lang"))
                            .Where("file_lang", "fr")
                    )

If we remove the 2 .Where("file_lang", …), then both MJoin will return the value from the FR part (same values, but won't show the ones for NL)

If we put both .Where("file_lang", …), then uploaded_files_nl is empty while uploaded_files_fr contains the FR files.

As for the SQL queries, they seem to be properly generated and executed individually, but only one of them(?) gives the expected results.

Any clue what could be the cause?

Best regards,
Vincent.

allan

I'll almost 100% certain that is is caused by this which I don't believe is required. I'll confess I'm just about to dash out of the office, so I haven't tried the fix yet, but I've built a DLL without that condition which you can download here. If you give it a try, let me know how it goes.

Thanks,
Allan

Vincent.P

Hi Allan,

I think the condition is needed as it returns this error message:

An item with the same key has already been added. Key: uploaded_files

Best regards,
Vincent.

allan

Hi Vincent,

That's what I get for rushing it .

Try this one - I'm got it doing a proper merge now.

Allan

Vincent.P

Hi Allan,

Ahah yes, we all rush things from time to time

The issue is different now, when uploading a file it returns the following message:
An item with the same key has already been added. Key: 72.

My guess would be that it tries to upload the file twice.

Best regards,
Vincent

allan

Third time is the charm.

The problem there was when the same file was used by two different DataTables.

Allan

Vincent.P

Hi Allan,

So, I just had a look in the database and it seems that when I click on Update after a file upload (NL and/or FR), only the FR has the linked table updated. The uploaded file is not linked to the other table.

It only gets linked for the FR one and never for the NL one.

Best regards,
Vincent.

Vincent.P

Hi @allan ,

Any update on the issue?

It is almost working, just that it doesn't update the link table properly

Best regards,
Vincent

allan

Hi Vincent,

Sorry for the delay in getting back to you. I've set up a full example to replicate what you have now and I see the issue - it is because of how the Mjoin is handled by our libraries. They assume that the link table (assigned_files_articles in this case) is unique per Mjoin.

What happens is that when you do an update, it will actually just delete all records for a parent row and then insert a new link. That delete is what is causing us problem!

What is happening is:

1. Delete on assigned_files_articles for uploaded_files_nl
2. Insert the new links for uploaded_files_nl
3. Delete on assigned_files_articles for uploaded_files_fr
4. Insert the new links for uploaded_files_fr

Step 3 is what is killing it!

I've tried a quick experiment by swapping the order of the actions so all deletes happen first, but it isn't quite as simple as that. There needs to be a way to tell between which files are owned by the nl field and which by the fr one. That would need an extra column in the link table, which is a deeper change.

To get it working just now, can you use two different link tables rather than assigned_files_articles - say assigned_files_articles_nl and assigned_files_articles_fr? That would allow this to work.

Allan

Vincent.P

Hi Allan,

I understand that this change would impact a lot of things and is probably not easy to implement without breaking everything

For now, we will use the workaround and use 2 different link tables (even if I really would like not to )

I hope that in the near future we won't have to use that workaround

Again, thanks a lot for your help.

Best regards,
Vincent

allan

Hi Vincent,

I've marked it down for our Editor 2.1 release cycle .

Regards,
Allan

Vincent.P

Hi @allan ,

Are there any updates on this part? How could we provide our help otherwise? Any guidelines to follow?

Best regards,
Vincent.

allan

Hi Vincent,

Unfortunately we haven't made any progress with this yet as Editor 2.1 development hasn't really started (just minor things so far). You would need to continue using the workaround for now.

Allan

Vincent.P

Hi Allan,

Alright thanks for the update. Let me know if I can do anything to help on that development.

Best regards,
Vincent.