English > General Questions

Not translated when sql contains count()

(1/1)

zigzo:
Hello,

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

--- Code: ---$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'; 
--- End code ---

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!

Harald Leithner:
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: ---if (count($parsed['SELECT']) > 1 || $parsed['SELECT'][0]['base_expr'] != 'COUNT') {
--- End code ---
to

--- Code: ---if (count($parsed['SELECT']) > 1) {
--- End code ---

please give me feedback if it works for you.

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


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

Harald Leithner:
J4i, I removed the protection in 1.3.0.

Harald Leithner:
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?

Navigation

[0] Message Index