[eluser]Jon L[/eluser]
Keep in mind that I'm developing with a copy of the AR class that I have extracted from CI SVN, and I am not working within CI, which is why you'll see me accessing the AR class differently.
Anyways, I've seen others requesting the ability to group WHERE statements, and I've wanted a solution for this as well. After working on this headache for 2 solid days, I have a solution that works well.
* another note, the build_query method is an implementation to compile queries without executing, so I can test more easily
Example code:
Code:
<?php
$mysqli->where('test', 'value');
$mysqli->where('test2', 'value2');
$mysqli->group_where();
$mysqli->where('test4', 'value4');
$mysqli->group_where_or();
$mysqli->where('test5', 'value5');
$mysqli->where('test6', 'value6');
$mysqli->ungroup_where();
$mysqli->or_where('test7', 'value7');
$mysqli->ungroup_where();
$mysqli->where('test8', 'value8');
echo $mysqli->build_query('test');
?>
Result:
Code:
SELECT *
FROM (`test`)
WHERE `test` = 'value' AND `test2` = 'value2' AND (`test4` = 'value4' OR (`test5` = 'value5' AND `test6` = 'value6') OR `test7` = 'value7') AND `test8` = 'value8'
Thoughts anyone?
The only issue I have currently, is how to deal with the where cache, because ar_where now stores a multi-dimensional array, which could pose problems if someone is starting/stopping cache & mixing that with different calls to group_where()
The good thing about these changes, is that I should now be able to add in support for sub-selects, nested joins, etc...
In my custom copy of AR, I've also addressed the following issues:
- Incorrect processing of strings passed to SELECT (old behavior just explodes on commas without checking for parenthesized statements)
- The ` mark is not added to tables/fields that are within parentheses
- update/delete didn't properly build WHERE statements (ignored LIKE)
- Added new methods between(), not_between()
- Added new method where_not()
I've also corrected some duplicate logic issues regarding the separation of WHERE and LIKE, when really LIKE should fall under WHERE and should not be stored separately (makes building of WHERE statements much easier).
Any feedback/suggestions is appreciated. I intend to contribute this code back to the community once I'm sure there are no issues that my changes introduce.
