Additionnal join in a DBjoin Elt does not trigger

[lcollong]

Hi,

I've setup a DBjoin element to show the result from two tables using the "Additional join statements" feature of the DBJoin elt (see screenshot). It generates the following error where one can see that the additional left join (on eco_users) is not triggered in the general query. Thus unknown column :

getData:Unknown column 'eco_users.name' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `fk_factures`.`id` AS `fk_factures___id`, `fk_factures`.`id` AS `fk_factures___id_raw`, `fk_factures`.`parent_id` AS `fk_factures___parent_id_raw`, CONCAT_WS('', eco_users.name,' (',fk_prestataires.com_nom_commercial,')') AS `fk_factures___parent_id`, `fk_factures`.`invoice_date` AS `fk_factures___invoice_date`, `fk_factures`.`invoice_date` AS `fk_factures___invoice_date_raw`, `fk_factures`.`invoice_number` AS `fk_factures___invoice_number`, `fk_factures`.`invoice_number` AS `fk_factures___invoice_number_raw`, `fk_factures`.`invoice_status` AS `fk_factures___invoice_status`, `fk_factures`.`invoice_status` AS `fk_factures___invoice_status_raw`, `fk_factures`.`company` AS `fk_factures___company`, `fk_factures`.`company` AS `fk_factures___company_raw`, `fk_factures`.`address` AS `fk_factures___address`, `fk_factures`.`address` AS `fk_factures___address_raw`, `fk_factures`.`city` AS `fk_factures___city`, `fk_factures`.`city` AS `fk_factures___city_raw`, `fk_factures`.`country` AS `fk_factures___country`, `fk_factures`.`country` AS `fk_factures___country_raw`, `fk_factures`.`name` AS `fk_factures___name`, `fk_factures`.`name` AS `fk_factures___name_raw`, `fk_factures`.`email` AS `fk_factures___email`, `fk_factures`.`email` AS `fk_factures___email_raw`, `fk_factures`.`invoice_type` AS `fk_factures___invoice_type`, `fk_factures`.`invoice_type` AS `fk_factures___invoice_type_raw`, `fk_factures`.`invoice_content` AS `fk_factures___invoice_content`, `fk_factures`.`invoice_content` AS `fk_factures___invoice_content_raw`, `fk_factures`.`invoice_sub` AS `fk_factures___invoice_sub_raw`, CONCAT_WS('', code,' - ',description,' : ',price,' (',vat,')') AS `fk_factures___invoice_sub`, `fk_factures`.`invoice_ht` AS `fk_factures___invoice_ht`, `fk_factures`.`invoice_ht` AS `fk_factures___invoice_ht_raw`, `fk_factures`.`invoice_vat` AS `fk_factures___invoice_vat`, `fk_factures`.`invoice_vat` AS `fk_factures___invoice_vat_raw`, `fk_factures`.`invoice_ttc` AS `fk_factures___invoice_ttc`, `fk_factures`.`invoice_ttc` AS `fk_factures___invoice_ttc_raw`, `fk_factures`.`invoice_pdf` AS `fk_factures___invoice_pdf`, `fk_factures`.`invoice_pdf` AS `fk_factures___invoice_pdf_raw`, `fk_factures`.`invoice_calc_purchase` AS `fk_factures___invoice_calc_purchase`, `fk_factures`.`invoice_calc_purchase` AS `fk_factures___invoice_calc_purchase_raw`, `fk_factures`.`invoice_calc_price` AS `fk_factures___invoice_calc_price`, `fk_factures`.`invoice_calc_price` AS `fk_factures___invoice_calc_price_raw`, `fk_factures`.`invoice_calc_desc` AS `fk_factures___invoice_calc_desc`, `fk_factures`.`invoice_calc_desc` AS `fk_factures___invoice_calc_desc_raw`, `fk_factures`.`invoice_payment_method` AS `fk_factures___invoice_payment_method`, `fk_factures`.`invoice_payment_method` AS `fk_factures___invoice_payment_method_raw`, `fk_factures`.`IPN_Txn_Id` AS `fk_factures___IPN_Txn_Id`, `fk_factures`.`IPN_Txn_Id` AS `fk_factures___IPN_Txn_Id_raw`, `fk_factures`.`IPN_payment` AS `fk_factures___IPN_payment`, `fk_factures`.`IPN_payment` AS `fk_factures___IPN_payment_raw`, `fk_factures`.`IPN_status` AS `fk_factures___IPN_status`, `fk_factures`.`IPN_status` AS `fk_factures___IPN_status_raw`, `fk_factures`.`IPN_address` AS `fk_factures___IPN_address`, `fk_factures`.`IPN_address` AS `fk_factures___IPN_address_raw`, `fk_factures`.`id` AS slug , `fk_factures`.`id` AS `__pk_val` FROM `fk_factures` LEFT JOIN `fk_plans` AS `fk_plans` ON `fk_plans`.`id` = `fk_factures`.`invoice_sub` LEFT JOIN `fk_prestataires` AS `fk_prestataires` ON `fk_prestataires`.`id` = `fk_factures`.`parent_id` ORDER BY `fk_factures`.`invoice_status` ASC, `fk_factures`.`invoice_number` DESC LIMIT 0, 10

It' a J2.5 installation updated to a recent 24 hours ago githup master.zip full replace. I've tried to replace "thistable" by the actual table name without any change.

Is this a general problem or specific to my site ?

 

[cheesegrits]

Nope, it's an issue I just haven't yet been able to resolve. The 'additional joins' works for form views, but not in list views, and as such isn't a whole heck of a lot of use. It's pretty much "work in progress".

-- hugh

 

[rob]

Like Hugh I've looked at it and its probably not at all feasible to implement into the list view.

 

[lcollong]

Ok. Thanks for looking. I'll do it another way. May I suggest to add an alert on the wiki or inside the tip text ?

 

[cheesegrits]

I may just have to remove that feature. I started adding it as a request from a Pro user, didn't anticipate the difficulty of getting the joins added in the main list query. So I'll mark it as 'deprecated / non functional' and strip it back out in a future release.

-- hugh

 

[cheesegrits]

BTW, the usual workaround to get a 'complex' label involving joins would be to create a calc element which builds the label as it's own field, and use that.

-- hugh

 

[cheesegrits]

I've put a note on the tooltip. That feature isn't in the wiki anyway.

-- hugh

 

[lcollong]

Thanks. The calc element is a powerfull solution indeed. I had to use it intensively for this customer in order to retrieve jomsocial custom field's label/value "spread" over several tables. However the idea of the additional join feature was an elegant solution to solve these situations. May be it'll be back in some future version....