+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Feature Requests (https://forum.codeigniter.com/forumdisplay.php?fid=29)
+--- Thread: improve Query Builder to make more complex queries (/showthread.php?tid=66356)
Pages:
1
2
improve Query Builder to make more complex queries - andersonunsonst - 10-14-2016
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);
RE: improve Query Builder to make more complex queries - prezire - 10-14-2016
I believe this is doable even on CI3.
RE: improve Query Builder to make more complex queries - kilishan - 10-16-2016
(10-14-2016, 06:25 AM)andersonunsonst Wrote: 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?
(10-14-2016, 06:25 AM)andersonunsonst Wrote: 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.
RE: improve Query Builder to make more complex queries - iRedds - 05-10-2019
(10-16-2016, 08:20 PM)kilishan Wrote: 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
RE: improve Query Builder to make more complex queries - scalla - 05-10-2019
(05-10-2019, 12:54 AM)iRedds Wrote:(10-16-2016, 08:20 PM)kilishan Wrote: 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.
RE: improve Query Builder to make more complex queries - scalla - 05-10-2019
@kilishan I share his sentiment of this issue.
RE: improve Query Builder to make more complex queries - dave friend - 05-11-2019
I agree on the need for a more elegant subquery solution.
RE: improve Query Builder to make more complex queries - kilishan - 05-12-2019
Looks like someone has submitted a PR for this that I'll review shortly.
RE: improve Query Builder to make more complex queries - iRedds - 05-13-2019
(05-12-2019, 08:24 PM)kilishan Wrote: 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.
RE: improve Query Builder to make more complex queries - MGatner - 05-14-2019
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.