Add union() to DB Query Builder - Printable Version +- 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: Add union() to DB Query Builder (/showthread.php?tid=68239) |
Add union() to DB Query Builder - krystian2160 - 06-14-2017 It would be nice to have union() in CI's Query Builder RE: Add union() to DB Query Builder - skunkbad - 06-14-2017 I don't know. With a properly designed database, there's almost never a good reason to use UNION. There are no performance benefits to using it vs separate queries. It's rarely needed. RE: Add union() to DB Query Builder - ivantcholakov - 06-14-2017 @skunkbad Code: -- Filling a dropdown menu about citizenship RE: Add union() to DB Query Builder - ivantcholakov - 06-14-2017 A possible now (CI3) usage of the query builder: Code: $sql = array(); The good thing is that in this simple and plausible example I don't have to bother about the SQL dialect, here are some generated results: MySQL: Code: (SELECT 'XX' AS country_code, '-- Not specified --' AS country_name) PostgreSQL: Code: (SELECT 'XX' AS country_code, '-- Not specified --' AS country_name) I don't know how tricky the implementation of UNION within the query builder might be. At least I can see that for every sub-query a separate instance of the builder object would be needed. But even without such an implementation, usage of the query builder is possible as it was shown. Note: As far as I can remember PostgerSQL would ignore the ORDER BY clause within the sub-queries in this case, but this is not my problem. :-) RE: Add union() to DB Query Builder - zurtri - 06-14-2017 (06-14-2017, 11:04 AM)skunkbad Wrote: I don't know. With a properly designed database, there's almost never a good reason to use UNION. There are no performance benefits to using it vs separate queries. It's rarely needed. I completely agree with skunkbad here. I've even logged in after a couple of months of lurking to comment. Using UNION is a Dark Path and should be avoided as much as possible. Codeigniter's strength is being lean. The introduction of db->union() would bloat CI and then promote bad practice. BUT - I know the world is a messy place and occasionally we need to engineer nasty solutions, so perhaps:
RE: Add union() to DB Query Builder - ivantcholakov - 06-14-2017 UNION is just a tool and it is programmer's responsibility to decide where and why to use it. db->union(): I can imagine something else (CI3-like syntax): Code: $result = $this->db The only problem is how hard the implementation of UNION would be. RE: Add union() to DB Query Builder - skunkbad - 06-14-2017 (06-14-2017, 02:56 PM)ivantcholakov Wrote: @skunkbad 1) I know you know that you could just put that in $this->db->query(). 2) I'd probably just pull them in in a single query and then process them through PHP. 3) That query could be simplified with an "ORDER BY `country_name` = 'United States', `country_name` ASC", and add the empty option with PHP. 4) I suppose if you want MySQL to do all the work, you're right. RE: Add union() to DB Query Builder - kilishan - 06-14-2017 The development effort would not be trivial, especially when you have to ensure it works across every platform we end up supporting. I wouldn't expect this to happen prior to release, if it happens at all. And everyone seems to forget you can always bypass the query builder and simply use query() with query bindings to keep it safe. Simplifies things for everyone. RE: Add union() to DB Query Builder - krystian2160 - 06-15-2017 @zurtri Why "Using UNION is a Dark Path and should be avoided as much as possible." ? RE: Add union() to DB Query Builder - Kaosweaver - 06-19-2017 (06-14-2017, 08:26 PM)kilishan Wrote: And everyone seems to forget you can always bypass the query builder and simply use query() with query bindings to keep it safe. Simplifies things for everyone. This is what I do on very complex queries (or dynamic queries). And it works fine with the UNION command as I've used it for that (although in hindsight, I should have done two queries and just merged the results...) |