Calculated Fields in a “phantom” table

Archived from the Xataface Users forum.

rugcutter — Tue Jun 29, 2010 11:02 am

I’d like to make a tab that is a read-only view of a “phantom” table that simply exposes calculated fields.

My attempt is to make a folder named “calculated_fields” underneath the “tables” folder. The fields.ini would contain a custom SQL that would calculate the values for given columns, and may join to other tables as a source of for those calculations. That fields.ini might look something like this:

Code: Select all

`sql = “select
(1+1) as cf_days_used_to_date,
(2+4) as cf_days_remaining,
(3+5) as cf_budget_used_to_date,
(4+2) as cf_budget_remaining”

[cf_project_details_id]
widget:label = “Project Details ID”
order=1

[cf_days_used_to_date]
widget:label = “Days Used to Date”
order=2

[cf_days_remaining]
widget:label = “Days Remaining”
order=3

[cf_budget_used_to_date]
widget:label = “Budget Used to Date”
order=4

[cf_budget_remaining]
widget:label = “Budget Remaining”
order=5`

In theory I would think this would work but Xataface expects an actual database table underneath it, whereas my configuration is simply a “phantom” table. I get this error:

Code: Select all

Fatal error: Error performing mysql query to get column information from table 'cf_calculated_fields'. The mysql error returned was : 'Table 'gs_project_dashboard_dev.cf_calculated_fields' doesn't exist'. On line 477 of file C:\public_html\xataface-1.2.2\Dataface\Table.php in function printStackTrace() On line 2348 of file C:\public_html\xataface-1.2.2\Dataface\Table.php in function Dataface_Table(cf_calculated_fields,Resource id #19,) On line 413 of file C:\public_html\xataface-1.2.2\Dataface\Relationship.php in function loadTable(cf_calculated_fields,Resource id #19) On line 370 of file C:\public_html\xataface-1.2.2\Dataface\Relationship.php in function _normalizeColumns() On line 105 of file C:\public_html\xataface-1.2.2\Dataface\Relationship.php in function _init(array($pt_project_details_id,1,0,0)) On line 1559 of file C:\public_html\xataface-1.2.2\Dataface\Table.php in function Dataface_Relationship(project_details,CalculatedFields,array($pt_project_details_id,1,0,0)) On line 3097 of fil in C:\public_html\xataface-1.2.2\Dataface\Table.php on line 477

I would prefer to keep the logic in Xataface (in fields.ini) vs. creating a view in the database.

Is there a way around this, to do what I am trying to accomplish?

---

shannah — Mon Jul 05, 2010 4:39 pm

Currently the Xataface SQL parser doesn’t support arithmetic (i..e 1+2 1*2). I’m not sure when I’ll be able to get around to adding this. Using a view is one workaround.

---

cantlep — Tue Jul 06, 2010 2:57 am

Hi,

Arithmetic sort of works, depending on how it’s done. I’ve used it fine in table delegate classes (the webserver logs *will* error) but the sums are still calculated correctly. An example from my BillData/BillData.php

Code: Select all

`// Allow the StandingChargeUnits column to estimate days - Column does NOT exist in DB
// Allow the VAT to be calculated - As above
//Allow Bill Total to be calculated - And again

function sql(){
        return “select t., datediff(t.SupplyPeriodEnd,t.SupplyPeriodStart) as StandingChargeUnits, t.TotalCostExVATt.MPAN1LowerVATPerc/(100)t.LowBillDataVATRates+t.TotalCostExVATt.MPAN1HigherVATPerc/(100)t.HighBillDataVATRates as VAT, t.TotalCostExVATt.MPAN1LowerVATPerc/(100)t.LowBillDataVATRates+t.TotalCostExVATt.MPAN1HigherVATPerc/(100)*t.HighBillDataVATRates + t.TotalCostExVAT AS BillTotal from BillData t”;
}`

Maybe that will help?