Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » how to write 'join' for live as well for draft site...

Our old forums are still available as a read-only archive.

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

Page: 1
Go to End
Author Topic: 1325 Views
  • dio5
    Avatar
    Community Member
    501 Posts

    how to write 'join' for live as well for draft site... Link to this post

    In one of my controllers I have:

    function TipsByCategory()
       {
          if(isset($_GET['id']))
          {
             $tips = DataObject::get('Tip', "tip_categories.CategoryID = $_GET[id]", '', "LEFT JOIN tip_categories ON tip.ID = tip_categories.TipID");
             return $tips;
          }
       }

    But this doesn't seem to be working in the 'live' site. I figured out by the errors and looking in the database that for the 'live' site I had to use

    ON tip_live.ID = tip_categories.TipID

    instead of

    ON tip.ID = tip_categories.TipID

    Now I'm probably doing this the wrong way... any ideas?

  • Matt
    Avatar
    Core Development Team
    84 Posts

    Re: how to write 'join' for live as well for draft site... Link to this post

    I believe you just need to encase the table name in backticks - e.g:

    $tips = DataObject::get('Tip', "tip_categories.CategoryID = $_GET[id]", '', "LEFT JOIN tip_categories ON `tip`.ID = tip_categories.TipID");

    That should automatically use either tip or tip_live, depending on what version of the site you're looking at (draft/live respectively)

  • dio5
    Avatar
    Community Member
    501 Posts

    Re: how to write 'join' for live as well for draft site... Link to this post

    No, doesn't seem to do it here on my installation 2.1.0 rc1

  • dio5
    Avatar
    Community Member
    501 Posts

    Re: how to write 'join' for live as well for draft site... Link to this post

    Wait a minute... when I Uppercase tip and set it in backticks it works...

    so :

    ON `Tip`.ID = tip_categories.TipID

    guess I have to use the name of the class instead of the name of the table.

  • Matt
    Avatar
    Core Development Team
    84 Posts

    Re: how to write 'join' for live as well for draft site... Link to this post

    Ah, are you using Windows?

    There's a problem with MySQL on Windows - by default it doesn't care about the case-sensitivity of table names, but Linux does. This is because Windows itself is a case-insensitive file system, whereas Linux isn't.

    If you want to fix that, there's an option you can add to your my.ini file for MySQL, take a look at the MySQL docs here which explain it in more depth. If I ever build a Windows server, I use lower_case_table_names = 2, which preserves the original case - e.g. it will create the table 'Tip' instead of 'tip'. This means your code will port fine to Linux later, and you can just copy the database over without issues.

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