2di7
20 November 2020 08:46
#1
Hi guys,
I am doing something wrong on a parameter in the where clause but i don’t know what.
This is my code:
$id_condizione = $bean->id;
//$somma = 0;
$condizione = new clob_CLOB_condizioni();
$condizione->retrieve($id_condizione);
$clob = $condizione->clob_clob_anagrafica_clob_clob_condizioni_2_name;
$prodotto = $condizione->codice_prodotto;
$consuntivo = BeanFactory::getBean("clobc_CLOB_consuntivo_condizioni");
$where = "clob_clob_anagrafica_clobc_clob_consuntivo_condizioni_1_name = '".$clob."' AND cod_prodotto_c='".$prodotto."'";
$consuntivo_list = $consuntivo->get_full_list("", $where,false,0);
foreach($consuntivo_list as $list){
$somma = $somma + $list -> quantita_c;
}
$bean->qta_ap_c = $somma;
I have already tried the variables ($clob and $prodotto) by printing them in the qta_ap_c field and they are correct.
I also tried get_full_list excluding “clob_clob_anagrafica_clobc_clob_condizioni_condizioni_1_name = '”. $ Clob. “’” And it works fine.
So the problem is that it doesn’t read this relationship (I think). In the module clobc_CLOB_consuntivo_condizioni it is inserted as a field
Any help is appreciated.
pgr
20 November 2020 10:45
#2
With your logs in DEBUG level you can see the exact SQL query produced.
You will probably confirm that your suspicion is correct, the relationship doesn’t get referenced with a JOIN, so the column is unknown. But if that turns out to be the case, I don’t exactly know how to get the query constructor to include that JOIN…
2di7
20 November 2020 12:05
#3
Yes, you’re right.
It clearly makes a mess … even if I still don’t know how it works out …
SELECT clobc_clob_consuntivo_condizioni.* ,clobc_clob_consuntivo_condizioni_cstm.periodo_competenza_c,clobc_clob_consuntivo_condizioni_cstm.codice_grossista_c,clobc_clob_consuntivo_condizioni_cstm.piva_clob_c,clobc_clob_consuntivo_condizioni_cstm.cod_agente_c,clobc_clob_consuntivo_condizioni_cstm.agente_c,clobc_clob_consuntivo_condizioni_cstm.cod_prodotto_c,
clobc_clob_consuntivo_condizioni_cstm.desc_prodotto_c,clobc_clob_consuntivo_condizioni_cstm.quantita_c,clobc_clob_consuntivo_condizioni_cstm.prezzo_listino_c,clobc_clob_consuntivo_condizioni_cstm.currency_id,clobc_clob_consuntivo_condizioni_cstm.prezzo_gg_c,
clobc_clob_consuntivo_condizioni_cstm.prezzo_netto_clob_c,clobc_clob_consuntivo_condizioni_cstm.margine_grossista_c,clobc_clob_consuntivo_condizioni_cstm.ricarica_grossista_c,clobc_clob_consuntivo_condizioni_cstm.venduto_grossista_c,clobc_clob_consuntivo_condizioni_cstm.venduto_clob_c,clobc_clob_consuntivo_condizioni_cstm.venduto_grossista_margine_c,
clobc_clob_consuntivo_condizioni_cstm.venduto_grossista_ricarica_c,clobc_clob_consuntivo_condizioni_cstm.nc_c,clobc_clob_consuntivo_condizioni_cstm.area_manager_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner ,
'Users' assigned_user_name_mod, ' ' clob_clob_anagrafica_clobc_clob_consuntivo_condizioni_1_name , ' ' clob_clob_e7b2grafica_ida , ' ' accounts_clobc_clob_consuntivo_condizioni_1_name , ' ' accounts_clobc_clob_consuntivo_condizioni_1accounts_ida
FROM clobc_clob_consuntivo_condizioni
LEFT JOIN clobc_clob_consuntivo_condizioni_cstm ON clobc_clob_consuntivo_condizioni.id = clobc_clob_consuntivo_condizioni_cstm.id_c
LEFT JOIN users jt0 ON clobc_clob_consuntivo_condizioni.modified_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0
LEFT JOIN users jt1 ON clobc_clob_consuntivo_condizioni.created_by=jt1.id AND jt1.deleted=0
AND jt1.deleted=0
LEFT JOIN users jt2 ON clobc_clob_consuntivo_condizioni.assigned_user_id=jt2.id AND jt2.deleted=0
AND jt2.deleted=0 where (jt3.name = 'Test Comnicazione 2' AND cod_prodotto_c='EFC200BL') AND clobc_clob_consuntivo_condizioni.deleted=0:
MySQL error 1054: Unknown column 'jt3.name' in 'where clause'
I think I need to access directly to the linked field and not to the displayed field which is no-db …
pgr
21 November 2020 12:56
#4
It uses a jt3 alias, but that is not created anywhere…
You’d have to really dive into the code generating the query, to get to the bottom of this…