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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Archive /

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

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

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


Go to End


5 Posts   1765 Views

Avatar
dio5

Community Member, 501 Posts

17 September 2007 at 8:41am

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?

Avatar
Matt

Community Member, 86 Posts

17 September 2007 at 8:29pm

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)

Avatar
dio5

Community Member, 501 Posts

17 September 2007 at 8:34pm

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

Avatar
dio5

Community Member, 501 Posts

17 September 2007 at 8:38pm

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.

Avatar
Matt

Community Member, 86 Posts

18 September 2007 at 7:58pm

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.