CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
user1682073
user1682073

Reputation: 47

Write query with JOINs, GROUP BY, and SUM()s using CodeIgniter's query building methods

I am struggling to write the following query using CodeIgniter's query building methods.

 SELECT b.name,
        SUM(CASE WHEN c.size = 'S' THEN 1 ELSE 0 END) S,
        SUM(CASE WHEN c.size = 'M' THEN 1 ELSE 0 END) M,
        SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) L,
        SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) XL
FROM orderTB a
INNER JOIN productTB b ON a.id_product = b.id_shirt
INNER JOIN sizeTB c ON a.id_size = c.id_size
GROUP BY b.name

I've tried like this:

function get()
{
    $this->db->select("b.name,SUM(CASE WHEN c.size ='S' THEN 1 ELSE 0 END) as S,SUM(CASE    WHEN c.size = 'M' THEN 1 ELSE 0 END) as M,SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) as   L,SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) as XL");
    $this->db->from('order');
    $this->db->join('ukuran','order.id_size=ukuran.id_size');
    $this->db->where('date',$date);
    return $this->db->get();
}

but it's clearly wrong.

Upvotes: 2

Views: 7813

Answers (2)

mickmackusa
mickmackusa

Reputation: 47894

I'd like to reduce @iLaYaツ's recommended snippet a little.

  • The CASE statements do not need to explicitly use ELSE 0, the default NULL value will perform the same.
  • I'll use multiple select() method calls to remove identifier quoting only where necessary and keep the column assignments separate/short/readable.
  • It is unclear to me if the JOIN on productTB is truly necessary; if not required, it should be removed.
  • The WHERE clause and FROM clause can be combined via the get_where() method.
  • CodeIgniter's select_sum() method does not allow escaping to be disabled, so it is unsuitable for this task.
  • Some SQL dialects (such as MySQL) allow a boolean evaluation in the SUM() call -- for example sizeTB.size = 'S' without the full CASE syntax. However, other dialects (such as Postgres) do not allow this brevity.
public function getOrderNamesByDateWithSizeSums(string $date): array
{
    return $this->db
        ->select('productTB.name')
        ->select("SUM(CASE WHEN sizeTB.size = 'S' THEN 1 END) S", false)
        ->select("SUM(CASE WHEN sizeTB.size = 'M' THEN 1 END) M", false)
        ->select("SUM(CASE WHEN sizeTB.size = 'L' THEN 1 END) L", false)
        ->select("SUM(CASE WHEN sizeTB.size = 'XL' THEN 1 END) XL", false)
        ->join('productTB', 'orderTB.id_product = productTB.id_shirt')
        ->join('sizeTB', 'orderTB.id_size = sizeTB.id_size')
        ->get_where('orderTB', ['orderTB.date' => $date])
        ->result();
}

Upvotes: 0

iLaYa ツ
iLaYa ツ

Reputation: 4017

Just try this one and let me know, 

$this->db->select("b.name,SUM(CASE WHEN c.size = 'S' THEN 1 ELSE 0 END) S,SUM(CASE WHEN c.size = 'M' THEN 1 ELSE 0 END) M,SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) L,SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) XL", FALSE );
$this->db->from('orderTB a');
$this->db->join('productTB b','a.id_product = b.id_shirt','inner');
$this->db->join('sizeTB c','a.id_size = c.id_size','inner');
$this->db->group_by('b.name');

From document here

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

EDIT

Just add FALSE in your select statement

function get() {
    $this->db->select("b.name,SUM(CASE WHEN c.size ='S' THEN 1 ELSE 0 END) as S,SUM(CASE    WHEN c.size = 'M' THEN 1 ELSE 0 END) as M,SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) as   L,SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) as XL",FALSE);

    $this->db->from('order');
    $this->db->join('ukuran','order.id_size=ukuran.id_size');
    $this->db->where('date',$date);
    return $this->db->get();
}

Upvotes: 10

Related Questions