Addin a SUM() field to a custom query yields a database exception
Hello all,
That's my first post after using QCubed for about two months for a professionnal project. I'm starting to find my way around this great framework, but here's something I've been stuck on for a couple days :
I have a database with users and projects. There's a user table, a project table, an ActiveuserProjects table that list the projects any user is currently working on and a DayProject table that stores the users activity (userId, projectId, day and amount worked).
I have a custom query that calculates the total number of days worked on a given project, and the remaining days based on the project estimated duration. Here's the query :
SELECT ActiveUserProjects.IdProject, project.est_days,
SUM( IF (amount IS NULL, 0,amount)) AS __TotalWorked,
project.est_days - SUM( IF (amount IS NULL, 0,amount)) AS __RemainingDays
FROM ActiveUserProjects
LEFT JOIN DayProject ON DayProject.IdProject = ActiveUserProjects.IdProject
LEFT JOIN Project ON Project.idProject = ActiveUserProjects.idProject
WHERE ActiveUserProjects.idUser = 2
GROUP BY ActiveUserProjects.idProject;Whenever I actually activate the SUM() columns, I get this kind of error :
MySqli Error: Incorrect table name ''
Exception Type: QMySqliDatabaseException
Source File: /Applications/xampp/xamppfiles/htdocs/wordpress-mu/timesheet/include/qcodo/_core/database/QMySqliDatabase.class.php Line: 547
Line 547: $objDescriptionResult = $objDb->Query(sprintf("DESCRIBE `%s`", $this->strOriginalTable));
The strOriginalTable variable is empty.
My data loading class is :
<?php
public static function GetProjectSummary($intIdUser) {
$strQuery = "SELECT Project.Name,ActiveUserProjects.IdProject, Project.Est_days as EstDays, SUM(amount) as Amount
FROM ActiveUserProjects
LEFT JOIN DayProject ON DayProject.IdProject = ActiveUserProjects.IdProject
LEFT JOIN Project ON Project.idProject = ActiveUserProjects.idProject
WHERE ActiveUserProjects.idUser = ". $intIdUser ."
GROUP BY ActiveUserProjects.idProject;";
$objDatabase = QApplication::$Database[1];
$objDbResult = $objDatabase->Query($strQuery);
$objResult = array();
$objFields = $objDbResult->FetchFields();
while ($objDbRow = $objDbResult->FetchArray()) {
array_push($objResult, $objDbRow);
}
return $objResult;
}
?>and the data binding is done as such :
$this->dtgProjects->DataSource = ActiveUserProjects::GetProjectSummary($this->idUser);Without the sum() columns, everything works, when I add any of the two I want to use, I get the exception. As I'm not sure that this can be done with a QQuery, how can I use my calculated columns? I've been looking at the qcodo example site and forum to no avail...
Thanks
Maybe you should be using SUM(ActiveUserProjects.amount) instead?
To add to the point that Vexed is making, it's often easier to troubleshoot SQL queries by running them directly against your server (without QCubed) through a tool like phpMyAdmin. If the query succeeds there, you know it's QCubed's "fault" - if not, it's just an error in the SQL statement.
I think you must have the columns Project.Name, ActiveUserProjects.IdProject and Project.Est_days in the Group By clause
Thanks for the answers. I forgot to say in my first post that the query worked fine by itself. I tried to add the other fields in the group by clause, as suggested, but to no avail. The query works, but not within Qcubed.
On a side note, when unsure about dynamically built queries, I add an error in the query so that it shows in the resulting expression. I can then copy it and test it in the mysql query tool. A clunky way to debug queries, but it works for me.
So I guess that QCubed is at fault here, although I don't have the ninja skills to prove why the sum() method would crash it.
Maybe I oversee something, but I see two different queries there. Have you tried both of them in phpMyAdmin?
Yeah, I'm also noticing that the SQL query that you originally posted is NOT the same query that you're running in QCubed. Please try with the *same exact* query.
Personally, I would recommend a tool like SQuirreL over phpMyAdmin. The latter is a very painful way to interact with databases. If you need to access a database that is restricted to internal access only then ssh port forwarding is your friend.
Hum, talk about a confusing first post. I'm sorry guys :)
Ok, so the SQL query I have is now the same in the code, with the full group by :
SELECT ActiveUserProjects.IdProject, project.est_days,SUM( IF (amount IS NULL, 0,amount)) AS __TotalWorked,
project.est_days - SUM( IF (amount IS NULL, 0,amount)) AS __RemainingDays
FROM ActiveUserProjects
LEFT JOIN DayProject ON DayProject.IdProject = ActiveUserProjects.IdProject
LEFT JOIN Project ON Project.idProject = ActiveUserProjects.idProject
WHERE ActiveUserProjects.idUser = 2
GROUP BY ActiveUserProjects.idProject,Project.Name, ActiveUserProjects.IdProject and Project.Est_days
Still no luck, unless I remove the SUM columns :
Same error with a COUNT() column, by the way, and the IF clause is not the problem as even a SUM(0) brings up the error.
As for tools, I believe that the only advantage of phpMyAdmin over the other tools is that it's web based. I'm happily using MySQLWorkbench for the design part and Sequel Pro for the querying (I'm on a mac). Apparently MySQLWorkbench will get a query tool in the next version. This should be good.
Back to the problem : if I do a "SELECT SUM(IdUser) FROM ActiveUserProjects", or some other simpel query, I get the same result. If someone has successfully run such a query as a custom SQL query within QC, then the problem lies in the surrounding code. Otherwise there's a problem with QC...
Alcoyotl,
Thanks for a speedy reply. We're really gonna try to help :-)
To make sure I understand what exactly the problem is, let me ask you a few stupid questions:
1) When you copy-paste the EXACT query that you just posted in this thread into Sequel Pro and run it, you get a good result. Is that right?
2) When you do
$strQuery = // exact query, without any modifications whatsoever, that you posted above$objDatabase = QApplication::$Database[1];
$objDbResult = $objDatabase->Query($strQuery);
You get an error on the line with $objDatabase->Query(). Is that right?
If your answer is "yes" to both of these questions, I'm vastly perplexed. Can you post your DDL (CREATE TABLE) statements into pastebin, and I'll try to repro the problem on my box?
Thanks!
I tested a small model similar to that of Alcoyotl and got the same problem.
The simple cod:
<?phpprotected function agregateTest(){
$strQuery = "SELECT SUM(nomenklatura.viewed) AS viewed FROM nomenklatura";
$objDatabase = QApplication::$Database[1];
$objDbResult = $objDatabase->Query($strQuery);
$this->objResult = array();
$objFields = $objDbResult->FetchFields();
while ($objDbRow = $objDbResult->FetchArray()) {
array_push($this->objResult, $objDbRow);
}
}
?>
The result is in this page:
http://avalonbg.com/home/test
The same query in PHPMyAdmin is no problem.
Hello again:
Issue be decided as follows:
<?phpprotected function agregateTest(){
$strQuery = "SELECT SUM(`nomenklatura`.`viewed`) AS viewedsum FROM `nomenklatura`";
$objDatabase = QApplication::$Database[1];
$objDbResult = $objDatabase->Query($strQuery);
// $objFields = $objDbResult->FetchFields();
$this->objResult = array();
while ($objDbRow = $objDbResult->FetchArray()) {
array_push($this->objResult, $objDbRow);
}
}
?>
The line: // $objFields = $objDbResult->FetchFields();
generate the problem.
Result without problem generated there: http://avalonbg.com/home/test
Result from print_r command:
Array ( [0] => Array ( [0] => 375 [viewedsum] => 375 ) )
The result from:
<?phpecho $_CONTROL->objResult[0]['viewedsum'];
?>
Yes wiley, I think you got it. FetchFields is useless there and the Examples page for custom queries goes like this ...
<?php>
// To perform an ad hoc query, simply write out the SQL you want to perform.
$strQuery =
"SELECT
project.name AS project_name,
manager.first_name AS manager_first,
manager.last_name AS manager_last,
(
SELECT
COUNT(*)
FROM
team_member_project_assn
WHERE
project_id = project.id
) AS team_member_count
FROM
project AS project,
person AS manager
WHERE
project.manager_person_id = manager.id
ORDER BY
project.name";
// Call on QApplication to get to the instantiated/active Database Adapter that you want to query
// Be sure to specify the database index (as you defined in configuration.inc.php)
// For purposes of this example, we're assuming that the "Examples" database connection string is defined
// in the DB_CONNECTION_1 constant.
$objDatabase = QApplication::$Database[1];
// Perform the Query
$objDbResult = $objDatabase->Query($strQuery);
// Iterate through the Database Result using ->FetchRow() or ->FetchArray(), as you would if
// you used the a database connector, directly.
while ($mixRow = $objDbResult->FetchArray()) {
_p(sprintf('%s, managed by %s %s (with %s team members)',
$mixRow['project_name'], $mixRow['manager_first'], $mixRow['manager_last'], $mixRow['team_member_count']));
_p('<br/>', false);
}
?>
So no FetchField used ...