Tip for get_full_list where clause

SuiteCRM Forum - English Language Developer Collaboration
#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.

#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…

#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 …

#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…