jDiction Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

jDiction 2.2.0 released. http://jdiction.org/downloads

Author Topic: Not translated when sql contains count()  (Read 10440 times)

zigzo

  • Newbie
  • *
  • Posts: 11
    • View Profile
Not translated when sql contains count()
« 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!


Logged

Harald Leithner

  • Administrator
  • Hero Member
  • *****
  • Posts: 1688
    • View Profile
Re: Not translated when sql contains count()
« Reply #1 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.
Logged
Joomla! 5.0 Release Manager
Vote at JED

zigzo

  • Newbie
  • *
  • Posts: 11
    • View Profile
Re: Not translated when sql contains count()
« Reply #2 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;
        }
Logged

Harald Leithner

  • Administrator
  • Hero Member
  • *****
  • Posts: 1688
    • View Profile
Re: Not translated when sql contains count()
« Reply #3 on: August 01, 2014, 03:56:43 pm »

J4i, I removed the protection in 1.3.0.
Logged
Joomla! 5.0 Release Manager
Vote at JED

Harald Leithner

  • Administrator
  • Hero Member
  • *****
  • Posts: 1688
    • View Profile
Re: Not translated when sql contains count()
« Reply #4 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?
Logged
Joomla! 5.0 Release Manager
Vote at JED