• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CodeIgniter 4.0 Query Builder and CAST support

#1
The Codeigniter 4 query generator allows you to use CAST to convert a value of one type to another within functions like(), where(), etc. ?

For example, now the like() function return a query like this:
Code:
SELECT *
FROM "table"
WHERE "field" LIKE '%foo%' ESCAPE '!'
ORDER BY "field" ASC
LIMIT 10

I would like to achieve this
Code:
SELECT *
FROM "table"
WHERE "field" LIKE CAST('%foo%' AS TEXT) ESCAPE '!'
ORDER BY "field" ASC
LIMIT 10

I know that alternatively I can write the query, but I would like to know if it is possible to use the query generator.

Thank you for your answer.
Codeigniter 4 - Docker Image [github] [docker hub]
Reply

#2
Could you please elaborate on use-case where you need CAST function in mySQL?
Also would be interesting why not to case datatypes in PHP or in your model before feeding them into mySQL?

Overall, I do not think Query Builder should support all possible functions of all DB engines around.
For custom queries you always have free-form query.
Reply

#3
(03-14-2020, 03:25 AM)zahhar Wrote: Could you please elaborate on use-case where you need CAST function in mySQL?
Also would be interesting why not to case datatypes in PHP or in your model before feeding them into mySQL?

Overall, I do not think Query Builder should support all possible functions of all DB engines around.
For custom queries you always have free-form query.

Thank you very much for your reply.

I am using datatables.

I am implementing database search and some tables not only contain text fields, but also integers, date and time and more.

If I use the cast function it becomes much simpler, otherwise I think I have to insert an if for each type of field...

I'm not very expert on the subject: if you have any suggestions I would be grateful.

I wouldn't want to write a search-specific query generator when CodeIgniter already has one
Codeigniter 4 - Docker Image [github] [docker hub]
Reply

#4
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: 3)
Codeigniter 4 - Docker Image [github] [docker hub]
Reply

#5
I learned a new thing: I managed to make a Pull Request Big Grin

https://github.com/codeigniter4/framework/pull/7
Codeigniter 4 - Docker Image [github] [docker hub]
Reply

#6
Hey man, nice try and thank you for taking care!

I do not want to be a party-braker here, but:
1) you did PR towards wrong repo; right one is https://github.com/codeigniter4/CodeIgniter4
2) you did not cover your change with Unit Tests, so your PR will not be accepted until you extend existing tests for like() method with additional test-cases that cover your changes
3) you have to take care about documentation as well
4) your code smells - but you will know it from maintainers later; particularly - you allow any string as $cast attribute, however, mySQL supports only handful of keywords in their CAST() function - and you mentioned them (INTEGER, DATE, DOUBLE, DOUBLE PRECISION, BOOLEAN, TEXT), but did not add checking and error reporting
5) you should validate your Github account and sign your commit. Read more how to do it here: https://help.github.com/en/github/authen...ng-commits

Good luck with 2nd attempt Smile
Reply

#7
(03-16-2020, 11:48 AM)zahhar Wrote: Hey man, nice try and thank you for taking care!

I do not want to be a party-braker here, but:
1) you did PR towards wrong repo; right one is https://github.com/codeigniter4/CodeIgniter4
2) you did not cover your change with Unit Tests, so your PR will not be accepted until you extend existing tests for like() method with additional test-cases that cover your changes
3) you have to take care about documentation as well
4) your code smells - but you will know it from maintainers later; particularly - you allow any string as $cast attribute, however, mySQL supports only handful of keywords in their CAST() function - and you mentioned them (INTEGER, DATE, DOUBLE, DOUBLE PRECISION, BOOLEAN, TEXT), but did not add checking and error reporting
5) you should validate your Github account and sign your commit. Read more how to do it here: https://help.github.com/en/github/authen...ng-commits

Good luck with 2nd attempt Smile

Hey buddy, I had no doubts that I was going to miss something.  Big Grin Big Grin

Thanks for all the information: let's see if I can perfect my attempt.
Codeigniter 4 - Docker Image [github] [docker hub]
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.