Jump to:

23450 Posts in 18899 Topics by 2877 members

General Questions

SilverStripe Forums » General Questions » So sick of this error!!

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

Page: 1 2
Go to End
Author Topic: 2862 Views
  • UncleCheese
    Avatar
    4085 Posts

    So sick of this error!! Link to this post

    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.

  • Victor
    Avatar
    Community Member
    128 Posts

    Re: So sick of this error!! Link to this post

    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

  • jhirm
    Avatar
    Community Member
    21 Posts

    Re: So sick of this error!! Link to this post

    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!)

  • jhirm
    Avatar
    Community Member
    21 Posts

    Re: So sick of this error!! Link to this post

    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.

  • UncleCheese
    Avatar
    4085 Posts

    Re: So sick of this error!! Link to this post

    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.

  • ajshort
    Avatar
    Community Member
    244 Posts

    Re: So sick of this error!! Link to this post

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

  • jhirm
    Avatar
    Community Member
    21 Posts

    Re: So sick of this error!! Link to this post

    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!

  • timwjohn
    Avatar
    Community Member
    98 Posts

    Re: So sick of this error!! Link to this post

    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.

    2862 Views
Page: 1 2
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.