the code so far goes like this
class Products extends DataObject{
static $db = array(
"CategoryID" => "Int",
"SubCategory" => "Varchar(80)",
"ProductName" => "Varchar(30)",
"Blurb" => "Text",
"Link" => "Varchar(100)"
);
static $has_one = array(
"ProductImage" => "Image"
);
and the query on the Page class goes
$sqlQuery = new SQLQuery();
$sqlQuery->select = array(
"p.*", "f.Filename as Filename", "f.Content as FContent", "f.Title as FTitle",
"@anum := if(@CategoryID = CategoryID, @anum + 1, 1) as row_num",
"@CategoryID := CategoryID as dummy"
);
$sqlQuery->from = array("(SELECT @anum:=0) r,
(SELECT @CategoryID:='') c, Products p left join File f on f.ID = p.ProductImageID");
$result = $sqlQuery->execute();
$Products = new DataObjectSet();
foreach($result as $row) {
$Products->push(new ArrayData($row));
}
Not sure about this "Image" in your suggestion. Is that like "File"?