improve Query Builder to make more complex queries

I suggest query builder should have some options:

1 - easy to make a sub query
today i used...:

$this->db->select('field');
$this->db->from('table')
$this->db->where('field', 'sub select here make in hand ... e.g select field from outher table where outher field= field')

I suggest..

$dbObject = $this->db->select('field')->from('table')->where('');
$this->db->select('field)->from('table')->where('field', $dbObject);

For sub-queries, I believe Query Grouping already handles that, even in CI. That aside, I don't understand how this example would work. You're comparing a field with a partially generated query. How would it know what you're expecting to compare it to?

2 - easy to make a union

$dbObject1 = $this->db->select('field')->from('table')->where('');
$dbObject2 = $this->db->select('field')->from('table')->where('');

$this->db->select()->union($dbObject1, $dbObject2);

You're correct that it doesn't currently have unions. To be honest, I've never used a union in the last 10 years, but I'm probably missing out. Before we could think to do that, we'd need to see how it would compare across all of the databases we support. If that works out, then perhaps. I'd wouldn't mind seeing a pull request for changes to support this, with tests, of course.

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials