Hi there, I am trying to create a database query by using 'LIKE' on an integer. But that does not work, the LIKE disappears as soon as I choose an integer field. So I gues thats not possible?
Now I switch to the custom query builder and that solved the use of LIKE but my next problem is that I want to use a wild card '%' in the parameter.
This is what I would like to create:
select tasks.task_id, tasks.task from tasks inner join categories on categories.category_id = tasks.category_id where categories.path LIKE '1/%'
The where could also be LIKE '1%' etc...
Can anyone help me get the parameter use this % wildcard?
First off, welcome to Wappler!
In MySQL, 'LIKE' is typically used for string comparisons rather than integer fields. When you use 'LIKE', MySQL expects to match patterns in strings, not exact values in integers. If you need to compare integers, you should use operators like '=', '>', '<', etc., depending on your comparison needs. Could you specify what you're trying to achieve with 'LIKE' on an integer field?
3 Likes
select `tasks`.`task_id`, `tasks`.`task` from `tasks` inner join `categories` on `categories`.`category_id` = `tasks`.`category_id` where `categories`.`path` LIKE '1/%'
This query seems to be applying LIKE operator on a string field called path.
Adding to what Brad has explained about operators, in Wappler's query builder, when you select path as the column, you should get the CONTAINS operator as an option.
If not, please report.
This CONTAINS is the LIKE operator, which converts to LIKE '%operand%'.
Additionally, you have BEGINS WITH operator, which converts to LIKE 'operand%'.
Lastly, if you wish to use % in the custom query with param, you can try something like this:
LIKE concat(:P1 ,'%')
P.S. Welcome to Wappler.
1 Like
Hi Brad, thanks for chiming in!
I have a category table in which I created a tree-like structure as a Materialized path. This field is a string.
But thanks for asking because now I realize that I shouldn't compare the path field to the task_id, (I stored the path as ID's like this: "1/3/5" ) but to the path field. So thats solved as wel.
Thanks Sid!
I changed the query, as I explained to Brad above, to compare it to the path field instead of the id. Now I can use the begins with and that works great.
Still like to understand how you construct the % in a parameter in the querybuilder. Or is that only possible with the custom query?
In query builder, you don't have to do it. Wappler does it for you.
For custom query, you have to manually concatenate the % as required.