CodeIgniter Forums

Full Version: Subquery
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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
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.
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