Using 10+ fields with TranslateBehavior (causing 10+ inner joins) returns SQL error

Question

I'm setting up a project in which I want to utilize CakePHP's Translate Behavior.

Everything seemed to work fine until I reached 10 fields that I wanted it to translate. The Translate Behavior creates an INNER JOIN for each field it's trying to retrieve - which I believe is what's causing this error (only happens with 10+):

SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Two questions:

1) I tried fixing it by running the two listed SQL commands, but still no luck - how can I get it to work?

2) Is it ideal/ok/acceptable to have 10-20+ translated fields if it's going to create an INNER JOIN for each one? Should I re-think using this behavior and maybe create something on my own?

Answer 1

Did You try SET OPTION SQL_BIG_SELECTS = 1 more on https://stackoverflow.com/a/950576/182823

Answer 2

1: Its a mysql security option, what you can override. Use this code in before filter in your app controller to avoid this error.

function beforeFilter() {  
$this->{$this->modelClass}->query('SET SQL_BIG_SELECTS=1'); 
...
}

2: It's ok to join table multipled, but some advice:

• always use indexes! without table cell indexes the query can be slow

• if you have a lot of translatable datadata in lot of table use separated tables to translate, like if you have Contents and Posts use Content_I18n and Post_I18n http://book.cakephp.org/2.0/en/core-libraries/behaviors/translate.html -> Multiple Translation Models

• you can override Translate Behavior. I'm using SmoothTranslate Behavior http://bakery.cakephp.org/articles/sky_l3ppard/2010/01/05/smoothtranslate-to-make-smooth-translations I think the best way to use Translate behavior or extend it, do not write own, its good enough in CakePhp. And it will better in Cake 3. (i hope)