Hi,
i’m making a SQL request with Yii ActiveRecord and i added a “join” param to it.
My problem is that the join "ON" condition uses column from another join table of the query (this one created with Yii "with" param), and the SQL Database send an error saying that the column is unknown.
This is because "join param" is written before the "with param join" so Mysql do not know it yet.
Does someone knows how to reverse the order ?
What i get :
SELECT COUNT(pi.id) as nbProspects, `t`.`id` AS `t0_c0`, `t_libTradG`.`id` AS `t1_c0`, `t_libTradG_trads`.`id` AS `t2_c0`, `t_libTradG_trads`.`fk_langue` AS `t2_c1`, `t_libTradG_trads`.`valeur` AS `t2_c2`, `t_libTradG_trads`.`fk_trad_g` AS `t2_c3`, `t_libTradG_trads_fkLangue`.`id` AS `t3_c0`, `t_libTradG_trads_fkLangue`.`fk_devise` AS `t3_c1`, `t_libTradG_trads_fkLangue`.`fk_lib_trad_g` AS `t3_c2`, `t_libTradG_trads_fkLangue`.`_format_us` AS `t3_c3`, `t_libTradG_trads_fkLangue`.`code` AS `t3_c4`, `t_libTradG_trads_fkLangue`.`_site_langue` AS `t3_c5`, `t_region`.`id` AS `t4_c0`, `t_region`.`fk_pays` AS `t4_c1`, `t_region`.`fk_lib_trad_g` AS `t4_c2`, `t_region`.`photo` AS `t4_c3`, `t_region`.`fk_txt_trad_g` AS `t4_c4`, `t_region_dept`.`id` AS `t5_c0`, `t_region_dept`.`fk_region` AS `t5_c1`, `t_region_dept`.`fk_lib_trad_g` AS `t5_c2`, `t_region_dept`.`photo` AS `t5_c3`, `t_region_dept`.`fk_txt_trad_g` AS `t5_c4`
FROM `pays` `t`
JOIN prospect_infos pi ON pi.fk_locvac_pays = t.id OR pi.fk_locvac_region = t_region.id OR pi.fk_locvac_dept = t_region_dept.id
LEFT OUTER JOIN `trad_groupe` `t_libTradG` ON (`t`.`fk_lib_trad_g`=`t_libTradG`.`id`)
LEFT OUTER JOIN `trad` `t_libTradG_trads` ON (`t_libTradG_trads`.`fk_trad_g`=`t_libTradG`.`id`)
LEFT OUTER JOIN `langue` `t_libTradG_trads_fkLangue` ON (`t_libTradG_trads`.`fk_langue`=`t_libTradG_trads_fkLangue`.`id`)
LEFT OUTER JOIN `region` `t_region` ON (`t_region`.`fk_pays`=`t`.`id`)
LEFT OUTER JOIN `dept` `t_region_dept` ON (`t_region_dept`.`fk_region`=`t_region`.`id`) GROUP BY t.id
what i want :
SELECT COUNT(pi.id) as nbProspects, `t`.`id` AS `t0_c0`, `t_libTradG`.`id` AS `t1_c0`, `t_libTradG_trads`.`id` AS `t2_c0`, `t_libTradG_trads`.`fk_langue` AS `t2_c1`, `t_libTradG_trads`.`valeur` AS `t2_c2`, `t_libTradG_trads`.`fk_trad_g` AS `t2_c3`, `t_libTradG_trads_fkLangue`.`id` AS `t3_c0`, `t_libTradG_trads_fkLangue`.`fk_devise` AS `t3_c1`, `t_libTradG_trads_fkLangue`.`fk_lib_trad_g` AS `t3_c2`, `t_libTradG_trads_fkLangue`.`_format_us` AS `t3_c3`, `t_libTradG_trads_fkLangue`.`code` AS `t3_c4`, `t_libTradG_trads_fkLangue`.`_site_langue` AS `t3_c5`, `t_region`.`id` AS `t4_c0`, `t_region`.`fk_pays` AS `t4_c1`, `t_region`.`fk_lib_trad_g` AS `t4_c2`, `t_region`.`photo` AS `t4_c3`, `t_region`.`fk_txt_trad_g` AS `t4_c4`, `t_region_dept`.`id` AS `t5_c0`, `t_region_dept`.`fk_region` AS `t5_c1`, `t_region_dept`.`fk_lib_trad_g` AS `t5_c2`, `t_region_dept`.`photo` AS `t5_c3`, `t_region_dept`.`fk_txt_trad_g` AS `t5_c4`
FROM `pays` `t`
LEFT OUTER JOIN `trad_groupe` `t_libTradG` ON (`t`.`fk_lib_trad_g`=`t_libTradG`.`id`)
LEFT OUTER JOIN `trad` `t_libTradG_trads` ON (`t_libTradG_trads`.`fk_trad_g`=`t_libTradG`.`id`)
LEFT OUTER JOIN `langue` `t_libTradG_trads_fkLangue` ON (`t_libTradG_trads`.`fk_langue`=`t_libTradG_trads_fkLangue`.`id`)
LEFT OUTER JOIN `region` `t_region` ON (`t_region`.`fk_pays`=`t`.`id`)
LEFT OUTER JOIN `dept` `t_region_dept` ON (`t_region_dept`.`fk_region`=`t_region`.`id`) GROUP BY t.id
JOIN prospect_infos pi ON pi.fk_locvac_pays = t.id OR pi.fk_locvac_region = t_region.id OR pi.fk_locvac_dept = t_region_dept.id
Thanks