Hi all

I'm trying to select data for a query from two tables, and get it into a DataObject one way or another, so that it can then be used in a TableListField. I've spent a LOT of time trawling through documentation and code for the core files, and I can't get it to work.

I can do a DB::query to get the data, but then it's not in a DataObject, and it's not the best way of doing it, I know.

For views, do I need to create a DB table (like I do for proper tables) before it's queryable?

Any help would be much appreciated, it's driving me up the wall.

Thanks.

Current code:

# Either this
		$CT = DataObject::Get('casetrackerview');

# Or this
		$report = new TableListField(
			'CorporateReport',
			'CaseTracker',
			array(
				'ID' => 'ID'
			)
		);
# Is preferred for getting data, either way it needs to go into the TableListField

		$report->setPermissions(array(
			'export',
			'delete',
			'print'
		));

			$fields = new FieldSet(
				new HiddenField('ID', 'ID'),
				$report
			);

			$actions = new FieldSet();

			return new Form($this, "EditForm", $fields, $actions);

DB Declarations:

class CaseTracker extends DataObject {
	static $db = array(
		'CaseName' => 'Varchar',
		'DateBirth' => 'Date',
		'TelNumber' => 'Int',
		'Address' => 'Text',
		'Postcode' => 'Varchar(8)'
	);

	static $has_one = array(
		'Status' => 'CaseTrackerStatus'
	);

class CaseTrackerStatus extends DataObject {
	static $db = array(
		'ShortText' => 'Varchar',
		'AllText' => 'Varchar(100)',
		'EmailData' => 'HTMLText'
	);

	static $has_many = array(
		'Statuses' => 'CaseTracker'
	);
 
}

Just to close off the topic, I got this working eventually.

		$sqlQuery = new SQLQuery();
		$sqlQuery->select = array('T1.C1, T1.C2, T1.C3, CONCAT(T1.C4, ". ", T2.C5) AS L1');
		$sqlQuery->from = array('T1INNER JOIN T2ON T1.C1= T2.C1');
		$sqlQuery->where = array();
		$sqlQuery->orderby = '';
		$sqlQuery->groupby = array();
  
		$allX = $sqlQuery->execute();
		$Xx= new DataObjectSet();
		foreach ($allX as $X) {	$Xx->push( new DataObject( $X));	}

		$XTLF= new TableListField(
			'Name',
			'T1',
			array(
				'C1' => 'H1',
				'C2' => 'H2',
				'C3' => 'H3'
			)
		);

		$XTLF->setCustomSourceItems($Xx);

Just adding something else on this for anyone who may stumble on this later - I think I found the proper way of joining.

Posted a comment on the tutorial for creating modules: http://doc.silverstripe.com/doku.php?id=private:tutorial:creating-a-module&disqus_reply=3662588#comment-3662588