Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

So sick of this error!!


Go to End
Reply

10 Posts   2902 Views

Avatar
UncleCheese

14 April 2009 at 3:32am 4085 Posts

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.

Avatar
Victor

14 April 2009 at 5:03am Community Member, 128 Posts

Two? Three! You forgot _versions.

However I tried and managed to run standard sql queries rather than DataObject::get and output them (I understand that this should be avoided if possible)

Victor

Avatar
jhirm

12 June 2009 at 10:49pm Community Member, 21 Posts

Shit, yes, this is frustrating as hell! Glad I'm not the only one who has run into this... have you figured out a solution?

I tried adding a little piece of logic in the Page_Controller that checks if $_REQUEST['stage'] is set, then altering the SQL based on that, but it seems like SS stores the last stage setting somewhere, maybe in the session. The result is that it works if ?stage is set in the URL, but if not it uses whatever setting was last set, and the SQL is wrong again, throwing the SQL error.

Please let me know if anyone figures this out. (I'll keep working on it, but I'm counting on YOU, UncleCheese! Haha, sorry, it's just that you've solved so many problems for ALL OF US!)

Avatar
jhirm

12 June 2009 at 11:41pm Community Member, 21 Posts

Brief followup -

I finally was able to figure out a really awful hack to at least get the Stage and Live sites working for my client... the Versioned class sets the stage via Versioned::reading_stage(), and this is set by the Session variable $_Session["currentStage"], which is initially set by the Get variable in the URL. The Versioned::reading_stage() is called before the Page_Controller init() function, so just setting the Session variable in the init() only sort of works... an SQL error shows up on the first page load, but upon refresh it works because the Session var has been set correctly AFTER the stage was set last time the page was loaded. Anyway, if you manually call Versioned::reading_stage("Live") in the init(), it works. The consequence is that in order to view the site in Stage mode, the URL must include the ?stage=Stage Get var, which in my opinion makes more sense anyway.

There HAS to be a better way to do this, right?

For reference, this is what I added to my Page_Controller init().

if(isset($_REQUEST["stage"])) {
   $_SESSION["currentStage"] = $_REQUEST["stage"];
} else {
   $_SESSION["currentStage"] = "Live";
   Versioned::reading_stage("Live");
}

Of course I still had to add logic in other controller methods to adjust the SQL for _Live vs. non live tables. Bitch.

Avatar
UncleCheese

13 June 2009 at 2:23am 4085 Posts

It's remarkable to me that this post has sat here for so long and only one user has sympathized with my pain!

I have made similar hacks. In the EventCalendar module, I did it this way. Can't say it's any prettier than yours :)

   public function getJoin()
   {
      $suffix = Versioned::current_stage() == "Live" ? "_Live" : "";
      $join = "LEFT JOIN CalendarEvent{$suffix} ON CalendarEvent{$suffix}.ID = CalendarDateTime.EventID";
      if(is_subclass_of($this->getEventObject(), "CalendarEvent")) {
         $parents = array_reverse(ClassInfo::ancestry($this->getEventClass()));
         foreach($parents as $class) {
            if(ClassInfo::hasTable($class)) {            
               $class = $class . $suffix;
               if($class == "CalendarEvent".$suffix) break;
                  $join .= " LEFT JOIN {$class} ON {$class}.ID = CalendarEvent{$suffix}.ID";
            }
         }
      }
      return $join;
   }   

Pretty bad. Can't believe no one is talking about this. I'm going to put in a ticket now.

Avatar
ajshort

13 June 2009 at 12:38pm Community Member, 244 Posts

If you actually use proper SQL syntax to identify your tables then the Versioned extension will automatically rewrite table names to reflect the current stage.

You should be using "KeynoteSpeaker"."ID" in trunk, and `KeynoteSpeaker`.`ID` in 2.3 - otherwise they are not recognised as table/column names and not rewritten (see lines 128-133 in Versioned.php).

Avatar
jhirm

13 June 2009 at 7:24pm Community Member, 21 Posts

Alright, there you have it! Looks like that's all I needed... thanks!! For 2.3 the correct lines in Versioned appear to be 180-198. I haven't changed my code yet, but I'll try it shortly... looks like it should solve the issue. I wish the docs always used correct SQL syntax - of course it's best practice, but it wasn't obvious that SS relied on it in some cases.

I'm still not sure I like the idea of using the get variable to set a session variable which sets the stage - it seems like it might easily confuse the client, since they initially see the ?stage=Stage in the URL when they click "draft" in the CMS. It seems like it would be intuitive for the admin to type in the URL without the extra ?stage stuff, and expect to be viewing the live site, when in fact they are still viewing the draft site... maybe not. Anyway, that's really a different issue.

Thanks again!

Avatar
timwjohn

2 October 2009 at 4:26am Community Member, 98 Posts

Ha! Took me a while to realise it's the ` character I need to use and not the ' character. Never used the ` character before. Looks like I'll be using it a lot from now on.

Go to Top