CodeIgniter Forums
Subquery - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forum-11.html)
+--- Thread: Subquery (/thread-70943.html)



Subquery - snelledre - 06-19-2018

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($strSubQueryfalse);
 
       $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


RE: Subquery - Pertti - 06-19-2018

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"
); 



RE: Subquery - InsiteFX - 06-20-2018

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


RE: Subquery - neuron - 06-24-2018

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


RE: Subquery - eacm - 01-12-2022

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);
    }

Quote: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