Subquery

Hello,

I'm new to Codeigniter and learn a lot every day
I have a subquery that works perfectly in MSSQL.

Code:

SELECT SUM(Weight) as Total, Recipegroup, Production
FROM (SELECT Rejectspecies, Production, Productionsite, Pullution, Weight, Recipegroup, Recipe, Datetime
FROM dbo.Reject
WHERE (Datetime BETWEEN DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND
        DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND
        (Rejectspecies = 'Diervoeder koek' OR Rejectspecies = 'Diervoerder deeg' OR Rejectspecies = 'Restafval')) AS Totals
GROUP BY Production, Recipegroup

I want this to convert to codeigniet db helper but i wont work.
This is what i have.

PHP Code:

   public function get_reusable_early()
    {
        $strSubQuery = $this->db
            ->select("Rejectspecies, Production, Productionsite, Pullution, Weight, Recipegroup, Recipe, Datetime")
            ->from("dbo.Reject")
            ->where("(Datetime BETWEEN DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND 
        DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND 
        (Rejectspecies = 'Diervoeder koek' OR Rejectspecies = 'Diervoerder deeg' OR Rejectspecies = 'Restafval')")
            ->get_compiled_select();

        $this->db->select('SUM(Weight) as Total, Recipegroup, Production', false);
        $this->db->from($strSubQuery, false);
        $this->db->group_by('Production, Recipegroup');
        $this->db->having('Production', 'Lijn A');
        $query = $this->db->get();

        return $query->result_array();
    } 


But won't work i get the next error.

Code:

A Database Error Occurred
Error Number: 42000/156

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'FROM'.

SELECT SUM(Weight) as Total, Recipegroup, Production FROM "SELECT" "Rejectspecies", "Production", "Productionsite", "Pullution", "Weight", "Recipegroup", "Recipe", "Datetime" FROM "dbo"."Reject" WHERE (Datetime BETWEEN DATEADD(day, "-" 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND DATEADD(day, "-" 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND ("Rejectspecies" = 'Diervoeder koek' OR "Rejectspecies" = 'Diervoerder deeg' OR "Rejectspecies" = 'Restafval') GROUP BY "Production", "Recipegroup" HAVING "Production" = 'Lijn A'

Filename: C:/inetpub/wwwroot/reject/system/database/DB_driver.php

Line Number: 691

Can anybody help me with the query builder?

Andre
From the Netherlands

Quickest solution is to just run SQL query with query method:

PHP Code:

$result = $this->db->query("SELECT SUM(Weight) as Total, Recipegroup, Production 
FROM (SELECT Rejectspecies, Production, Productionsite, Pullution, Weight, Recipegroup, Recipe, Datetime
FROM dbo.Reject
WHERE (Datetime BETWEEN DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '15:15:00', 108))) AND 
        DATEADD(day, - 7, CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), '23:15:00', 108)))) AND 
        (Rejectspecies = 'Diervoeder koek' OR Rejectspecies = 'Diervoerder deeg' OR Rejectspecies = 'Restafval')) AS Totals
GROUP BY Production, Recipegroup"); 


Each where clause after the first one will add the AND clause.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

I don't know the syntax of mssql but in mysql you should wrap subquery with parantheses and give it an alias name:

PHP Code:

$this->db->select('*')
->from("($sql) as t", false); 


so the result query will be :

SELECT * 
FROM (select * from users) as t 

Also for grouping where clauses you can use $this->db->group_start() function. For details see documentation

