-
meSmashsta
Junior Member
-
Posts: 25
Threads: 10
Joined: Dec 2016
Reputation:
1
PHP Code: $fname_format = "CONCAT(b.last_name, ', ', b.first_name, ' ', b.middle_name) AS full_name"; $this->db->select("a.id, b.id AS employee_id, {$fname_format}, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action,a.root_caused,a.remarks,a.item_replaced"); $this->db->from("reports AS a"); $this->db->join("employees AS b", "b.id = a.employee_id"); $this->db->join("machines AS c", "c.id = a.machine_id"); $this->db->join("hstatus AS d", "d.id = a.hstatus_id"); return this->db->get()->result_array();
Look at the select part, it's so ugly...
-
skunkbad
Senior Citizen
-
Posts: 1,300
Threads: 63
Joined: Oct 2014
Reputation:
86
03-18-2017, 11:06 AM
(This post was last modified: 03-18-2017, 11:11 AM by skunkbad.)
(03-18-2017, 10:43 AM)meSmashsta Wrote: PHP Code: $fname_format = "CONCAT(b.last_name, ', ', b.first_name, ' ', b.middle_name) AS full_name"; $this->db->select("a.id, b.id AS employee_id, {$fname_format}, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action,a.root_caused,a.remarks,a.item_replaced"); $this->db->from("reports AS a"); $this->db->join("employees AS b", "b.id = a.employee_id"); $this->db->join("machines AS c", "c.id = a.machine_id"); $this->db->join("hstatus AS d", "d.id = a.hstatus_id"); return this->db->get()->result_array();
Look at the select part, it's so ugly...
Trying to use CI query builder makes it worse than it has to be. Why not just use:
PHP Code: $sql = ' SELECT a.id, b.id AS employee_id, CONCAT( b.last_name, ', ', b.first_name, ' ', b.middle_name ) AS full_name, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action, a.root_caused, a.remarks, a.item_replaced FROM reports AS a LEFT JOIN employees AS b ON b.id = a.employee_id LEFT JOIN machines AS c ON c.id = a.machine_id LEFT JOIN hstatus AS d ON d.id = a.hstatus_id '; $query = $this->db->query($sql);
At least then you can read it.
-
PaulD
Posting Freak
-
Posts: 1,061
Threads: 42
Joined: Mar 2015
Reputation:
73
03-18-2017, 03:29 PM
(This post was last modified: 03-18-2017, 04:15 PM by PaulD.)
If you are going to spread it out for readability, how about something like:
PHP Code: return $this->db->from("reports AS a") ->select("a.id, b.id AS employee_id, { CONCAT( b.last_name, ', ', b.first_name, ' ', b.middle_name ) AS full_name }, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action, a.root_caused, a.remarks, a.item_replaced"), ->join("employees AS b", "b.id = a.employee_id"), ->join("machines AS c", "c.id = a.machine_id"), ->join("hstatus AS d", "d.id = a.hstatus_id"), ->get() ->result_array();
You can chain the db calls which makes everything so much neater too.
I am not sure why you have all the AS statements. Personally my tables would have columns like machine_id and employee_id and every field in, say, the employee table starts with employee_first_name, employee_last_name etc. So no collisions, and much simpler queries, and I always know what table produced what field without any backtracking through queries. Personally I hate all the AS a and a.id = b.id or c.id as it is meaningless for a human reader.
-
meSmashsta
Junior Member
-
Posts: 25
Threads: 10
Joined: Dec 2016
Reputation:
1
(03-18-2017, 11:06 AM)skunkbad Wrote: (03-18-2017, 10:43 AM)meSmashsta Wrote: PHP Code: $fname_format = "CONCAT(b.last_name, ', ', b.first_name, ' ', b.middle_name) AS full_name"; $this->db->select("a.id, b.id AS employee_id, {$fname_format}, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action,a.root_caused,a.remarks,a.item_replaced"); $this->db->from("reports AS a"); $this->db->join("employees AS b", "b.id = a.employee_id"); $this->db->join("machines AS c", "c.id = a.machine_id"); $this->db->join("hstatus AS d", "d.id = a.hstatus_id"); return this->db->get()->result_array();
Look at the select part, it's so ugly...
Trying to use CI query builder makes it worse than it has to be. Why not just use:
PHP Code: $sql = ' SELECT a.id, b.id AS employee_id, CONCAT( b.last_name, ', ', b.first_name, ' ', b.middle_name ) AS full_name, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action, a.root_caused, a.remarks, a.item_replaced FROM reports AS a LEFT JOIN employees AS b ON b.id = a.employee_id LEFT JOIN machines AS c ON c.id = a.machine_id LEFT JOIN hstatus AS d ON d.id = a.hstatus_id '; $query = $this->db->query($sql);
At least then you can read it.
Thanks.
-
meSmashsta
Junior Member
-
Posts: 25
Threads: 10
Joined: Dec 2016
Reputation:
1
(03-18-2017, 03:29 PM)PaulD Wrote: If you are going to spread it out for readability, how about something like:
PHP Code: return $this->db->from("reports AS a") ->select("a.id, b.id AS employee_id, { CONCAT( b.last_name, ', ', b.first_name, ' ', b.middle_name ) AS full_name }, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action, a.root_caused, a.remarks, a.item_replaced"), ->join("employees AS b", "b.id = a.employee_id"), ->join("machines AS c", "c.id = a.machine_id"), ->join("hstatus AS d", "d.id = a.hstatus_id"), ->get() ->result_array();
You can chain the db calls which makes everything so much neater too.
I am not sure why you have all the AS statements. Personally my tables would have columns like machine_id and employee_id and every field in, say, the employee table starts with employee_first_name, employee_last_name etc. So no collisions, and much simpler queries, and I always know what table produced what field without any backtracking through queries. Personally I hate all the AS a and a.id = b.id or c.id as it is meaningless for a human reader.
I'll take a note at that.
-
John_Betong
Super Moderator
-
Posts: 588
Threads: 52
Joined: Oct 2014
Reputation:
4
03-18-2017, 07:39 PM
(This post was last modified: 03-18-2017, 07:41 PM by John_Betong.)
(03-18-2017, 05:39 PM)Try PHP heredoc Wrote: PHP Code: /* BEWARE ONLY LINEFEED AFTER FIRST ____TMP AND ; AFTER LAST ____TMP NO SPACES ALLOWED OTHERWISE SYNTAX ERROR :( */
$sql = <<< ____TMP
SELECT a.id, b.id AS employee_id, CONCAT ( b.last_name, ', ', b.first_name, ' ', b.middle_name ) AS full_name, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action, a.root_caused, a.remarks, a.item_replaced FROM reports AS a LEFT JOIN employees AS b ON b.id = a.employee_id LEFT JOIN machines AS c ON c.id = a.machine_id LEFT JOIN hstatus AS d ON d.id = a.hstatus_id ____TMP;
$query = $this->db->query($sql);
-
meSmashsta
Junior Member
-
Posts: 25
Threads: 10
Joined: Dec 2016
Reputation:
1
(03-18-2017, 07:39 PM)John_Betong Wrote: (03-18-2017, 05:39 PM)Try PHP heredoc Wrote: PHP Code: /* BEWARE ONLY LINEFEED AFTER FIRST ____TMP AND ; AFTER LAST ____TMP NO SPACES ALLOWED OTHERWISE SYNTAX ERROR :( */
$sql = <<< ____TMP
SELECT a.id, b.id AS employee_id, CONCAT ( b.last_name, ', ', b.first_name, ' ', b.middle_name ) AS full_name, c.id AS machine_id, c.title AS machine, d.title AS hstatus, a.created_on, a.action_on, a.edit_on, a.problems_encountered, a.corrective_action, a.root_caused, a.remarks, a.item_replaced FROM reports AS a LEFT JOIN employees AS b ON b.id = a.employee_id LEFT JOIN machines AS c ON c.id = a.machine_id LEFT JOIN hstatus AS d ON d.id = a.hstatus_id ____TMP;
$query = $this->db->query($sql);
Looks nice!
|