Jump to:

3448 Posts in 1062 Topics by 738 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Bug in modeladmin (relation build order)

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 499 Views
  • zard
    Avatar
    Community Member
    23 Posts

    Bug in modeladmin (relation build order) Link to this post

    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

  • zard
    Avatar
    Community Member
    23 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

    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?

  • martimiz
    Avatar
    Forum Moderator
    1078 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

    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...

  • zard
    Avatar
    Community Member
    23 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

    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?

  • martimiz
    Avatar
    Forum Moderator
    1078 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

    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

  • zard
    Avatar
    Community Member
    23 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

    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...

  • martimiz
    Avatar
    Forum Moderator
    1078 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

    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"
    );

  • zard
    Avatar
    Community Member
    23 Posts

    Re: Bug in modeladmin (relation build order) Link to this post

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

    499 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.