Code:

   
public function LaporanQuery($dari, $sampai, $pertashop){
      return $this->db->query("SELECT DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AS tanggal,
      id_pertashop,
      ( SELECT ketinggian_stick
        FROM tbl_stick
        WHERE id_shift='1'
          AND id_pengecekan='1'
          AND DATE_FORMAT(tbl_stick.tanggal,'%d/%m/%Y') = DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y')
          AND tbl_stick.id_pertashop=tbl_laporan1.id_pertashop) AS StokAwal,
      ( SELECT tbl_pembelian.jumlah
        FROM tbl_pembelian
        WHERE DATE_FORMAT(tbl_pembelian.tanggal,'%d/%m/%Y') = DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y')
          AND tbl_pembelian.id_pertashop=tbl_laporan1.id_pertashop) AS Pembelian,
      ( SELECT teller
        FROM tbl_teller
        WHERE id_pengecekan ='2'
          AND id_shift ='2'
          AND DATE_FORMAT(tbl_teller.tanggal,'%d/%m/%Y')=DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y')
          AND tbl_teller.id_pertashop=tbl_laporan1.id_pertashop)
      -
      ( SELECT teller
        FROM tbl_teller
        WHERE id_pengecekan ='1'
          AND id_shift = '1'
          AND DATE_FORMAT(tbl_teller.tanggal,'%d/%m/%Y')= DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y')
          AND tbl_teller.id_pertashop=tbl_laporan1.id_pertashop) AS Penjualan,
      ( SELECT ketinggian_stick
        FROM tbl_stick
        WHERE id_shift='2'
          AND id_pengecekan='2'
          AND DATE_FORMAT(tbl_stick.tanggal,'%d/%m/%Y') = DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y')
          AND tbl_stick.id_pertashop=tbl_laporan1.id_pertashop) AS StockAkhir
        FROM tbl_laporan1
        ORDER BY tbl_laporan1.tanggal;
        ");
        return $query;
    }

Code:

public function index(){
        // $query['data'] = $this->M_AStick->DaftarStick()->result_array();
        $dari                      = $this->input->post('dari');
        $sampai                    = $this->input->post('sampai');
        $pertashop                  = $this->input->post('pertashop');
        $query['data_dari']        =  $dari;
        $query['data_sampai']      =  $sampai;
        $query['data_pertashop']    =  $pertashop;
        $query['nama_pertashop']    = $this->M_APertashop->daftar_pertashop();
        $query['pertashop']        = $this->M_APertashop->idpertashop()->result_array();
        $query['data']              = $this->M_APertashop->LaporanQuery($dari, $sampai,$pertashop)->result_array();
       
        // $query['yy'] = $this->M_APertashop->LaporanQueryAkhir($dari, $sampai,$pertashop);

        $this->template->set('title','Laporan');
        $this->template->load('layout_admin', 'contents', 'admin/laporan/daftar_laporan',$query);
    }

Error Number: 1242

Subquery returns more than 1 row

SELECT DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AS tanggal, id_pertashop, ( SELECT ketinggian_stick FROM tbl_stick WHERE id_shift='1' AND id_pengecekan='1' AND DATE_FORMAT(tbl_stick.tanggal,'%d/%m/%Y') = DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AND tbl_stick.id_pertashop=tbl_laporan1.id_pertashop) AS StokAwal, ( SELECT tbl_pembelian.jumlah FROM tbl_pembelian WHERE DATE_FORMAT(tbl_pembelian.tanggal,'%d/%m/%Y') = DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AND tbl_pembelian.id_pertashop=tbl_laporan1.id_pertashop) AS Pembelian, ( SELECT teller FROM tbl_teller WHERE id_pengecekan ='2' AND id_shift ='2' AND DATE_FORMAT(tbl_teller.tanggal,'%d/%m/%Y')=DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AND tbl_teller.id_pertashop=tbl_laporan1.id_pertashop) - ( SELECT teller FROM tbl_teller WHERE id_pengecekan ='1' AND id_shift = '1' AND DATE_FORMAT(tbl_teller.tanggal,'%d/%m/%Y')= DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AND tbl_teller.id_pertashop=tbl_laporan1.id_pertashop) AS Penjualan, ( SELECT ketinggian_stick FROM tbl_stick WHERE id_shift='2' AND id_pengecekan='2' AND DATE_FORMAT(tbl_stick.tanggal,'%d/%m/%Y') = DATE_FORMAT(tbl_laporan1.tanggal,'%d/%m/%Y') AND tbl_stick.id_pertashop=tbl_laporan1.id_pertashop) AS StockAkhir FROM tbl_laporan1 ORDER BY tbl_laporan1.tanggal;

Filename: models/M_APertashop.php

Line Number: 161