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);

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);

I believe this is doable even on CI3.

Long live CodeIgniter!

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

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?

Grouping queries looks ugly. I want to suggest using an anonymous function.

PHP Code:

$builder->where(function(BaseBuilder $builder) {
   return $builder->where('a', 'a')
       ->orWhereNot(function(BaseBuilder $builder) {
            return $builder->where('b', 'b')->where('c', 'c');
       });
})->orWhereNot('d', 'd');

// SELECT * FROM `test` WHERE ( `a` = 'a' OR NOT ( `b` = 'b' AND `c` = 'c' ) ) OR `d` != 'd' 


Subqueries?

PHP Code:

$builder->where('key', function(BaseBuilder $builder) {
    return $builder->where('b', 'b')->select('key')->from('test_2');
});

// SELECT * FROM `test` WHERE key = (SELECT `key` FROM `test_2` WHERE `b` = 'b')

$builder->whereIn('key', function(BaseBuilder $builder) {
    return $builder->where('b', 'b')->select('key')->from('test_2');
});
// SELECT * FROM `test` WHERE `key` IN (SELECT `key` FROM `test_2` WHERE `b` = 'b') 


it's easy

Attached Files

  BaseBuilder.php (Size: 71.71 KB / Downloads: 49)

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?

Grouping queries looks ugly. I want to suggest using an anonymous function.

PHP Code:

$builder->where(function(BaseBuilder $builder) {
   return $builder->where('a', 'a')
       ->orWhereNot(function(BaseBuilder $builder) {
            return $builder->where('b', 'b')->where('c', 'c');
       });
})->orWhereNot('d', 'd');

// SELECT * FROM `test` WHERE ( `a` = 'a' OR NOT ( `b` = 'b' AND `c` = 'c' ) ) OR `d` != 'd' 


Subqueries?

PHP Code:

$builder->where('key', function(BaseBuilder $builder) {
    return $builder->where('b', 'b')->select('key')->from('test_2');
});

// SELECT * FROM `test` WHERE key = (SELECT `key` FROM `test_2` WHERE `b` = 'b')

$builder->whereIn('key', function(BaseBuilder $builder) {
    return $builder->where('b', 'b')->select('key')->from('test_2');
});
// SELECT * FROM `test` WHERE `key` IN (SELECT `key` FROM `test_2` WHERE `b` = 'b') 


it's easy

You show submit a PR of the uploaded file, it would be easy to review.

@kilishan I share his sentiment of this issue.

I agree on the need for a more elegant subquery solution.

Looks like someone has submitted a PR for this that I'll review shortly.

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

Looks like someone has submitted a PR for this that I'll review shortly.

It was my PR, but without query grouping.

I am interested in your opinion on the query grouping that I suggested above.

I'm late to this party but this looks like an enticing feature. I use subqueries quite a bit in non-framework contexts, so it would be nice to have a transparent way to do that in CI4 as well.