Hey Guys,

I'm a little stuck on this.

I have a many to many relationship between a ProductCategory class and a Product class.

I have created an extra tab for ProductCategory in the CMS in which I would like to be able to view a dataobjectset of all the products which have been applied specifically to that ProductCategory.

Using the following code, I can see a dataobjectset of products only when they are children of the ProductCategory in the sitetree (but 0 results of any relationships).

$manager = new DataObjectManager(
$this, // Controller
'Products', // Source name
'Product', // Source class
array(
//'Field_1' => 'Field one',
//'Field_2' => 'Field two',
//'Field_3' => 'Field three'
), // Headings
'getCMSFields' // Detail fields (function name or FieldSet object)
// Filter clause
// Sort clause
// Join clause
);
$fields->addFieldToTab("Root.Content.DOM", $manager);

How would I get the dataobject set of the relationship between the ProductCategory and Products instead...?

After achieving this, i'm also aiming to have this list displayed visually by the product image, and integrating the ability to sort the list for each ProductCategory...

Any help would be much appreciated.

I think this question would be better posted in the special DataObjectManager section, as uncle cheese might still be monitoring it.

EDIT: forget this - we are in that section. Must be the fact I've been traveling all over the forums removig spam :(

i'm no DataObjectManager expert, but I think the default DataObjectManager would manage a has_many relation, and in this case that would be the relation between a page and its children.

To manage a many_many relation you might have a look at the other DataObjectManager versions. There is a ManyManyDataObjectManager amongst others, that might work for you.

Martine

Thank you Martimiz - Using a ManyManyDataObjectManager instead has definitely put me on the right track.
I am able to manage the many to many relationship as well as access the applicable Product pages from my tab in a ProductCategories page now.

I'm now working through adding an image thumbnail to the ManyManyDOM, as well as allowing the products to be sorted within each applicable category.

I'm following the articles by Aram on SSbits here:
1) DOM cheatsheet -
http://www.ssbits.com/snippets/2010/a-data-object-manager-cheatsheet/

which gives the following code for sorting a many to many relationship:
//many_many sortable objects, with arg1 being the ParentClass and arg2 the Child relation
SortableDataObject::add_sortable_many_many_relation("Page", "MyDataObjects");

I'm using:
SortableDataObject::add_sortable_many_many_relation("ProductCategory", "Products");

as well as this

2) Adding a Thumbnail to a DataObjectManger -
http://www.ssbits.com/snippets/2009/adding-a-thumbnail-to-a-dataobjectmanager-or-complex-table-field/

Unfortunately for both i'm ending up with a long SQL error (wondering if it's a similar problem affecting both):
For starters here's the full error triggered by adding the sortable code to my _config.php:

[User Error] Couldn't run query: SELECT "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "Product"."Style", "Product"."Price", "Product"."Weight", "Product"."Model", "Product"."FeaturedProduct", "Product"."AllowPurchase", "Product"."InternalItemID", "Product"."NumberSold", "Product".SortOrder AS LocalSort, "Product"."ImageID", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName", CASE WHEN "Product_ProductCategories"."ProductCategoryID" IS NULL THEN '0' ELSE '1' END AS "Checked", COALESCE("Product_ProductCategories"."SortOrder",9999999) AS "MMSort" FROM "SiteTree" LEFT JOIN "Product" ON "Product"."ID" = "SiteTree"."ID" LEFT JOIN "Product_ProductCategories" ON ("SiteTree"."ID" = "ProductID" AND "Product_ProductCategories"."ProductCategoryID" = '10') WHERE ("SiteTree"."ClassName" IN ('Product')) GROUP BY "SiteTree"."ClassName", "SiteTree"."Created", "SiteTree"."LastEdited", "SiteTree"."URLSegment", "SiteTree"."Title", "SiteTree"."MenuTitle", "SiteTree"."Content", "SiteTree"."MetaTitle", "SiteTree"."MetaDescription", "SiteTree"."MetaKeywords", "SiteTree"."ExtraMeta", "SiteTree"."ShowInMenus", "SiteTree"."ShowInSearch", "SiteTree"."HomepageForDomain", "SiteTree"."ProvideComments", "SiteTree"."Sort", "SiteTree"."HasBrokenFile", "SiteTree"."HasBrokenLink", "SiteTree"."Status", "SiteTree"."ReportClass", "SiteTree"."CanViewType", "SiteTree"."CanEditType", "SiteTree"."ToDo", "SiteTree"."Version", "SiteTree"."Priority", "SiteTree"."ParentID", "Product"."Style", "Product"."Price", "Product"."Weight", "Product"."Model", "Product"."FeaturedProduct", "Product"."AllowPurchase", "Product"."InternalItemID", "Product"."NumberSold", "Product"."SortOrder", "Product"."ImageID", "SiteTree"."ID", CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END, CASE WHEN "Product_ProductCategories"."ProductCategoryID" IS NULL THEN '0' ELSE '1' END, COALESCE("Product_ProductCategories"."SortOrder",9999999) ORDER BY "MMSort" ASC, "Checked" DESC, SortOrder LIMIT 0, 10 Unknown column 'Product_ProductCategories.SortOrder' in 'field list'

