Hi Guy, this one has been giving me headaches since before Christmas -- hope somebody out there can point me in the right direction

We are upgrading a site we wrote a couple of years ago in SS2.4.7 to SS3.1

All is working fine except some of the more complex reports which, in ver2, I had written by overloading SS_Report::sourceQuery(). This returns the SQLQuery object used to define the report contents -- code below

SS2 code

		public function sourceQuery($params) {
			$sql = new SQLQuery();
			
			$fields = array (
				'stVehicleBooking.ClassName as ClassName',			// must include the class name, even if we are not using it
				'stVehicleBooking.Status',
				//'year(stVehicleBooking.StartDate) as StartYear',
				'count(stVehicleBooking.ClassName) as NumberOfBookings',
				'sum(stQuote.TotalPrice) as TotalPrice',
				'sum(stQuote.Price) as TotalOwed',
				'sum(stQuote.AmountPaid) as TotalPaid',
				'sum(stQuote.PriceAirportTransferInbound + stQuote.PriceAirportTransferOutbound + stQuote.PriceOutdoorChairs + stQuote.PriceSnowChains + stQuote.PriceGenerator + stQuote.PriceSecondGasBottle + stQuote.PriceStarterPack + stQuote.PriceCaravanClub + stQuote.PriceGasEasyRefill + stQuote.PriceFuelEasyRefill + stQuote.PriceSatNav) as TotalTreats',
				'sum(stQuote.PriceAirportTransferInbound) as TotalInboundTransfers',
				'sum(stQuote.PriceAirportTransferOutbound) as TotalOutboundTransfers',
				'sum(stQuote.PriceOutdoorChairs) as TotalOutdoorChairs',
				'sum(stQuote.PriceSnowChains) as TotalSnowChains',
				'sum(stQuote.PriceGenerator) as TotalGenerator',
				'sum(stQuote.PriceSecondGasBottle) as TotalSecondGasBottle',
				'sum(stQuote.PriceStarterPack) as TotalStarterPack',
				'sum(stQuote.PriceCaravanClub) as TotalCaravanClub',
				'sum(stQuote.PriceGasEasyRefill) as TotalGasEasyRefill',
				'sum(stQuote.PriceFuelEasyRefill) as TotalFuelEasyRefill',
				'sum(stQuote.PriceSatNav) as TotalSatNav'
			);
			
			if (isset($params['ReportGrouping']) && !empty($params['ReportGrouping'])) {
				switch ($params['ReportGrouping']) {
					case 'Year':
						$fields[] = 'year(stVehicleBooking.StartDate) as StartYear';
						break;
					case 'YearAndMonth':
						$fields[] = "concat_ws('-', monthname(stVehicleBooking.StartDate), year(stVehicleBooking.StartDate)) as StartYear";
						break;
					case 'YearAndQtr':
						$fields[] = "concat_ws('-', year(stVehicleBooking.StartDate), 'Qtr', quarter(stVehicleBooking.StartDate)) as StartYear";
						break;
					default:
						$fields[] = 'stVehicleBooking.StartDate as StartYear';
				}
			} else
				$fields[] = 'stVehicleBooking.StartDate as StartYear';
			
			$sql->select = $fields;
			
			$sql->from = array (
				'stVehicleBooking',
				'left join stQuote on stQuote.ID = stVehicleBooking.QuoteID'
			);
			
			//$sql->where = array ( 'stVehicleBooking.Status = "Booking"' );
			
			$sql->groupby = array ( 'StartYear' );
			
			$sql->orderby = 'stVehicleBooking.StartDate';
			
			return $sql;
		}

which all worked perfectly.
(please note the SQL sum expressions on the joined table)

Now, in SS3 reports are defined by overloading SS_Report::sourceRecords() which returns an SS_Sortable list -- seems simple enough.

For the simpler reports this can be handled using by using MyDataObject::get()

It's the more complex ones (like the above example) that are giving me problems. These need to to be selected using SQL because MyDataObject::get()->leftJoin() does not return data from the joined table

So try: build the SQLQuery object, run the query and return the resulting list

