A Relationship (I think) [SOLVED]

[cantlep]

Hi There,

I have a table called "Customers". Within that table are various fields. (In this instance, the 2 I'm concerned with are CompanyName and TotalSites) I'd like to auto-populate the TotalSites field with a number. That number should be taken from another table called "SiteData" and depend on how many times the CustomerName is mentioned.

I already have a renderCell function in the "Customers" table listing that means when I click on a CompanyName field, it just lists me the (in this instance) 5 Sites in "SiteData" that have that CompanyName. Now I'd just like another field in the "Customers" table to show me the number of sites in SiteData with that CompanyName.

Do I need a relationship.ini file to make this magic happen?

Thanks

Paul

[shannah]

You probably wouldn't be using a relationships.ini file for this. I'd be looking more towards grafted fields and possibly overriding the xxx__display() method for the grafted field. The basic implementation is simple, however the thing that makes it a little more complex is the fact that you probably don't want to be performing this calculation for every row in every request - so you'll probably want to have some sort of caching strategy.

Here's one such strategy:

1. Create a table called company_stats with columns (company_id, and num_sites)
This table is meant to simply cache calculated data. (of course you could skip this and just add a field called num_sites directly to the company table, but some might not be comfortable mixing columns that are actually part of the company entity with columns that are simply meant to cache statistics).

2. Using the __sql__ directive in the company table, we graft the company_stats table onto it:

Code: Select all

__sql__ = "select c.*, s.num_sites from company c left join company_stats s on c.company_id=s.company_id"


3. Refresh the company_stats table whenever the SiteData table is modified. You could do this simply in an afterSave() trigger on the SiteData table, but this would only be called if updates are made through Xataface... We're going to actually compare the table modification times in the beforeHandleRequest() method and update the company_stats table if the SiteData modification time is newer than the company_stats table (**caveat*** this only works if you are using MYISAM for your table type... INNODB doesn't make modification times available to you).

Code: Select all

function beforeHandleRequest(){
    $modification_times = Dataface_Table::getTableModificationTimes();
    if ( $modification_times['company_stats'] < $modification_times['SiteData'] ){
        $res = mysql_query("replace into company_stats (company_id, num_sites) select company_id, count(*) as num_sites from SiteData group by company_id", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
       
    }

}


[cantlep]

Hi Steve, Thanks very much for the reply. I've tried using your method of a table used purely for caching...I'm clearly doing something wrong though. Here's what I have (slightly different naming convention than you used).

I created a table called "CustomerStats" with 2 fields, CompanyName and TotalSites.

In my tables/Customers/Customers.php I have this

Code: Select all

function __sql__(){
        return "select c.*, s.TotalSites from Customers c left join CustomerStats s on c.CompanyName=s.CompanyName";
}


In my tables/SiteData/SiteData.php I have this

Code: Select all

function beforeHandleRequest(){
    $modification_times = Dataface_Table::getTableModificationTimes();
    if ( $modification_times['CustomerStats'] < $modification_times['SiteData'] ){
        $res = mysql_query("replace into CustomerStats (CompanyName, TotalSites) select CompanyName, count(*) as TotalSites from SiteData group by CompanyName", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
    }

}


Now, in my Customers view, I see the "TotalSites" field listed (it's the first column shown) but it's empty. I created a new entry in "SiteData" so that should have triggered the above function (I guess) but the field is still blank.

Have I been silly?

Thanks

Paul

[cantlep]

I'll just also add that if I manually add data to the CustomerStats table (i.e. an existing Company into the CompanyName field and a number into the TotalSites field) then that number does show up in the TotalSites field in the "Customers" table.

Also, running the mysql query direct on the DB shows this:

Code: Select all

mysql> select 'Newquest Media Group', count(*) as TotalSites from SiteData group by CompanyName;
+----------------------+------------+
| Newquest Media Group | TotalSites |
+----------------------+------------+
| Newquest Media Group |          2 |
| Newquest Media Group |          1 |
| Newquest Media Group |          6 |
| Newquest Media Group |          1 |
| Newquest Media Group |          2 |
| Newquest Media Group |          1 |
+----------------------+------------+
6 rows in set (0.00 sec)


Cheers

Paul

[shannah]

You're close. The beforeHandleRequest() method goes in the application delegate class, not the table delegate class.

[shannah]

On side note, I recommend making sure that you build MySQL indexes on the CompanyName field the 3 tables you mention here. This will drastically increase performance of the join in your __sql__ directive as your database grows large.

[cantlep]

Hi Steve,

Excellent! Works perfectly. Thanks very much for your help.

(I added the indexes as you suggested too - How did you know I didn't have them already? )

Thanks again.

Paul