PHP Version 8.3 CodeIgniter4 Version <p di

Thank you for the feedback. There is no sample code with <code class

It seems join() does not support <code class="notrans

There is one test case. <div class="Bo

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

Bug: [QueryBuilder] join() with BETWEEN causes TypeError preg_quote(): Argument #1 ($str) must be of type string, false given about codeigniter4 HOT 7 CLOSED

oakhurstmgmt commented on June 12, 2024

Bug: [QueryBuilder] join() with BETWEEN causes TypeError preg_quote(): Argument #1 ($str) must be of type string, false given

from codeigniter4.

Comments (7)

oakhurstmgmt commented on June 12, 2024 1

I probably did not phrase the bug report correctly. I have two SQL queries that have worked perfectly from CI version 4.0.x all the way through 4.4.8. Both of these queries are constructed using the Query Builder Class. Here are the lines from each Query Builder that appear to be throwing the type error:

->join('leases', 'units.unit_id = leases.unit_id AND CURDATE() BETWEEN lease_start_date AND lease_exp_date', 'left')

->join('leases', 'units.unit_id = leases.unit_id AND workorder_start BETWEEN lease_start_date AND lease_exp_date', 'left')

The only common thread seems to be the use of the BETWEEN SQL operator. I'm not sure that has anything to do with it.

It does seem to be an issue in Query Builder because it no longer puts together the query without throwing an error.

It could simply be a strict typing issue like some of the other 4.5.0 bugs, but I will do some further troubleshooting.

from codeigniter4.

kenjis commented on June 12, 2024

How do we reproduce?
It seems Query Builder does not support BETWEEN.
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html

from codeigniter4.

kenjis commented on June 12, 2024

Thank you for the feedback.

There is no sample code with BETWEEN in join().
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#join
and there is no test case:
https://github.com/codeigniter4/CodeIgniter4/blob/develop/tests/system/Database/Builder/JoinTest.php

We need further investigation.

from codeigniter4.

kenjis commented on June 12, 2024

It seems join() does not support BETWEEN, but it worked by accident in previous versions.

When there is a conditon 'units.unit_id = leases.unit_id AND CURDATE() BETWEEN lease_start_date AND lease_exp_date', it is parsed in $conditions as

array (
  0 => 'units.unit_id = leases.unit_id',
  1 => 'CURDATE() BETWEEN lease_start_date',
  2 => 'lease_exp_date',
)

but it is of course wrong.

Fixing this behavior is difficult, so I sent ad hoc fix PR #8792

from codeigniter4.

oakhurstmgmt commented on June 12, 2024

Ahh, I understand now.

I replaced the BETWEEN operators in my JOIN clauses with a combination of >= and <= operators to workaround this limitation with Query Builder.

This project also has many WHERE clauses constructed by Query Builder utilizing the BETWEEN operator that are still working as expected in 4.5.1. Does where() officially support BETWEEN or is is this also working by accident?

from codeigniter4.

kenjis commented on June 12, 2024

There is one test case.

CodeIgniter4/tests/system/Database/Builder/WhereTest.php

Lines 198 to 210 in d425bdf

 public function testWhereCustomStringWithBetweenEscapeFalse(): void 

 { 

 $builder = $this->db->table('jobs'); 

 $where = "created_on BETWEEN '2022-07-01 00:00:00' AND '2022-12-31 23:59:59'"; 

 $builder->where($where, null, false); 

 $expectedSQL = "SELECT * FROM \"jobs\" WHERE created_on BETWEEN '2022-07-01 00:00:00' AND '2022-12-31 23:59:59'"; 

 $this->assertSame($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect())); 

 $expectedBinds = []; 

 $this->assertSame($expectedBinds, $builder->getBinds()); 

 }

But note that uses false in the third parameter $escape:

CodeIgniter4/tests/system/Database/Builder/WhereTest.php

Line 203 in d425bdf

$builder->where($where, null, false);

If $escape is set to false, the value as is becomes a part of the SQL statement, so you can freely create SQL statements, but QueryBuilder does not provide any protection/escaping.

from codeigniter4.

kenjis commented on June 12, 2024

@oakhurstmgmt If you agree to merge #8792, approve the PR.

from codeigniter4.

Bug: [QueryBuilder] join() with BETWEEN causes TypeError preg_quote(): Argument #1 ($str) must be of type string, false given about codeigniter4 HOT 7 CLOSED

Comments (7)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

	public function testWhereCustomStringWithBetweenEscapeFalse(): void
	{
	$builder = $this->db->table('jobs');

	$where = "created_on BETWEEN '2022-07-01 00:00:00' AND '2022-12-31 23:59:59'";
	$builder->where($where, null, false);

	$expectedSQL = "SELECT * FROM \"jobs\" WHERE created_on BETWEEN '2022-07-01 00:00:00' AND '2022-12-31 23:59:59'";
	$this->assertSame($expectedSQL, str_replace("\n", ' ', $builder->getCompiledSelect()));

	$expectedBinds = [];
	$this->assertSame($expectedBinds, $builder->getBinds());
	}