Search query get value error

hello
I left a question because it didn't work well while implementing the multi-search function.

As shown in the picture, I decided what to search for in the radio button, and then tried to run the query with the value, but it doesn't work.

I've tried it this way.
//
SELECT A.MemberID, A.UserID, B.Nickname, B.AccountLevel, A.LastLoginDate,
FROM {{formatDataAsArray(tUser.data)}} AS A
LEFT JOIN {{formatDataAsArray(tAccount.data)}} AS B
ON A.UserID = B.UserID
WHERE
AND ({{!keywordInput.value}} OR {{keywordOption.value}} = {{keywordInput.value}})

I tried to set {{keywordOption.value}} to 'A.'+{{keywordOption.value}} or {{'A.'+keywordOption.value}} keywordoption component's value to A.UserID, but it didn't work. .
can i get some help?

Hi @jungsu :slight_smile:

Looking at your last line:


AND ({{!keywordInput.value}} OR {{keywordOption.value}} = {{keywordInput.value}})

It looks like you're using a dynamic column in your Query JSON with SQL query and I don't think this is currently supported.

However, you can write a JS query to do something similar!

1. In your Query JSON with SQL query, you can keep the JOIN statement. We could write this in JS as well, but it's probably simpler in your current query:

SELECT A.MemberID, A.UserID, B.Nickname, B.AccountLevel, A.LastLoginDate, FROM {{formatDataAsArray(tUser.data)}} AS A LEFT JOIN {{formatDataAsArray(tAccount.data)}} AS B ON A.UserID = B.UserID WHERE AND ({{!keywordInput.value}} OR {{keywordOption.value}} = {{keywordInput.value}})

2. Write a JS transformer with code like this:

const data = {{queryJSONwithSQL.data}};

if ({{keywordInput.value}}) {

return data.filter(obj => obj[{{keywordOption.value}}].includes({{keywordInput.value}}));

} else {

return data;

};

3. In your table, use {{transformer1.value}} in your table data.

See attached app JSON if you want to poke around with my setup! It won't have data since we don't have the same database connected, but it still has the queries written. You can import this JSON file by clicking Create new (home page) > From JSON. Quick docs on exporting/importing apps here.


Query-20JSON-20with-20SQL.json

hello..
I understood the answer and it has been solved to some extent.

The remaining problem is that the value of textinput is applied to the first value of radioGroup, but from the second and third it is not applied.

I don't think the suspected part is because it's a difference between letters and numbers, or because it's a different table... I might be misunderstanding.

If you know a solution, can you teach me?



//use query//
SELECT A.MemberID, A.UserID, B.Nickname, B.AccountLevel, A.LastLoginDate, C.CircleID, D.Name
FROM {{formatDataAsArray(t_user.data)}} AS A
LEFT JOIN {{formatDataAsArray(t_account.data)}} AS B
ON A.UserID = B.UserID
LEFT JOIN {{formatDataAsArray(t_circle_member.data)}} AS C
ON A.UserID = C.UserID
LEFT JOIN {{formatDataAsArray(t_circle.data)}} AS D
ON C.CircleID = D.circleID

//transformer//
const data = {{selectUserList.data}};
if ({{keywordInput.value}}) {
return data.filter(obj => obj[{{keywordOption.value}}].includes({{keywordInput.value}}));
} else {
return data;
};

hello?

I have additional questions.

Can you give me advice on how to search additional date ranges with the above conditions satisfied?
Can I search by date even though I don't have time?

Hi again @jungsu!

For your first question (about the radio buttons), it should work with both text and numbers (I made sure to test that with my app using email strings and cost numbers).

Does your data have Nickname and UID columns to search through?

For your second question, you'd like to add another filter with dates? Would something like this be helpful?

http://community.retool.com/t/how-to-write-a-query-that-filters-data-using-a-date-range-component/13361