Page 1 of 1

PostPosted: Wed Nov 29, 2006 1:53 pm
by roginald
Hey everybody,

Was wondering about the following scenario. I have a main "projects" table along with several supporting tables that require linking tables due to their relationships. What would be the optimal method for building a list grid that displayed all of the data when these tables are joined?

I followed Steve's instructions for including some related table data in a custom side bar view, which shows up on the detials pages. Is there an easy way within the framework to simply add these columns to the main grid output?

Also, does anyone know of a method to hide fields in the ajax drop down on the detials pages?


Thanks for any help.

PostPosted: Fri Dec 01, 2006 1:54 pm
by shannah
Hi Roland,

Sorry for the delay in reply.

Personally, I think the best method for building a list grid that displays all of the data when the tables are joined is to create a custom action that performs the query and displays the results that you want.

You can use the Dataface_Record grid class to help you to output the results in a grid with the same style as the list view:

Code: Select all
$app =& Dataface_Application::getInstance();
      
      $sql = "
         select
            l.username,
            hours_pending,
            hours_approved,
            hours_rejected,
            hours_paid,
            balance_payable,
            paid_to_date
         from
            users l
            
         left join
            (select username, sum(hours_declared) as hours_pending from log_entry where status='pending' group by username) as hp
            on l.username=hp.username
         left join
            (select username, sum(hours_declared) as hours_approved from log_entry where status='approved' group by username) as ha
            on l.username=ha.username
         left join
            (select username, sum(hours_declared) as hours_rejected from log_entry where status='rejected' group by username) as hr
            on l.username=hr.username
         left join
            (select username, sum(hours_declared) as hours_paid from log_entry where status='paid' group by username) as hpaid
            on l.username=hpaid.username
         left join
            (select username, sum(ifnull(flat_rate,hourly_rate*hours_declared)) as balance_payable from log_entry where status='approved' group by username) as hbp
            on l.username=hbp.username
         left join
            (select username, sum(ifnull(flat_rate,hourly_rate*hours_declared)) as paid_to_date from log_entry where status='paid' group by username) as hptd
            on l.username=hptd.username
            ";
         
      
      $res = mysql_query($sql, $app->db());
      if ( !$res ) trigger_error(mysql_error($app->db()));
      $data = array();
      while ( $row = mysql_fetch_assoc($res) ){
         $data[] = $row;
      }
      
      import('Dataface/SkinTool.php');
      import('Dataface/RecordGrid.php');
      $grid = new Dataface_RecordGrid($data);
      echo $grid->toHtml();


(I just quickly copied that snippet from a custom action in another app of mine so you may have to sift through to make sense. The thing to note is in the last few lines where I use the Dataface_RecordGrid object to display a grid.

As for hiding fields from the ajax drop-down pages, this is something that should be available but is not currently.. In general you should be able to use permissions or set visibility:browse = hidden in your fields.ini file to achieve this result.

When I get a chance i'll post the changes that will allow this to work.

-Steve