Error with complex Oracle query |
(08-09-2017, 10:49 AM)Kaosweaver Wrote: I figured out what is going on, but I'm not sure how to fix it. The last_query() method is, in fact, returning what it thinks is the query, and I see where it's crashing. First let me show you part of my Status_model. ________________________________________________________________ <?php class Status_model extends CI_Model { var $iTable = 'PASSPORT.PASSPORT_INFORMATION_DETAIL'; var $uTable = 'PASSPORT.PASSPORT_USERS'; var $sTable = 'PASSPORT.PASSPORT_STATUS'; var $sql; // order of columns var $column_order = array( 'LAST_NAME', 'FIRST_NAME', 'MIDDLE_NAME', 'PASSPORT_NUMBER' ); // set the searchable columns var $column_search = array( 'LAST_NAME', 'FIRST_NAME', 'MIDDLE_NAME', 'PASSPORT_NUMBER' ); // default sort column var $order = array( 'LAST_NAME' => 'asc' ); private function _get_datatables_query() { // individual column searching $column_count = 0; foreach ($this->column_search as $item) { if ($_POST['columns'][$column_count]['search']['value']) { $this->db->like('LOWER(' . $item . ')', strtolower($_POST['columns'][$column_count]['search']['value'])); // fix to make case-insensitive } $column_count++; } $this->db->select("pu.username, pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number, ps.passport_status_desc, pmax_status_date from passport_users pu, (select pi.username, pi.passport_number, max(pid.passport_status_date) pmax_status_date from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps where pu.username = p_max_status.username and pu.username = ppi.username and ppi.passport_number = p_max_status.passport_number and ppi.passport_number = ppid.passport_number and ppid.passport_status_date = p_max_status.pmax_status_date and ppid.passport_status = ps.passport_status and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate)", FALSE); $i = 0; foreach ($this->column_search as $item) { if ($_POST['search']['value']) { if ($i === 0) { $this->db->group_start(); $this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive } else { $this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive } if (count($this->column_search) - 1 == $i) $this->db->group_end(); } $i++; } if (isset($_POST['order'])) { $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']); } else if (isset($this->order)) { $order = $this->order; $this->db->order_by(key($order), $order[key($order)]); } } function get_datatables() { $this->_get_datatables_query(); if ($_POST['length'] != -1) $this->db->limit($_POST['length'], $_POST['start']); $query = $this->db->get(); //echo $this->db->last_query(); return $query->result(); } function count_filtered() { $this->_get_datatables_query(); $query = $this->db->get(); //echo $this->db->last_query(); return $query->num_rows(); } public function count_all() { $this->db->from($this->uTable); return $this->db->count_all_results(); } ________________________________________________________________ Notice the $this->db->select piece in the _get_datatables_query() function (in blue). It works as expected and the DataTable gets loaded correctly; however, when you try to do a footer or page search, it crashes. If I do a last_query() and look at it from the search, it looks like this: SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number from passport_users pu, (select pi.username, pi.passport_number, max(pid.passport_status_date) pmax_status_date from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps where pu.username = p_max_status.username and pu.username = ppi.username and ppi.passport_number = p_max_status.passport_number and ppi.passport_number = ppid.passport_number and ppid.passport_status_date = p_max_status.pmax_status_date and ppid.passport_status = ps.passport_status and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate) WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!' ORDER BY "LAST_NAME" ASC ) inner_query WHERE rownum < 11) Now, look at the two WHERE clauses in bold (one lower case and one upper case). This can't work! That's broken sql query syntax for Oracle! If I take that query and run it in Toad, it breaks...like it should. But if I change the line from this: WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!' to this: AND LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!' It works! It appears what's causing that is the code above in red. The problem is that I don't know how to get around it or fix it. Does all this make sense? I hope I've explained it well enough. Thanks...
In blue in your post is a $this->db->select() where you have a FROM and WHERE
Then you have a $this->db->where() which appends the SELECT with "WHERE..." giving you the two WHEREs in the SQL. Take out all of the WHERE commands and FROM commands within the SELECT command - and use the respective $this->db->from() and $this->db>where() to construct those. (08-15-2017, 05:04 AM)Kaosweaver Wrote: In blue in your post is a $this->db->select() where you have a FROM and WHERE I don't think the SQL in the $this->db->select() is the problem. Yes, there are two WHERE clauses, but the one is embedded in parenthesis with it's own select. That works fine. It's when I do a search. The other WHERE is coming from this part of the code that I posted, outside of the $this->db->select(). Specifically the first like: foreach ($this->column_search as $item) { if ($_POST['search']['value']) { if ($i === 0) { $this->db->group_start(); $this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive } else { $this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive } I've tried using $this->db->where() and $this->db->from(), etc, to construct this, but it always points back to the same error. I know there's a way to group the like clauses into the query I have, to where it's expressed as an AND instead of an additional WHERE, but I haven't figured it out yet. Thank you...
$this->db->select("pu.username,
pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number, ps.passport_status_desc, pmax_status_date FROM passport_users pu, (select pi.username, pi.passport_number, max(pid.passport_status_date) pmax_status_date from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps WHERE pu.username = p_max_status.username and pu.username = ppi.username and ppi.passport_number = p_max_status.passport_number and ppi.passport_number = ppid.passport_number and ppid.passport_status_date = p_max_status.pmax_status_date and ppid.passport_status = ps.passport_status and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate)", FALSE); --------------------- I don't think it is embedded... (08-16-2017, 06:06 AM)Kaosweaver Wrote: $this->db->select("pu.username, Then why when I run the query in Toad for Oracle, it works fine. It also works fine in CI, with the exception of when I try to do a footer search. The following function is called when doing a footer search: foreach ($this->column_search as $item) { if ($_POST['search']['value']) { if ($i === 0) { $this->db->group_start(); $this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); } else { $this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); } if (count($this->column_search) - 1 == $i) $this->db->group_end(); } $i++; } And by looking at the results from last_query() when I try the footer search, the piece in red is appended. Do you see the difference? SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number from passport_users pu, (select pi.username, pi.passport_number, max(pid.passport_status_date) pmax_status_date from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps where pu.username = p_max_status.username and pu.username = ppi.username and ppi.passport_number = p_max_status.passport_number and ppi.passport_number = ppid.passport_number and ppid.passport_status_date = p_max_status.pmax_status_date and ppid.passport_status = ps.passport_status and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate) WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!' ORDER BY "LAST_NAME" ASC ) inner_query WHERE rownum < 11) Now if I change the red WHERE above to an AND, once again it works in Toad for Oracle.
You have a WHERE clause outside of the subquery in your SELECT declaration.
I color coded it for you and explained that your footer code ADDs a new WHERE to the SQL. You're misusing the SELECT by having a FROM and a WHERE in it. The CI code expects that you'll use the SELECT for SELECT statements (and the FROM and WHERE clauses for those respective functions) - you're using it wrong. Use it as designed and this problem will go away. (08-17-2017, 05:23 AM)Kaosweaver Wrote: You have a WHERE clause outside of the subquery in your SELECT declaration. That's a more plausible response. Now I can move on to a different way of solving this issue. How would I structure the code below using Active Record objects? I've tried a number of different ways, but to no avail. That's why I started using the query as stated in the original post, as shown below. I understand how to setup the $this->db->select(), $this->db->from(), $this->db->join(), and $this->db->where() pieces, etc, but it's the embedded, added part (in red below) that breaks the query as a whole. I can't be the only one that's using complex queries with embedded select statements to populate a DataTable. Did I explain what I'm trying to do well enough? I understand if you can't help. I can start a new thread that better describes my plight. Thank you! select pu.username, pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number, ps.passport_status_desc, pmax_status_date from passport_users pu, (select pi.username, pi.passport_number, max(pid.passport_status_date) pmax_status_date from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps where pu.username = p_max_status.username and pu.username = ppi.username and ppi.passport_number = p_max_status.passport_number and ppi.passport_number = ppid.passport_number and ppid.passport_status_date = p_max_status.pmax_status_date and ppid.passport_status = ps.passport_status and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate)", FALSE);
When I have very complex queries using subqueries, I just use a string and assemble the query and then use the $this->db->query() to make it work.
In your case, since it appears the $this->db->select will take the FROM clause and parse it (which is still wrong, but it is a "feature" not a "bug" that it works...) - you could just use the existing code as you have it, take out just the WHERE statements and put those in the $this->db->where() statements which *should* append correctly for you. I'd assemble a sql string and then just pass it to the db->query function. There is nothing magical about the active record code that makes it superior to straight SQL. (apart from filtering/security - but your query has no input/variables in to it)
04-15-2018, 11:55 PM
(This post was last modified: 04-16-2018, 02:21 AM by ciadmin. Edit Reason: SEO link redacted )
$this->db->from("passport_users, (select pi.username,
pi.passport_number, max(pid.passport_status_date) pmax_status_date from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps"); |
Welcome Guest, Not a member yet? Register Sign In |