Hi,

I am using modeladmin to filter on a relation id, as specified here:

class RoomAdmin extends ModelAdmin {
    public static $managed_models = array('Room'); // Can manage multiple models
    static $url_segment = 'roomadmin'; // Linked as /admin/products/
    static $menu_title = 'Room rate admin';
}

And filtering criteria are specified here:

    public static $searchable_fields = array(
        "Name", "Lodge.ID", "Lodge.Destination.ID"
    );

I get correct dropdown fields for both Lodge and Lodge's destination, however, when I try to apply the destination filter, I get the following:

[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "Room"."ID") AS "0" FROM "Room" LEFT JOIN "Lodge" ON "Lodge"."ID" = "Room"."LodgeID" INNER JOIN "PhysicalLocation" ON "Destination"."ID" = "PhysicalLocation"."ID" LEFT JOIN "Destination" ON "Destination"."ID" = "Lodge"."DestinationID" WHERE ("PhysicalLocation"."ID" LIKE '%1%') Table 'rsc1.destination' doesn't exist
GET /admin/roomadmin/Room?q%5BName%5D=&q%5BLodge__ID%5D=&q%5BLodge__Destination__ID%5D=1&action_search=Apply+Filter

Line 580 in .../framework/model/MySQLDatabase.php

Full code can be seen here: https://gist.github.com/petrklus/6781769

I've narrowed it down to two combined errors:
- first of all, destination did not have any extra fields apart from physical location, therefore I've "fixed" it by adding a field and forcing SS to create the table

I am now getting following error:

[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "Room"."ID") AS "0" FROM "Room" LEFT JOIN "Lodge" ON "Lodge"."ID" = "Room"."LodgeID" INNER JOIN "PhysicalLocation" ON "Destination"."ID" = "PhysicalLocation"."ID" LEFT JOIN "Destination" ON "Destination"."ID" = "Lodge"."DestinationID" WHERE ("Destination"."ID" LIKE '%1%') Unknown column 'Destination.ID' in 'on clause'

Let's look at the query:

SELECT DISTINCT count(DISTINCT `Room`.`ID`) AS `0` 
FROM `Room` 
LEFT JOIN `Lodge` ON `Lodge`.`ID` = `Room`.`LodgeID` 
INNER JOIN `PhysicalLocation` ON `Destination`.`ID` = `PhysicalLocation`.`ID` 
LEFT JOIN `Destination` ON `Destination`.`ID` = `Lodge`.`DestinationID` 
WHERE (`Destination`.`ID` LIKE '%1%')

On we can see that the destination ID is being used before its declaration, simple fix to the query and voila, all works:

SELECT DISTINCT count(DISTINCT `Room`.`ID`) AS `0` 
FROM `Room` 
LEFT JOIN `Lodge` ON `Lodge`.`ID` = `Room`.`LodgeID` 
LEFT JOIN `Destination` ON `Destination`.`ID` = `Lodge`.`DestinationID` 
INNER JOIN `PhysicalLocation` ON `Destination`.`ID` = `PhysicalLocation`.`ID` 
WHERE (`Destination`.`ID` LIKE '%1%')

So the problem is that SS first queries the superclass but the relation is connected to the subclass - meaning that the subclass ID is not yet defined by the join.

I believe this is a bug?

I'm not sure but what I think happens is that since both Lodge and Destination extend FysicalLocation, initially there'd be two joins on FysicalLocation. One for Lodge (Lodge.ID = FysicalLocation.ID) and one for Destination (Destination.ID = FysicalLocation.ID).

This could only be done using table aliases, which are not used. So it looks like Silverstripe instead just strips out the first one and uses only the second - but in the wrong location (where the first should be). I suppose technically this is a bug, but it might also be stretching silverstripe a bit far maybe? I don't know...

Thank you very much for you reply!

I agree that this is probably a bit niche case which most people are not going to hit - however, I think at least SS should detect the situation and throw error saying why this state is not supported.

More importantly, how do I work around this problem?

I'm not sure what would be the best approach in your case...

Maybe if Lodge and Destination have a FysicalLocation instead of being (extending) one? Of course that would mean you can't use the FysicalLocation fields so easily in the CMS...

And what if you were to just step away from normalisation and define the FysicalLocation fields in the Lodge and Destination objects themselves? You could then probably drop the FysicalLocation methods altogether. Of course it doesn't seem as nice, and I don't know if Lodges and Destinations would really that often have the same address, and/or if you use queries especially targeting the FysicalLocation object. But it might make things a lot easier and even faster :)

Thank you for the advice!

Before I go on re-structuring my model, do you see any problems with the current one apart from the ModelAdmin issues? The thing is, I am using other queries that rely on getting all PhysicalLocations and only occasionally filtering on the subclass.. which would mean quite a few changes to what I've got at this moment.

Any chance this would get noticed/fixed or that I can provide manual override for some queries? Having a relation on subclasses cannot be that uncommon in general...

You might run into trouble every time you join Lodge to Destination in a query - or use some SilverStripe functionality that does that, like in this case Lodge.Destination.ID

You might try to replace that with your own function (haven't tested, so please check). Something like:

public static $searchable_fields = array(
	"Name", "Lodge.ID", "LodgeDestinationID"
);

public function LodgeDestinationID() {
	$lodge = $this->Lodge();
	return $lodge->Destination()->ID;
}

[EDIT2] or if say, you wanted the destination name

public function LodgeDestinationID() {
	$destinationID = $this->Lodge()->DestinationID;
	return Destination::get()->byID($destinationID)->Name;
}

[EDIT] or in this case even, if it's actually just the ID you want

public static $searchable_fields = array(
	"Name", "Lodge.ID", "Lodge.DestinationID"
);

Thank you very much! I will put those in the next iteration and see if it helps..