Querying a distant table in Silverstripe model

I'm trying to get a sum of a field "cost" from a page type from another page type. (ProjectCategory), like this:

class ProjectCategory  extends Page {
 static $belongs_many_many = array( 
  'RelateToProject' => 'Project' 
); 

function totalCost(){
    $sqlQuery = new SQLQuery( 
     "SUM(Project.cost)", // Select 
     "Project", // From 
    "what to do here?" // 开发者_运维技巧Where (optional) 
    ); 
    $totalVisits = $sqlQuery->execute()->value();

    return $totalVisits;
}

What do I do for the where bit? How can I get the sum of cost for just this Category? (If I leave the where blank it returns the sum of all project costs for every category - which is no good).

the "where" part should be:

"ID = " . $this->RelateToProjectID

oh, wait, the above will only work für $has_one, but you're using a many_many relationship.

the following should do the trick for your many_many relationship:

build an array of IDs of related projects:

$projects = $this->RelateToProject();
$projectIDs = array();
if($projects) {
  foreach($projects as $project) {
    $projectIDs[] = $project->ID;
  }
}

then use them in your 'where' statement like so:

"ID IN (" . join(',',$projectIDs) . ")"