List plugin erros

[gnavarro]

Hi,

Since I've updated Fabrik I'm getting the following error message when I run list plugins in some of my lists:

An error has occurred with a eval'd field - please inform the web-site owner.
Debug: Eval exception : list php plugin : Only variables should be assigned by reference

One list example is http://www.fisiolar.pt/index.php/pt/financas/liquidar-sessoes. You can filter to id '4971' and click list plugin 'Comprovativo de Pagamento Recebido'

Also if you filter the list using extended search all, for example 'honorio' and click list plugin 'Comprovativo de Pagamento Recebido' it will return page not found with 'Fabrik has generated an incorrect query'.

I've looked to all my php plugins and they seam all OK. How can I find the specific plugin(s) and line of code where this error occurs? Are those known issues? Am I using some php code that turned obsolete?

Please advise. Thanks in advance.

 

[troester]

What is the code in your php plugin 'Comprovativo de Pagamento Recebido'?

Search all: the complete error message (enable fabrikdebug=1) is
Fabrik has generated an incorrect query for the list Liquida??es: <br /><br /><pre>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(''.) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH( if(`fb_clientes`.nome_empr' at line 5 SQL=SELECT DISTINCT `liquidacao_sessoes_fisiot`.`id` AS __pk_val0, `fb_facturacao`.`id` AS __pk_val1 FROM `liquidacao_sessoes_fisiot` LEFT JOIN `fb_facturacao` AS `fb_facturacao` ON `fb_facturacao`.`id_liquid` = `liquidacao_sessoes_fisiot`.`id` LEFT JOIN `fb_clientes` AS `fb_clientes` ON `fb_clientes`.`user_id` = `liquidacao_sessoes_fisiot`.`user_id` LEFT JOIN `my_meios_pagamento` AS `my_meios_pagamento` ON `my_meios_pagamento`.`id` = `liquidacao_sessoes_fisiot`.`meio_pag` WHERE ( (MATCH(`liquidacao_sessoes_fisiot`.`titular`) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH( CONCAT_WS(''.) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH( if(`fb_clientes`.nome_empresa<>"") AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH(concat(`fb_clientes`.nome_empresa) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH(" - ".) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH(`fb_clientes`.nome)) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH( `fb_clientes`.nome))) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH( `liquidacao_sessoes_fisiot`.`detalhe`, `liquidacao_sessoes_fisiot`.`ref_mb`, `liquidacao_sessoes_fisiot`.`email_notific`) AGAINST ('+honorio*' IN BOOLEAN MODE) OR MATCH( `fb_facturacao`.`doc_factura`) AGAINST ('+honorio*' IN BOOLEAN MODE)) ...

Which elements did you include in search all? Not all element types can be used here.

 

[gnavarro]

Hi troester,

What is the code in your php plugin 'Comprovativo de Pagamento Recebido'?

$db =& JFactory::getDBO();
$ids = JRequest::getVar('ids', array());
$count = 0;
foreach ($ids AS $id) {
$update = "UPDATE liquidacao_sessoes_fisiot SET comprovativo_pagamento='1', estado_pagamento = '1' WHERE id=" . (int)$id;
$db->setQuery($update);
$db->query();
$count++;
}
$msg = JText::_('Dado(s) como recebido(s) ' . (int)$count . ' comprovativo(s) de pagamento.');
$params =& $this->getParams();
$params->set('table_php_msg', $msg);

Which elements did you include in search all?

titular Descri??o do Movimento
Form (add): Public
Form (edit): Clientes | Secretariado
Details view: Public">liquidacao_sessoes_fisiot___titular 0 Validations
1 JavaScript">0/1 Liquida??o
field

num_facturacao Documento(s) de Factura??o num_facturacao
Form (add): Public
Form (edit): Public
Details view: Public">liquidacao_sessoes_fisiot___num_facturacao 0 Validations
1 JavaScript">0/1 Liquida??o
field

doc_factura Documento de Factura??o doc_factura
Form (add): Secretariado
Form (edit): Secretariado
Details view: Clientes | Secretariado">fb_facturacao___doc_factura 0 Validations
0 JavaScript">0/0 Liquida??o (Factura??o)
link

user_id Cliente user_id
Form (add): Secretariado
Form (edit): Secretariado
Details view: Colaboradores| Secretariado">liquidacao_sessoes_fisiot___user_id 0 Validations
0 JavaScript">0/0 Liquida??o
databasejoin

email_notific Email de Notifica??o email_notific
Form (add): Public
Form (edit): Public
Details view: Public">liquidacao_sessoes_fisiot___email_notific 0 Validations
0 JavaScript">0/0 Liquida??o (?ltima Notifica??o)
field

detalhe Mais Informa??o detalhe
Form (add): Public
Form (edit): Public
Details view: Registered">liquidacao_sessoes_fisiot___detalhe 0 Validations
0 JavaScript">0/0 Liquida??o
calc

ref_mb Refer?ncia MB ref_mb
Form (add): Public
Form (edit): Public
Details view: Public">liquidacao_sessoes_fisiot___ref_mb 0 Validations
0 JavaScript">0/0 Liquida??o
field

 

[troester]

You php code is pretty outdated.

You are calling by reference:
$db=& JFactory::getDBO();
$params=&$this->getParams();

JRequest is deprecated, use
$myApp= JFactory::getApplication();
$xy=$myApp->input->get...
You should not use common variables like $db, this may override variables used by Joomla or Fabrik (use e.g. $mydb)
$db->query should be $db->execute in Joomla3

http://fabrikar.com/forums/index.php?wiki/common-php-tasks/#update

 

[gnavarro]

Troester,

Thanks! I will try to apply the necessary changes...

Just as an example... could you change the following code to not be obsolete:

$db =& JFactory::getDBO();
$ids = JRequest::getVar('ids', array());
$count = 0;
foreach ($ids AS $id) {
$update = "UPDATE liquidacao_sessoes_fisiot SET comprovativo_pagamento='1', estado_pagamento = '1' WHERE id=" . (int)$id;
$db->setQuery($update);
$db->query();
$count++;
}
$msg = JText::_('Dado(s) como recebido(s) ' . (int)$count . ' comprovativo(s) de pagamento.');
$params =& $this->getParams();
$params->set('table_php_msg', $msg);

this is my proposition (but I'm getting Call to a member function getVar() on null)

$mydb = JFactory::getDBO();
$ids = $myApp->input->getVar('ids', array());
$count = 0;
foreach ($ids AS $id) {
$update = "UPDATE liquidacao_sessoes_fisiot SET comprovativo_pagamento='1', estado_pagamento = '1' WHERE id=" . (int)$id;
$mydb->setQuery($update);
$mydb->query();
$count++;
}
$msg = JText::_('Dado(s) como recebido(s) ' . (int)$count . ' comprovativo(s) de pagamento.');
$params = $this->getParams();
$params->set('table_php_msg', $msg);

what about the search all?

Thanks once again!

 

[gnavarro]

Friendly bump

 

[cheesegrits]

$ids = JFactory::getApplication()->input->get('ids', array());
if (!empty($ids)) {
   $mydb = JFactory::getDBO();
   $myQuery = $myDb->getQuery(true);
   $myQuery
      ->update('liquidacao_sessoes_fisiot')
      ->set('comprovativo_pagamento = 1')
      ->set('estado_pagamento = 1')
      ->where('id IN (' . implode(',', $ids) . ')');
   $myDb->setQuery($myQuery);
   $myDb->execute();
   $statusMsg = JText::_('Dado(s) como recebido(s) ' . count($ids) . ' comprovativo(s) de pagamento.');
}
else {
   $statusMsg = "Error - no id selected";
}

Suggest you do a backup of your tables before running this.

-- hugh

 

[cheesegrits]

For the search all, you'll just have to figure out which of those elements are creating the illegal MATCH AGAINST queries, and turn off search all for those.

Easiest way would be to turn them all off, then start adding them back one by one, till it breaks. Take the one that broke out, and move on to the next one.

-- hugh

 

[gnavarro]

Ok Hugh I'll do that. Thanks for your support!

 

[gnavarro]

Hugh,

I have turned off all elements from list filter to avoid illegal MATCH AGAINST queries and tried the proposed php code in #7 and it's returning page not found - Call to a member function getQuery() on null. I have filtered the list directly through the element and then tried again without filters and is always returning Call to a member function getQuery() on null!

Please advise. Thanks in advance!

 

[troester]

Ah, there's a typo in Hugh's code (I think this part was copy/paste from yours)
Try with
$myDb= JFactory::getDbo();

 

[gnavarro]

Troester is working now thanks!

Also discovered the calc element that is generating page not found in search all. Is there something I could change in the code to avoid illegal MATCH AGAINST queries:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$user_id='{liquidacao_sessoes_fisiot___user_id_raw}';
$reccord_id='{liquidacao_sessoes_fisiot___id_raw}';
$tipologia='{liquidacao_sessoes_fisiot___pacote_raw}';

$query="
SELECT
concat(
c.label,
' a ',
t.nome_pac,
' no Dia ',
date_format(s.dta_hora_sess,'%d-%m-%Y'),
' (',
round(sum(s.valor_sess) + sum(s.valor_sess_adic),2),
'?)') as sess
FROM fb_sessoes_fisioterapia s
LEFT JOIN fb_tratamento_fisiot t ON s.id_tratamento=t.id
LEFT JOIN my_categorias c on t.categoria=c.id
where
s.cancelada=0 and
s.data_liquidacao='0000-00-00 00:00:00' and
DATE_FORMAT(s.dta_hora_sess,'%Y%m%d') <= DATE_FORMAT(NOW(),'%Y%m%d') AND
(CASE
WHEN DATE_FORMAT(s.dta_hora_sess,'%Y%m%d') < DATE_FORMAT(NOW(),'%Y%m%d') THEN (s.pacote_id='0' or s.pacote_id is null)
ELSE t.configuracao <>'2' END) AND
s.user_id='$user_id'
group by s.id
";
$db->setQuery($query);
$rows= $db->loadObjectList();


$list = array();
foreach ($rows as $row)
{
    $list[] = "<li>" . $row->sess . "</li>";
}
$avulso= '<h5>Agendamento(s) Avulso</h5>'."<ol>" .implode($list) . "</ol>";



$query="
SELECT p.id, concat(
p.num_sess,
' Sess?es com In?cio a ',
date_format(p.dta_inicio,'%d-%m-%Y'),
    ' (',
if(s.estado='Realizada',(p.num_sess* s.valor_sess)/COUNT(DISTINCT p.id),
round((p.num_sess* t.preco)/COUNT(DISTINCT p.id),2)),
'?)') as pac

from fb_pacotes p
left join fb_sessoes_fisioterapia s on p.id=s.pacote_id
left join fb_pacotes_repeat_tratamentos pt on p.id=pt.parent_id
left join fb_tratamento_fisiot t on pt.tratamentos=t.id
where
p.dta_liquidacao='0000-00-00 00:00:00' AND
(p.liquidacao_id=0 or p.liquidacao_id is null) AND
p.user_id='$user_id'
group by p.id
";
$db->setQuery($query);
$rows= $db->loadObjectList();


$list = array();
foreach ($rows as $row)
{
    $list[] = "<li>" . $row->pac  . "</li>";
}
$pacote= '<h5>Pacote(s) Requisitado(s)</h5>'."<ol>" .implode($list) . "</ol>";


if ($tipologia == 0) {
return $avulso;
}

if ($tipologia == 1) {
return $pacote;
}

if ($tipologia == 2) {
return $avulso . $pacote;
}

Thanks

 

[troester]

In general it only make sense to use calc elements filters if "only calc on save" is set to YES (so the actual values you are searching for are already stored in the database for each record and not calculated "on the fly" during list display).

Exclude your calc from the search-all elements.

 

[gnavarro]

Ok Troester thanks!

 

[gnavarro]

@troester @cheesegrits

I've found the problem, it seams to be a Fabrik bug.

The page not found issue it's happening when we use as search all filtering element one dbjoin where we personalize a concat label . When we remove the concat from the element the list php plugin works ok when fired after filtering the list.

You can test this in the following list:
http://www.fisiolar.pt/index.php/pt/financas/liquidar-sessoes

The element that is generating query errors when using concat is the user_id

To test this filter in search all by 'marinalopeshonorio@gmail.com' and in the id '4971' run the php plugin ' Comprovativo de Pagamento Entregue'. Then try to use a simple concat like 'nome'... filter the list again, run the php plugin and the query error appears.

Please advise. Thanks in advance!

 

[cheesegrits]

It's not a "bug", it's just a limitation of MySQL's full text searching. You can only do the full text ...

MATCH (`table`.`field`) AGAINST ('some text')

... using fields. So the (`table`.`field`) has to be just a field, or a list of field names. It can't be a CONCAT() statement, or anything else that generates a value at runtime.

The reason for this is that full text searching in MySQL is done using pre-prepared indexes, which are updated by MySQL every time you update a row that has column(s) that have an index. So say you have two fields, first_name and last_name, which are enabled for full text searching. MySQL builds two indexes, one for each column, that list every occurrence of every character sequence, and indexes the row they are in. So if you do "MATCH (first_name) AGAINST ('fred')", MySQL looks up 'fred' in the index for the first name field. This is obviously much quicker than having to open every row in the actual table and find the matching rows on the fly. That's what indexes are for - rapidly finding a given word.

So if you have a join which just uses 'first_name' as the label, a simple field, it can be searched for using full text indexing. You are looking up the first name, and MySQL can use that full text index on the first_name column.

But ... if you are using a CONCAT label for a join, what you are searching for isn't a simple field. It's CONCAT(first_name, ' ', last_name). And there is no MySQL index file for that, and MySQL will throw an error.

Fabrik could do a better job of preventing you from trying to use 'search all' on joins which use a CONCAT label, but the net effect is the same - you can't do it.

-- hugh

 

[gnavarro]

Hugh,

Thanks for the explanation... Nevertheless Fabrik continues to be the best application builder ever! Congrats for all of your work