Loading...
 
Skip to main content

Features / Usability


PluginDBreport error

I'm trying to use the DBreport plugin and I keep getting an error that doesn't make sense. Here is the code:

Copy to clipboard
{DBREPORT(db=>"tracks",debug=>true)} SQL { SELECT `projects`.`name`,`projects`.`description`,`projects`.`state`,`todos`.`description`,`todos`.`due`,`todos`.`state` FROM `projects`,`todos` WHERE `todos`.`project_id` = `projects`.`id` } GROUP [projects.name] "Project name:[name]" :H1 {DBREPORT}


The error is "The Field 'projects.name' was not returned by the SQL query." It has something to do with the GROUP line. When I delete it, it works, but without the grouping. I don't think it has to do with the quotes. Even if I use the single quotes

Copy to clipboard
GROUP [`projects`.`name`] "Project name:[name]" :H1


it still gives the error.

I have also uncommented the adodb line in /db/local.php and that doesn't help. BTW, it that still necessary? The docs for PluginSQL say it was fixed in v5.0; does that fix also apply to PluginDBreport?

I'm using PluginDBreport because I need more formatting than PluginSQL gives. I haven't tried downloading the WikiPluginsDB stuff yet.

I found that if I just use the field name without the table name it works. However, I don't know how this will distinguish between fields with the same name in different tables. Example:

Copy to clipboard
GROUP [name] "Project name:[name]" :H1

Ok, I think this is the solution. The SQL command returns the column/field names as they exist in their respective tables. If you have two fields with the same name in two different tables, they will both have the same name in the returned data. The trick is to use the the AS parameter in the SELECT statement. This allows you to rename the field to something unique.

Copy to clipboard
SQL { SELECT `projects`.`name` AS `project name`,`projects`.`description`,`projects`.`state`, `todos`.`name`,`todos`.`description`,`todos`.`due`,`todos`.`state` FROM `projects`,`todos` WHERE `todos`.`project_id` = `projects`.`id` } GROUP [project name] "[project name]":H1

Here I have told it to return the field projects.name as "project name" instead of "name" so it is not confused with todos.name, which would also have been returned as "name".
Then, in the GROUP parameter, I can use "project name" instead of "name".


There still seems to be a bug this plugin. According to the documentation page I'm supposed to be able to use both field names and text in the report. The format is:

Copy to clipboard
"Some text [fieldname] more text"
However, the plugin only uses the last item in the quotes. In this example, I only get "more text". If I use the string
Copy to clipboard
"Some text [fieldname]"
then I get only the field contents. I don't get the text before it. If I use
Copy to clipboard
"Some text"
then I get "Some text " like I should.

DUH .. roll back ..

-pete