Welcome Guest, Not a member yet? Register   Sign In
CodeIgniter 4.0 Query Builder and CAST support
#4

(This post was last modified: 03-15-2020, 03:24 PM by atsanna.)

I think I have solved my problem.

When I do a general search with DataTables, I can have a table that contains fields other than text.
In this case, using the like() function of the Query generator creates an error in the database which obviously does not allow you to search for text in a numeric field.
The only way is to specify in the query that the numeric field should be treated as text and this is done using the CAST function.


Code:
WHERE "field" LIKE '% foo%'
should become
Code:
WHERE CAST(field as TEXT) LIKE '% foo%'


With the changes I made, the like (), orLike (), etc. functions of the query generator continue to work in the same way:

Code:
$ this->like(fied, value)


If you need to cast, the function should be called like this:
Code:
$ this->like(fied, value, 'both', null, false, "cast")

where instead of "cast" the desired type of destination is set, for example:
INTEGER, DATE, DOUBLE, DOUBLE PRECISION, BOOLEAN, TEXT, etc...

In my case, before calling the like function, I check if the field is a text:
If it's text, I don't need CAST and I'll call the function like this:

Code:
$ this->like(fied, value)


If instead it is a field other than text, I will call the function in this other way:

Code:
$ this->like(fied, value, 'both', null, false, "TEXT")


I would like to do a PR, but I am self-taught and I don't know how.

Since I like to share my findings, I attach the files in case they are useful for someone.

Attached Files
.zip   framework.zip (Size: 19.15 KB / Downloads: 4)
Codeigniter 4 - Docker Image [github] [docker hub]
Reply


Messages In This Thread
RE: CodeIgniter 4.0 Query Builder and CAST support - by atsanna - 03-15-2020, 07:08 AM



Theme © iAndrew 2016 - Forum software by © MyBB