CodeIgniter Forums
Active Record where clause parenthesis [PATCHED FILE] - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Active Record where clause parenthesis [PATCHED FILE] (/thread-37360.html)



Active Record where clause parenthesis [PATCHED FILE] - El Forum - 01-06-2011

[eluser]Nick_MyShuitings[/eluser]
So,You'll have to bear with the rant if you want the code... that's not actually true you could skip it, but then you'd have to figure out what the hell the code is supposed to do... anyway.

There were at least three posts in the last day or two talking about/requesting this feature. Plus the one that I read in the Reactor Forum. Plus two or three that I responded to a while back.

All of them are requesting the addition to active record of some code that will allow you to make more complex queries that contain a clause like this WHERE (SOME MULTIPLE STATEMENTS) AND (SOME MORE STATEMENTS) OR (SOME STUFF) etc.

First off its really not that hard to get that part done... the question is why bother? If you had enough thought to put into the SQL logic, why not just write the damn sql? Perhaps you say, to take advantage of the sanitation? That would a benefit, and it can be achieved using query bindings a la user guide

Code:
$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

The only other benefit is so that your code is portable to other databases... I concede that point, but don't think the percentage of real life cases in which that might occur are worth modifying the Active Record Class.


Other then that there are no benefits beyond the quasi conjecture that its easier to write using AR.

You do however assume a lot of function calls that are fairly trivial in and of themselves, but do not provide any clear direction aside from holding your hand in the writing of a query.

That said, attached is my hacked AR class you'd need to put it into your system/database/ folder. It is the product of only an hour of work, while at the office eating lunch... so it definitely has bugs and if you are silly enough to put it into a production environment blame yourself.

*It only goes 1 level deep groupings, (considering I think this is totally useless I didn't want to take any more time to work on making it n-levels groupable).
*You have to use all the parameters of the AR you want, and then add the group number (again, if I were to use this for real, I'd put the group number after the most commonly used params - third place)
*You cannot begin a grouped section with an OR statement or it would fail (to lazy to mod it, all that would have to be done is swap the array and make sure each group starts with a positive clause)
*You have to establish the joiner to use between groups.
*It considers anything without a group to be in group 0 (sheer laziness on my part, the solution is trivial if you look at the code)

And thats about all I can think of. Here is an example:
Code:
$this->db->where('aid','comment_publish_action'); //group 0
      $this->db->where('type','comment'); //group 0
      $this->db->where('aid','comment_publish_action',TRUE,1); //group 1
      $this->db->where('type','comment',TRUE,1); //group 1
      $array = array ('comment_publish_action',2);
      $this->db->where_in('aid',$array,FALSE,'AND ',1); //group 1
      $this->db->clause_grouping('AND');
        echo $this->db->last_query();

And here is the output:
Code:
array(1) {
  [0]=>
  object(stdClass)#14 (5) {
    ["aid"]=>
    string(22) "comment_publish_action"
    ["type"]=>
    string(7) "comment"
    ["callback"]=>
    string(22) "comment_publish_action"
    ["parameters"]=>
    string(0) ""
    ["label"]=>
    string(15) "Publish comment"
  }
}
SELECT *
FROM (`actions`)
WHERE (`aid` = 'comment_publish_action'
AND `type` = 'comment')AND(`aid` = 'comment_publish_action'
AND `type` = 'comment'
AND `aid` IN ('comment_publish_action', 2) )NULL

That said, I think there is a reason that none of the CI guru's ever took the time to do this... its not of any clear benefit, and it actually detracts from the simplicity of the Active Record Class method calls.

End of Rant... please let me know if you see any actual use to this.


Active Record where clause parenthesis [PATCHED FILE] - El Forum - 01-07-2011

[eluser]Nick_MyShuitings[/eluser]
[whine][pout] This is why we don't have nice things... lol [/pout][/whine]

At lunch and bored again, wondering why the interest in this dissipated. Sigh, off for more forum trolling to find interesting topics.


Active Record where clause parenthesis [PATCHED FILE] - El Forum - 03-27-2011

[eluser]Unknown[/eluser]
It is not on my side, thanks for your patch Smile


Active Record where clause parenthesis [PATCHED FILE] - El Forum - 03-27-2011

[eluser]Nick_MyShuitings[/eluser]
Haha... I had forgotten about this post. Glad it came in useful at least for someone.