Sub set totals

[njw]

I am classifying records by location, a field on the main data record. Is there an easy way for me to display the number of records in each location in the list and detail views for the locations table? In other words include a calculated field on the locations record that totals all the records in a different table which have a location matching that of the displayed record. (If that makes any sense!)

I'm not much of a php programmer so I really am looking for a simple method please!

Many thanks

Neil

[Aoirthoir]

I am not sure exactly how to do this, but the right direction might be a simple dataface sql call (you know all those __sql__ = "statement")

SQL has a COUNT command. Quoting from MySQL in a nutshell on safari.oreilly.com you can do the following:

SELECT COUNT(*)
FROM books, authors
WHERE author_last = 'Tolstoy'
AND author_id = authors.rec_id;

So to modify the SQL let us say your table is Customer.

SELECT COUNT(*)
FROM Customer
WHERE City = 'Houston';

Or with a variable, instead of city name in text. (Ok that part I know yet...Id have to look it up in a book. In any case this simple SQL statement will save you loads and loads of time. You could even move it to being a STORED PROCEDURE in MySQL (dont know enough about those to help..yet....soon I will). Instead of having to read the records into PHP, loop through them, verify the field values, exit the loop etc..one simple sweet statement. Hope this sends you in the right direction bro.

[njw]

Thanks Aoirthoir. I suppose my real question is how to integrate it into the Dataface output, without having to amend the Dataface code too much. I prefer to leave the code as close to what Steve produces as I can to give me less work after upgrades! It would be good to have a calculated field defined in MySQL to do all this, but I suspect that my version of MySQL (4.0.27) won't support anything like that.

I'll keep looking at the code!

[Aoirthoir]

The code can be done directly in dataface..you just use the dataface __sql__ = commands. As I learn the code better I can show you this.

Basically though, the idea here I'm presenting is, move as much of the data logic stuff to SQL. Then if dataface updates you wont have to make changes. Also php is slower for the things that SQL can do already. In fact you should see in the tutorials about the relationships, Mr. Steve says that we can do this or that SQL statement, or the dataface equivelant, which will be converted to dataface itself.

The part I dont know how to do just yet, is to display that specific field in dataface. It is probably part of the dataface smarty templates. In fact thinking about it as I write this I have a couple Ideas. I will experiment with them and get back to you on it.

[shannah]

Yes.. currently there is no easy way to do this, other than overriding the list view and view tab templates.

I will be releasing 0.6.3 later today that will allow you to specify an alternate SQL select query for a table. This would allow you to graft on as many calculated fields as you want.

However this requires subselects, which are available in MySQL 4.1, but I'm not sure about 4.0

-Steve

[shannah]

I have added this functionality in 0.6.3 and created a small how-to on this topic:
http://framework.weblite.ca/documentation/how-to/how-to-add-calculated-fields-to-your-list-and-details-view

[njw]

Many thanks Steve. I have yet to install and implement, but the following SQL code seems to run ok on MySQL 4.0 and appears to give the result I need: All the Locations listed with a count of the opportunities at each location.

SELECT a. * , COUNT( b.OpportunityId ) AS Number
FROM Locations AS a
LEFT JOIN Opportunities AS b ON a.LocationsId = b.LocationId
GROUP BY b.LocationId

Thanks again

Neil

[njw]

Steve

I've tried to implement the above SQL for the locations table and I get this set of errors:

Warning: Illegal offset type in /home/qsgi2ec/public_html/dataface0p6/Dataface/Table.php on line 1493

Fatal error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Locations.* , COUNT( Opportunities.OpportunityId ) FROM SELECT COUNT(*) FROM (SELECT Locations.* , COUNT( Opportunities.OpportunityId ) FROM Locations LEFT JOIN Opportunities ON Locations.LocationsId = Opportunities.LocationId GROUP BY Locations.LocationId) as `Locations`On line 99 of file /home/qsgi2ec/public_html/dataface0p6/Dataface/QueryTool.php in function printstacktrace()
On line 423 of file /home/qsgi2ec/public_html/dataface0p6/Dataface/QueryTool.php in function dataface_querytool(Locations,Resource id #10,00310f0594b02adeaa75cae45b891fd2-en-Locations-list-0-0-30-list)
On line 694 of file /home/qsgi2ec/public_html/dataface0p6/Dataface/Application.php in function loadresult(Locations,Resource id #10,00310f0594b02adeaa75cae45b891fd2-en-Locations-list-0-0-30-list)
On line 789 of file /home/qsgi2ec/public_html/dataface0p6/Dataface/Application.php in fun in /home/qsgi2ec/public_html/dataface0p6/Dataface/QueryTool.php on line 99

The SQL runs fine when I run it under phpAdmin.

Any thoughts?

Many thanks

Neil

[shannah]

Hi Neil,

When you say the SQL runs fine in phpAdmin, does that mean that the query:

SELECT Locations.* , COUNT( Opportunities.OpportunityId ) FROM Locations LEFT JOIN Opportunities ON Locations.LocationsId = Opportunities.LocationId GROUP BY Locations.LocationId

runs fine?

or does it mean the query:

SELECT Locations.* , COUNT( Opportunities.OpportunityId ) FROM SELECT COUNT(*) FROM (SELECT Locations.* , COUNT( Opportunities.OpportunityId ) FROM Locations LEFT JOIN Opportunities ON Locations.LocationsId = Opportunities.LocationId GROUP BY Locations.LocationId) as `Locations`

Runs fine?

Dataface automatically uses your __sql__ as a subquery to form the altered query... It was the easiest way I could think of doing this functionality... this requires subquery support.

-Steve

[njw]

The first is the one that runs fine. If you are coverting it into the second, I understand why I have the problem - my ISP supplies me with MySQL 4.0.27 which doesn't support sub queries.

Do you have to create the sub query, as then we will have available any functionality our local version of MySQL will support?

[shannah]

Unfortunately the only way that i could think of offering this functionality that would be consistent was to do it with a subquery. This is because dataface needs to be able to pick and choose the columns that it selects from a given table. It also needs to be able to add columns to the request to obtain meta data. Subqueries allowed me to replace the table name with an entire subquery which required little to no ripple effects throughout the rest of the framework.

To do this without subqueries would have required a lot of work to the framework as a whole. I am open to suggestions on this ...

Best regards

Steve

[njw]

There's no way I can offer suggestions at that level!!!

I was taking a simpler view, which may be totally erroneous. I thought you were just replacing the "SELECT table.* FROM table" with the __SQL__ statement in the conf.ini.

If you were, I thought that would just be a simple substitution?

As I said, I have no real understanding ...

Thanks a lot for your help

Neil

[njw]

Steve

Can you point me to the code where the change needs to be made please? I only want to change the List View by adding an additional field to the end, calculated by executing a SQL commend within the php code. I've been going backwards and forwards through the code, but can't find the correct place.

Many thanks

Neil

[shannah]

Hi Neil,

I cannot thing of an easy way to just graft a column onto the list view without really digging in.
The best way to achieve a different list would be to override the result_list slot in your delegate class:

class tables_Locations {

function block__result_list(){
$res = df_query("SELECT Locations.* , COUNT( Opportunities.OpportunityId ) FROM Locations LEFT JOIN Opportunities ON Locations.LocationsId = Opportunities.LocationId GROUP BY Locations.LocationId");

// print your table header stuff here ...

while ( $row = mysql_fetch_assoc($res) ){
$record = new Dataface_Record('Locations', $row);
$url = $record->getURL(); // the link to the record for this row

// print your row here ....
}

// print your table footer here
}
}

If you want the html table to match the look and feel of the rest of the site, then you can make it class="listing"

Hope this helps a little

-Steve

[njw]

Thanks Steve. I'll let you know. Sorry to be a pain.

Neil