22

I created my custom DQL function for Doctrine DQL:

class Translate extends FunctionNode {
    public $field;

    public function getSql(SqlWalker $sqlWalker) {
        $query = "TRANSLATE(" . $this->field->dispatch($sqlWalker) . ", 'àâäãáåÀÁÂÃÄÅçÇéèêëÉÈÊËîïìíÌÍÎÏñÑôöðòóÒÓÔÕÖùúûüÙÚÛÜýÿÝ', 'AAAAAAAAAAAACCEEEEEEEEIIIIIIIINNOOOOOOOOOOUUUUUUUUYYY')";
        return $query;
    }

    public function parse(Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

It seems to work well when using it.

But if the string parameter contains more than 307 chars, it is not working. There is no error but the script is ending.

$query = $this->createQueryBuilder('...');
$query->addSelect("TRANSLATE('less than 307 chars')"); // working
$query->addSelect("TRANSLATE('more than 307 chars')"); // NOT working

How can I use more than 307 chars?

andy
  • 2,002
  • 1
  • 12
  • 21
Seb33300
  • 7,464
  • 2
  • 40
  • 57
  • 2
    Have you tried to run just SQL with more than 307 chars? Maybe it's problem in max query size. – Dmytro Krasun Mar 27 '13 at 16:10
  • 3
    Yes, and there is no limitation. – Seb33300 Mar 27 '13 at 16:25
  • And if i modify my DQL function (Translate class) to directly put my string, it's working : $query = "TRANSLATE('raw string with more than 307 chars working', 'àâäãáåÀÁÂÃÄÅçÇéèêëÉÈÊËîïìíÌÍÎÏñÑôöðòóÒÓÔÕÖùúûüÙÚÛÜýÿÝ', 'AAAAAAAAAAAACCEEEEEEEEIIIIIIIINNOOOOOOOOOOUUUUUUUUYYY')"; – Seb33300 Mar 27 '13 at 16:28
  • 16
    Did you consider writing a test case that verifies if the problem lies in the generated SQL or in the RDBMS? – Ocramius Mar 29 '13 at 12:27
  • What is the exception error message exactly? – Nico Jul 22 '13 at 19:37
  • 2
    Old question, don't know the DQL answer, but: seems like a simple `iconv` to ASCII would do about the same thing without needing to provide all characters seperately. – Wrikken Jan 13 '14 at 21:53
  • You might want to have a look at `DoctrineExtensions` as possible solution for your original problem, which might be related to "slugification". See also [https://github.com/l3pp4rd/DoctrineExtensions/blob/master/doc/sluggable.md#transliterator](https://github.com/l3pp4rd/DoctrineExtensions/blob/master/doc/sluggable.md#transliterator) – Th. Ma. Mar 14 '14 at 16:07
  • Have you tried to store this ASCII-only text in another column instead of using this function? – A.L Apr 26 '14 at 18:00
  • I tried to recreate your issue and couldn't. I tried 'SELECT TRANSLATE(c.notes) from DemoBundle:Entity c where c.id = 3' where 'c.notes' was a long text field. I also tried 'SELECT TRANSLATE("verylongstring...") from CranaContactsBundle:Contact c where c.id = 3'. The only change I made to your custom function class was to use the 'REPLACE()' SQL function instead of 'TRANSLATE()'. Can you post the whole query you are running, with the SQL? – Ryan May 20 '14 at 04:39
  • What is the script ending on? – Steve Buzonas Jul 10 '14 at 18:44
  • This has nothing to do with 307 character limit. It's probably that you have an invalid character at position 308 in your database. Try the SQL with a `:substitution_variable` rather than injecting text into your SQL. You'll probably get a more debuggable error message. – cartbeforehorse Jul 15 '14 at 08:30
  • 1
    Is the value from `$this->field->dispatch($sqlWalker)` returned in single quotes? – Sverri M. Olsen Sep 20 '14 at 01:27

1 Answers1

2

Like wrikken had already explained, Transliteration should be done using iconv since that will save your server resources.

here's a simple function that will transliterate (convert non latin characters to their closest representations in latin charset) a string.

function transliterateString($str)
{
    $serverLocale = setlocale(LC_CTYPE, 0);
    setlocale(LC_CTYPE, 'en_US.UTF8');
    // transliterate the string using iconv
    $str = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $str); 
    setlocale(LC_CTYPE, $serverLocale); //  return the locale to what it was before
    return $str;
}

Usage: 

$string = "café"; 
echo $string;
echo transliterateString($string);

Above will output the below: 

café
cafe
dnshio
  • 914
  • 1
  • 8
  • 21