jDiction Forum

English => General Questions => Topic started by: zigzo on July 11, 2014, 08:22:47 am

Title: Not translated when sql contains count()
Post by: zigzo on July 11, 2014, 08:22:47 am

Hello,

I am using the below query where the cc.title is bilingual.  However it cannot be translated.

Code: [Select]

$query = 'SELECT cc.title AS text, cc.id AS id, cc.parent_id as parentid, cc.alias as alias, cc.access as access'
. ', cc.accessuserid as accessuserid, cc.owner_id as owner_id '
. ', count(cc.id) as number'
. ' FROM `#__phocagallery_categories` AS cc'
. ' INNER JOIN `#__photos` AS a ON cc.id = a.catid'
. ' WHERE cc.published = 1'
. ' AND cc.approved = 1'
. ' AND a.published = 1'
. ' GROUP BY cc.id'
. ' ORDER BY cc.id DESC'
. ' LIMIT 100'; 
After some trial-and-errors,  I found that the title can be translated successfully if I replace "count(cc.id) as number" with "1 as number". 

I am using joomla 3.3.0 and jdiction 1.2.0.  When I move the whole module back to my old site (joomla 3.1.5 with jdiction 0.9.9.10), it works.  Is it a problem of incompatibility with joomla 3.3.0 or is there a problem with the count() in sql?  Thanks!

Title: Re: Not translated when sql contains count()
Post by: Harald Leithner on July 11, 2014, 11:08:32 am

the reason for this is that group by and count() queries are disabled in the translation module.

The problem is that its not really possible to be sure which row should be translated because group by my not be that primary key of the table we want to translate.

in this case the primary key is grouped and we want to translate the title of this grouped id, so that should work. But there could be other queries where that could be fatal.

You can try to remove the protection in /libraries/jdiction/database/jddatabase.php line 538

Change it from:

Code: [Select]

if (count($parsed['SELECT']) > 1 || $parsed['SELECT'][0]['base_expr'] != 'COUNT') {to

Code: [Select]

if (count($parsed['SELECT']) > 1) {
please give me feedback if it works for you.

Title: Re: Not translated when sql contains count()
Post by: zigzo on July 15, 2014, 03:54:05 am

It did not work at first but after I commented around line 420 as well, it works!  Thanks! :)

Code: [Select]

if (stripos(substr($this->sql, 0, stripos($this->sql, 'FROM')), 'COUNT(') !== false) {
         // $translate = false;
        }

Title: Re: Not translated when sql contains count()
Post by: Harald Leithner on August 01, 2014, 03:56:43 pm

J4i, I removed the protection in 1.3.0.

Title: Re: Not translated when sql contains count()
Post by: Harald Leithner on August 05, 2014, 12:37:41 pm

I have to add the filter for count, but only for queries where only the count value is selected.

Is it possible that you test the attached version?