I want the Main SQL to use WHERE close when applicable
SELECT *
FROM note
WHERE Note LIKE %{{textInput}}%
AND ( {{SelectCompany.selectedLabel.length > 0 }}
AND NoteCompanyID = {{SelectCompany.selectedItem.Cid}}
)
AND ( {{SelectUser.selectedLabel.length > 0 }}
AND NoteUserID = {{SelectUser.selectedItem.Uid}}
)
AND ( {{SelectType.selectedLabel.length > 0 }}
AND NoteTypeID = {{SelectUser.selectedItem.Tid}}
)
GROUP BY NoteID
List of users in my DropDown User list needs to be limited based on Selected Company.
My sql query to get users:
SELECT UserID, UserFullName
FROM users
WHERE UserID > 0
AND
{{SelectCompany.selectedLabel.length > 0 ?
UserCompanyID = SelectCompany.selectedItem.UserCompanyID : '1=1'}}
GROUP BY UserID
Non of the querys above is working. I do not know how to do this and was hoping that somebody in the community can help me?
I do not have much knowledge about IF in MySQL or JavaScrip and need help. with ssolvig this?
A couple of things that I think can help get you up and running with this query:
In your first query, the WHERE Note LIKE %{{textInput}}% is a little off -- you'll need the value from the textInput, not just the component and you are going to want to include the wildcard % as part of the {{ }} like this:
WHERE Note LIKE {{'%' + textInput.value +'%'}}
I recommend testing this query without the addtional AND conditions to make sure the results come back when there is data in the textInput component
Once the WHERE clause is working with just this one condition I would then start adding back the rest and testing them in stages. I believe the way you are returning the truthiness of the component values will work, but using the Label length might not be the most logical choice -- just a thought, not an issue.
Once the entire query is returning values for each of your components, address the next query:
Here it looks like you are trying to return a value conditionally. For SQL, you would want to use the IF/ELSE syntax:
...
AND
IF {{SelectCompany.selectedLabel.length > 0}}
UserCompanyId = {{SelectCompany.selectedItem.UserCompanyID}};
ELSE
1=1;
Keep in mind that you can also use logic a little more cleanly in a JavaScript transformer which creates a fully formed statement for you to use in the query you are making. This can help if you are more comfortable outside of SQL but know how to piece together the statements you need.
Let us know if the query suggestions above don't work out or you get stuck with a new issue if they do start to work
On the first query I have multiple filters for user to select from and limit the search.
SQL shall return all records if user only press the Search button. Else the result shall be limited based on selection or text inputed.
`SELECT *
FROM note
WHERE NoteText LIKE {{'%' + SearchNote.value + '%'}}
The above SQL return all if SearchNote.value is empty or 644 rows with SearchNote = test.
SELECT *
FROM note
WHERE ( {{SearchNote.value.length>0}}
AND NoteText LIKE {{'%' + SearchNote.value + '%'}} )
AND ( {{SelectCompany.selectedLabel.length > 0 }}
AND NoteCompanyID = {{SelectCompany.selectedItem.Cid}}
)
AND ( {{SelectUser.selectedLabel.length > 0 }}
AND NoteUserID = {{SelectUser.selectedItem.Uid}}
)
AND ( {{SelectType.selectedLabel.length > 0 }}
AND NoteTypeID = {{SelectUser.selectedItem.Tid}}
)
GROUP BY NoteID
The SQL above returns 0 rows if all is empty or if SearchNote = test.
I hope you can see any errors or recommend a way to sovle the issue to have 4 different input fields.
In this group of conditions, it seems like you check a component for a Label with a non-zero length and matching property. In the first two conditionals, the check for length is done on 3 different components, but in the last one the property check is done against SelectUser.selectedItem.Tid seemingly not from the SelectType component -- should this be SelectType.selectedItem.Tid?
Possibly! You may need to actually check the label for a condition (like != '' || != null || != undefined) since it is technically always present on the component and will always go down the truthy path.
