Good day. The idea of this is that the user can add parameters to the SQL editor from two inputs, one the parameter and the other the value:
and if you type in the SQL editor you automatically add inputs which are the parameters and the value.
From the SQL editor to the inputs it works fine because I pass a regular expression to it. The problem is from the inputs to the editor, I think it's more of a logic problem, but I haven't found a solution yet. The case is that when adding a parameter from the input, it adds more, although always before adding the clean but it seems that it does not affect.
This is the code:
import React, { useEffect, useState} from 'react';
import SQLContainerInput from '../Components/SQLContainerInput';
........
function arrayParamsExec(stringSql) {
const paramsQueryText = [...stringSql.matchAll(/{{(\w+)}}/ig)];
const newArray = paramsQueryText.map(item => item[1]);
return newArray;
}
const initalStateCurrentChartInfo = {
SQLQuery: '',
dataType: 'TABLE',
columns: [],
};
const CustomSQLEditor = ({
fromQuery, // del Redux
}) = {
const [currentChartInfo, setCurrentChartInfo] = useState(
initalStateCurrentChartInfo,
);
const [params, setParams] = useState([]);
const [textSql, setTextSql] = useState('');
useEffect(() => {
....
let sqlDefaultString = '';
sqlDefaultString = fromQuery.internal_name
? `SELECT * FROM \`${fromQuery.internal_name}__${fromQuery.items[0]}\` LIMIT 20`
: '';
setCurrentChartInfo({
...currentChartInfo,
SQLQuery: `${sqlQuery}`,
});
},[fromQuery]);
// ------------------params---------------------
const addProperty = () => {
setParams([
...params,
{ name: '', value: '' },
]);
};
const updateProperty = (event, index, key) => {
const newProperties = [...params];
newProperties[index][key] = event?.target?.value;
// agregar parámetros al editor SQL
let sqlParams = textSql;
if (key === 'name') {
params.forEach(p => {
if (p.name && /^\w+$/i.test(p.name)) {
sqlParams += `{{${p.name}}}`;
}
});
setTextSql('');
setTextSql(`${sqlParams}`);
}
setParams(newProperties);
};
const deleteProperty = index => {
const newProperties = [...params];
newProperties.splice(index, 1);
const newTextSQL = replaceAll(textSql, `{{${params[index]?.name}}}`, '');
setTextSql(newTextSQL);
setParams(newProperties);
};
// ------------------end params---------------------
const changeTextEditor = (valueEditor) => {
const namesParams = arrayParamsExec(valueEditor);
const newProperties = namesParams.map((pName) => {
const valueNew = params.find(p => p.name === pName);
return {name: pName, value: valueNew?.value || ''};
});
setParams(newProperties);
setTextSql(valueEditor);
}
return (
<>
<SQLContainerInput
button={{
onClick: handleSubmit,
}}
input={{
value: `${textSql}\n`,
onChange: changeTextEditor,
}}
/>
<DymanicKeyValueInputInput
properties={params}
updateProperty={updateProperty}
deleteProperty={deleteProperty}
addProperty={addProperty}
/>
</>
);
}
So a solution I put another value that is textSql
in charge of placing the already concatenated string, and the string that comes from the redux that is this value fromQuery
. The redux string is put in a variable sqlParams
, when adding it concatenates with the parameters and from there if I clean the textSql
.
......
const updateProperty = (event, index, key) => {
const newProperties = [...params];
newProperties[index][key] = event?.target?.value;
// agregar parámetros al editor SQL
let sqlParams = currentChartInfo.SQLQuery;
if (key === 'name') {
params.forEach(p => {
if (p.name && /^\w+$/i.test(p.name)) {
sqlParams += `{{${p.name}}}`;
}
});
setTextSql('');
setTextSql(`${sqlParams}`);
}
setParams(newProperties);
};
......
The problem there is that if I write directly from the SQL editor, the entire string is reset, that is,
everything that has been written and of course there, if it works, placing the parameters is not repeated. But I can't find a way to do that there. Forgive my ignorance if I'm doing something wrong.
This is an example when one writes a long sql:
Adding a parameter from input resets:
A video showing the error: https://www.youtube.com/watch?v=rQBPOPyeXlI
repository link: https://gitlab.com/albert925/parametrosui-a-editor-sql
Potential Solution Update
thank you very much @g.4, yes that part works but when I add a parameter from the SQL editor and modify the inputs or add, it breaks what I had before. https://youtu.be/-fqRiAwa1Hs . I already updated the changes in the repository
Possible solution
Because of the modification you implemented:
The owner of the state, when doing a
keyStroke
in the text editor is the variableendSql
:So, for changes to be persisted in the text editor, for each event
updateProperty
, you must operate onendSql
:Controlling data using state in ReactJS
Lastly, I suggest handling the variable
endSql
with a state:Then instead of assigning data to the variable
endSql
directly in the component scopeCustomSQLEditor
:The state of the variable is controlled
endSql
withuseEffect
:With this minimal modification to the solution you implemented previously, the user's modifications to
Query SQL
the text editor should already be maintained/persisted, when the user adds more parameters using theinputs
.Update: Avoid duplicate parameters
When
n
parameters are added to the SQL query, by means ofinputs
these they are concatenated in the chainendSql
.Now, each time is called
updateProperty
, the SQL query currently in the text editor is passed, with the state variableendSql
.Assuming that the user added a single parameter, first assignment
endSql
:Assuming that the user wants to change the name of the parameter, it is called again
updateProperty
, but the stringendSql
has already been modified during the first assignment, so its value is:The user changes the name of the variable
a
tob
, but at no time has the methodupdateProperty
removed ita
from the chainendSql
, so its value is:And what was expected was:
Therefore, if more parameters are assigned, the chain
endSql
with previous assignments will always be seen.Example, assuming the creation of two parameters:
It is observed that it
a
will be repeated inendSql
.Possible solution
In the method comments
updateProperty
there is the following hint :A new method is created
cleanSQLString
to clean the stringendSQL
each time the methodupdateProperty
is called; This method separates the structure of the SQL query and its parameters in aarray
:Where the array contains:
Next, the method
cleanSQLString
insideupdateProperty
is called and asks:If the array that represents the SQL query
endSQL
had already been assigned parameters, then the structure of the query without parameters is assignedposicion[0]
to the variablesqlParams
Otherwise it is the first assignment, it is assigned
endSql
to the variablesqlParams
:To do the tests, you have to add the following code in the component
CustomSQLEditor
, first the methodcleanSQLString
:Then, at the beginning of the method
updateProperty
:field notes
The method
updateProperty
knows whichinput
one is being manipulated, since it receives itindex
as an argument.In addition, there is a state
params
that is responsible for accumulating the parameters that the user adds through theinput
.The rest of the operations in
updateProperty
use these two variablesindex
andparams
for eachinput
.Hope this answer is helpful.