SS3 code

		public function sourceRecords($params, $sort, $limit) {
			$sql = new SQLQuery();
			
			$fields = array (
				'NumberOfBookings'			=> 'count(stVehicleBooking.ClassName)',
				'TotalPrice'				=> 'sum(stQuote.TotalPrice)',
				'TotalOwed'					=> 'sum(stQuote.Price)',
				'TotalPaid'					=> 'sum(stQuote.AmountPaid)',
				'TotalTreats'				=> 'sum(stQuote.PriceAirportTransferInbound + stQuote.PriceAirportTransferOutbound + stQuote.PriceOutdoorChairs + stQuote.PriceSnowChains + stQuote.PriceGenerator + stQuote.PriceSecondGasBottle + stQuote.PriceStarterPack + stQuote.PriceCaravanClub + stQuote.PriceGasEasyRefill + stQuote.PriceFuelEasyRefill + stQuote.PriceSatNav)',
				'TotalInboundTransfers'		=> 'sum(stQuote.PriceAirportTransferInbound)',
				'TotalOutboundTransfers'	=> 'sum(stQuote.PriceAirportTransferOutbound)',
				'TotalOutdoorChairs'		=> 'sum(stQuote.PriceOutdoorChairs)',
				'TotalSnowChains'			=> 'sum(stQuote.PriceSnowChains)',
				'TotalGenerator'			=> 'sum(stQuote.PriceGenerator)',
				'TotalSecondGasBottle'		=> 'sum(stQuote.PriceSecondGasBottle)',
				'TotalStarterPack'			=> 'sum(stQuote.PriceStarterPack)',
				'TotalCaravanClub'			=> 'sum(stQuote.PriceCaravanClub)',
				'TotalGasEasyRefill'		=> 'sum(stQuote.PriceGasEasyRefill)',
				'TotalFuelEasyRefill'		=> 'sum(stQuote.PriceFuelEasyRefill)',
				'TotalSatNav'				=> 'sum(stQuote.PriceSatNav)'
			);
			
			if (isset($params['ReportGrouping']) && !empty($params['ReportGrouping'])) {
				switch ($params['ReportGrouping']) {
					case 'Year':
						$fields['StartYear'] = 'year(stVehicleBooking.StartDate)';
						break;
					case 'YearAndMonth':
						$fields['StartYear'] = "concat_ws('-', monthname(stVehicleBooking.StartDate), year(stVehicleBooking.StartDate))";
						break;
					case 'YearAndQtr':
						$fields['StartYear'] = "concat_ws('-', year(stVehicleBooking.StartDate), 'Qtr', quarter(stVehicleBooking.StartDate))";
						break;
					default:
						$fields['StartYear'] = 'stVehicleBooking.StartDate';
				}
			} else
				$fields['StartYear'] = 'stVehicleBooking.StartDate';
			
			foreach ($fields as $name => $field)
				$sql->selectField ($field, $name);
			
			$sql->setFrom		('stVehicleBooking')
				->addLeftJoin	('stQuote', 'stQuote.ID = stVehicleBooking.QuoteID')
				->setGroupBy	('StartYear')
				->setOrderBy	('stVehicleBooking.StartDate');
			
			$results = $sql->execute();
			
			$list = ArrayList::create();
			//$list = DataList::create('stVehicleBooking');
			if ($results->numRecords() > 0)
				foreach ($results as $item)
					$list->add (new ArrayData ($item));
			
			return $list;
		}

This does not seem to work

Returning the results of the query directly (return $results) gives an error (Gridfield expects a SS_Sortable list) -- not particularly surprising but never hurts to try the obvious

However, the SQL does work correctly (I have confirmed this by looping through the $results record set and echoing a couple of the fields)

Creating a DataList and returning that simply produces a list of blank records

Creating an ArrayList returns the the correct dataset but SS throws an uncaught exception error (Object->__call(): the method 'canview' does not exist on 'ArrayData')

So, the big question is

What am I missing here?

Is there any way of converting the results of an SQLQuery() object into a data list that the report generator will understand?

OR

Is there a way of calling MyDataObject::get()->leftJoin() that will return the contents of the joined table?

It looks like the GridField just really wants to work with DataObjects - hence the canView problem. I've been able to work around it by creating an ArrayData extension like this:

class Report_ArrayData extends ArrayData {
	public function canView($member = null) {
		return true;           //<-- hmm, check some permission
	}
} 

And then in sourceRecords:

		...
		$sqlResult = $sqlQuery->execute();
		$returnSet = new ArrayList();
		foreach($sqlResult as $result) {
			$returnSet->push(new Report_ArrayData($result));
		}
		return $returnSet;

OK, it feels like a hack, but... the ArrayData extension can be made as safe as any DataObject. You could have it call the canView() method of the main DataObject (by using a singleton). So if that really is the only problem (I've found no other yet), this might not be such a bad hack :)

Martine

Thanks Martine, that worked great

The following should work as well:

foreach($sqlResult as $result) {
    $returnSet->push(new DataObject($result)); 
}