Has anyone noticed that because Silverstripe creates two tables for each object, Live and stage, that sometimes it's impossible to get a query to work in both environments? Take this for example:
return DataObject::get("KeynoteSpeaker", "KeynoteSpeaker.ID NOT IN (". implode(",",$ids) . ")", 'Title ASC');
Results in an error, because the KeynoteSpeaker.ID is an unknown field in the WHERE clause, because the ORM is pulling from the _Live tables (SiteTree_Live, KeynoteSpeaker_Live, etc)
Of course, if I just specify the field as ID, it results in an ambiguous column error.
So, I append "_Live" to the WHERE clause.
return DataObject::get("KeynoteSpeaker", "KeynoteSpeaker_Live.ID NOT IN (". implode(",",$ids) . ")", 'Title ASC');
Fine, but when a user is logged in, or looking at the Stage environment, this results in an error as well, because the ORM is pulling from the non "_Live" tables (SiteTree, KenoteSpeaker, etc).
This must be something simple. I can't imagine Silverstripe would have overlooked such an obvious flaw.