Any clues...? Again, I definitely appreciate all the help!

It looks like you didn't perform a /dev/build/?flush=1 after adding the new config settings. That should create the SortOrder field that is missing

Hi Martimiz,

Double checked that I had run a /dev/build/?flush=1 after adding the config settings - still the same error unfortunately.

As a quick side note: Running - SortableDataObject::add_sortable_class('Product'); works fine - however the effect of this obviously is that all of the products are sorted universally instead of within a specific ProductCategory as desired.

Regarding: "Unknown column 'Product_ProductCategories.SortOrder' in 'field list'":
Do I possibly need to declare the new SortOrder field somewhere so it exists for this manymany relationship between Products and ProductCategories...

Any other clues / ideas?

Thanks,

Hi Guys,

Here's a bit of an update on this.

I've managed to get the relationship and sorting working on the ProductCategory side of things.
To elaborate: In the CMS - I can click on a ProductCategory Page where I have created a tab with a ManyManyFileDataObjectManager. Note:I've changed from using the ManyManyDataObjectManager to the ManyManyFileDataObjectManager as it better suited what I was looking to accomplish.
In this tab I can see all the products and create the relationship / connection between a product and the current ProductCategory page. I can also sort the related products from here, which then applies only to the active category - great, exactly what i'm after.

This came through playing around with the defined relationship in the Product.php and ProductCategory.php files.
ie:
ProductCategory.php:
"
static $many_many = array(
'Products' => 'Product'
);
"

However I am having trouble on the Product side of things. Ideally I would like to be able to control the very same relationship between a Product and the ProductCategories it belongs to from the Product Page as well.
For the product page I have a tab with a ManyManyComplexTableField to be able to select the applicable ProductCategories.

In Product.php:

protected function getProductCategoriesTable() {

$tableField = new ManyManyComplexTableField(

$this,

'ProductCategories','ProductCategory',

array(
'Title' => 'Category')

);

return $tableField;

}

Adjusting the defined relationship in Product.php here results in different behaviour:

In Product.php

"public static $many_many = array(
'ProductCategories' => 'ProductCategory');"

Results in the desired category list and tick boxes, however the stored relationship data is totally seperate from the data stored in the ProductCategory pages ie: it's using a different table from the database. Obviously I want to be able to reference and manipulate the same table that is being used by the ProductCategory pages.

Changing this to: "public static $belongs_many_many = array('ProductCategories' => 'ProductCategory');"
Results in the query error: "Table 'productcategory_productcategories' doesn't exist" - even after I have run a dev/build/?flush=1

Also because i've defined multiple relationship variations while trialling this (technically creating the associated tables in the database) by running dev/build each time - would this have stuffed things up...

Any clues,

Thanks again,

Any feedback / ideas on this guys?

Thank you,

$many_many defines the relation and triggers the creation of a connecting table in the database. To make sure the other side of the relation uses that same connecting table, you need to use $belongs_many_many.

In Product.php

public static $belongs_many_many = array(
'ProductCategories' => 'ProductCategory'
);