• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to make this query a little bit more readable?

#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...
Reply

#2
(03-18-2017, 11: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.
Reply

#3
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.
Reply

#4
(03-18-2017, 12:06 PM)skunkbad Wrote:
(03-18-2017, 11: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.
Reply

#5
(03-18-2017, 04: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.
Reply

#6
(03-18-2017, 06: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); 
CI4 - Playground:
    https://www.john-betong.tk/test/1/42
CI 3.15  -  AmpProject 
     http://tools.pingdom.com/fpt/#!/uC1KU/ww...-jokes.com

Reply

#7
(03-18-2017, 08:39 PM)John_Betong Wrote:
(03-18-2017, 06: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!
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.