custom SQL query convert to editor datatables

yskapell

hello all,

Can you help me to convert this query
"insert into test2
select valu1, value2, value3 value4 --Here we have to add the actual values from the csv file
where not exists (select 1 from test2 where ideogram=value1 and pinyin=value2 and type=value3 and meaning=value4);"

To fit properly the datatables editor?

rf1234

Editor is a CRUD application. So you would need to define the Editor instance in a way that all of the four cases can be handled. You can't use NOT EXISTS with Editor but you could use a LEFT JOIN and check the LEFT JOINed columns for being NULL in the WHERE clause which means they don't exist.

yskapell

The table of select and insert is the same. so how left join will work?

rf1234

What you seem to be trying to achieve is to avoid a duplicate record insertion. I frankly wouldn't do it that way.

You could use a very simple Editor instance that doesn't even require a WHERE clause and check for duplicate records in a validator (check for server side validators in the docs please) or you don't use a validator and just let the database return a duplicate key error and handle that client side by displaying the right error message to the user like in my code below.

Your Editor instance would be even simpler than in this example:
https://editor.datatables.net/examples/simple/simple.html

This is what you could do if you want to handle the duplicate key error client side:

editor
    .on( 'postSubmit', function ( e, json, data, action ) {
        if (json.error) {
            if ( json.error.indexOf('1062 Duplicate entry') >= 0 ) {
                json.error = "Sorry, this record is a duplicate and will not be inserted again";
            }
        }        
    });

allan

We are currently discussing this issue by e-mail as well as in this thread and a few others. There is a client-side solution to this that we just need to get working in the application in question.

Allan

rf1234

Hmmm - now you lost me, Allan. What issue do you mean? Dupkey? Or the INSERT statement above? What client side solution are you referring to?

yskapell

Yes the issue solved on client side, but I need to know my option on how can I solve it on database side.

Thank you both for your help.

allan

@rf1234 Apologies - this question was also submitted to me via e-mail and there was an on going discussion there.

We had a client-side solution to the issue that had an error that needed to be worked out. @yskapell's post here was looking for an alternative since the client-side solution wasn't working. But we've got that resolved now.

This is the client-side solution for imported and checking for duplicates. It does need to be tweaked for each install though, which was the issue here.

Allan

rf1234

Thanks Allan!

Yes the issue solved on client side, but I need to know my option on how can I solve it on database side.

And that is where my suggestion comes in: I have the database produce the duplicate key SQL error "1062 Duplicate entry". All I do is catch this error client side to produce a better error message to the user. Otherwise the user'd be confronted with the rather technical SQL error message.

Of course: This will only work if you got your database indexes right. But that goes without saying I guess.

I think you should also be able to use this logic when importing a file. In that case you could even ignore the error when a duplicate occurs and continue with the next record. Just make sure you don't show the error message to the user and the import process continues.