Hi all,

I need to export records from a child entity (say Product) including all parent records (say Category), and coming from a java background with a full blown ORM (Hibernate), I was wondering how to do that.
AFAIK, the DataObject::get method does not include all fields of the parent relations but can only filter on a parent. Here's a way to retrieve all those records including all parents, where I "only" need one sql query for all children, and one per parent relationship.
The intention is to make use of the dataobject model, so I wanted all records to be "converted" to DataObject instances before serializing them to the export (in JSON).
Is this ok or is there a better, shorter or more reliable way in SilverStripe?

Best regards,
Ronald

		$childDataObjSet = DataObject::get($this->entityName, '', 'ID ASC');
		// If there are any records, process them
		if($childDataObjSet) {
			// We need the first instance to find all parent relationships,
			// ie all relationships defined for this entity as has_one
			$firstRecord = $childDataObjSet->First();
			$listOfParents = $firstRecord->has_one(); 
			
			// Create an index with per parent entity all ID's
			$parents = array();
			foreach($listOfParents as $parentName => $key) {
				$fieldName = $parentName.'ID';
				foreach ($childDataObjSet as $record) {
					$parents[$parentName][] = $record->getField($fieldName);
				}
			}
			
			// Load all parents
			foreach($parents as $parentName => $key) {
				// Fetch all parents with a WHERE IN (...) database query
				$parentObjDataSet = DataObject::get($parentName, $parentName.'.ID in ('.join(',',$parents[$parentName]).')');
				
				// Create an array index where the key is the parent ID and the value is the parent record
				$parentIndex = array();
				foreach ($parentObjDataSet as $key => $parentRecord) {
					$parentIndex[$parentRecord->ID] = $parentRecord;
				}
				
				// Loop through all children and assign the right parent record to each child
				$parentIDFieldName = $parentName.'ID';
				foreach ($childDataObjSet as $key => $child) {
					$child->setComponent($parentName, $parentIndex[$child->$parentIDFieldName]);
				}
			}
			
			// Now we have a $childDataObjSet with all parent entities neatly inserted in the DataObject's components property
		}

You could also try adding virtual properties if the parent object is simple
http://doc.silverstripe.org/sapphire/en/topics/datamodel#overloading
or a custom relation getter
http://doc.silverstripe.org/sapphire/en/topics/datamodel#custom-relation-getters
http://doc.silverstripe.org/sapphire/en/topics/datamodel?s#whats-the-difference-between-dataobject-get-and-a-relation-getter

Can you explain a bit more about your business requirements -- I was a bit surprised that you made Products children of Categories. While this might seem like the typical relationship when you look at many merchant websites, behind the scenes I'm not sure that's the actual shape of the data model.

It seems like a Category would be best implemented as an independent property in a related table, or even perhaps (in a primitive form) an enum owned by the Product class definition. After landing on a category, getting related products would be a DataObjectSet query looking for the property Category=?.... giving the appearance to the user that the products are "inside" the category, but without locking your data model into that hierarchy.

For example, the model you cite breaks down as soon as you desire to have a product be a member of more than one category.

Sorry to answer a question with a question, but I was just worried that continuing down this path will only lead to more odd data modeling difficulties later on.

Hi,

Thanks for your reply, posting another question is no problem :)
For my project, I need to extend SilverStripe with a lot of model classes for my business needs. These classes are related using normal one-to-many, many-to-one and many-to-many relationships. Nothing fancy here.

You're right if you say that a Category-Product relationship might be a many-to-many relationship, but in my case it was just an example of a one-to-many relationship. Actually, I was describing the Product-Category as a many-to-one relationship (child->parent).
You could also view it as a User-Role type relationship, where each user has precisely one role.

For my project I want to be able to maintain the business-model entities on mobile platforms, and my dataformat of choice is JSON. Therefore, I want to convert all my queries to JSON. When I retrieve a list of products, I want to display the name of the category, as in:

Product.ID  Product.Name   Category.Name

As I have Edit in place (Editing records in the grid without a detail screen popping up), I also need the Category.ID which is a hidden field so the category name can be selected in a dropcombo in the gridcell.
As a java programmer, I'm used to performing a query and retrieving all records as objects with the Hibernate ORM. It's easy to perform a single query, include multiple joins and return a complete object graph.

If I retrieve 30 products, I want the category.ID and the category.Name be retrieved, preferably in one single query, as in:

SELECT Product.*, Category.ID, Category.Name from Product LEFT JOIN Category where Product.CategoryID = Category.ID

The JSON output then should look like:

[{
   ID: 1,
   Name: 'First product',
   Category: {
      ID: 123,
      Name: 'Category 123'
   }
}, {
  ... More products
}]

Here, the category is a nested object in each product.

AFAIK, the DataObject::get method can filter and sort on parent fields (The Category.ID and Category.Name fields), but does not include them in the object graph that's returned. And that's what I'm after.

Hope this clarifies my question :)

Ronaldo