Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

$searchable_fields should support dot notation for has one relations #2475

Closed
petrklus opened this Issue · 7 comments

5 participants

@petrklus

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';
    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 ("Destination"."ID" LIKE '%1%') Unknown column 'Destination.ID' in 'on clause'

Looking at the query in more detail:

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%')

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

Forum thread discussing the issue: http://www.silverstripe.org/data-model-questions/show/25177#post324608#post324608

I am using the latest version before 3.1.

@petrklus

... am I missing any required fields; can this be at least acknowledged? I would like to know the position of the SS team on this issue..

@ajshort

Try using Lodge.DestinationID instead of Lodge.Destination.ID.

@chillu
Owner

I think this needs to be fixed regardless of the workaround, the point of the new ORM is that you have to worry less about arbitrary distinctions like using the foreign key rather than dot-notation on relation names.

@petrklus

The Lodge.DestinationID seems to be working - however, I still agree that this should be fixed. I think the bug can creep up in other parts of the system too and may not be as easy to fix.

@ajshort

Agree, just providing a workaround:)

@petrklus

.... so, is this a right procedure to report a bug; just to simply list it here? Is it going to get picked up by the team?

@kinglozzer
Collaborator

Just tested this on the 3.1 branch, seems to be fixed

@simonwelsh simonwelsh closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.