XF 2.1 Trying to use a custom thread field as the compare value in whereOr in finder... not supported, bug, feature request, or am I doing it wrong?

Kevin

Well-known member
I have two custom thread fields, cv_event_date_start and cv_event_date_end. In my finder I'm trying to include a whereOr clause to check if either the end date value is blank or if it is greater than the start date. When executing the code it looks like left side (the end date) of the whereOr condition array gets translated to the actual column name as expected but the right side of the condition array (the start date) does not and stays the literal value.

To clarify, here's my finder command...
Code:
$threadFinder
    ->with(['Forum', 'User'])
    ->with('Forum.Node.Permissions|' . $visitor->permission_combination_id)
    ->where('discussion_state', 'visible')
    ->where('discussion_type', '<>', 'redirect')
    ->where('CustomFields|cv_event_date_start.field_value', '<>', '')
    ->where('CustomFields|cv_event_date_start.field_value', '<=', $dateTodayYYYMMDD)
    ->whereOr(
        ['CustomFields|cv_event_date_end.field_value', '=', ''],
        ['CustomFields|cv_event_date_end.field_value', '>=', 'CustomFields|cv_event_date_start.field_value']
    )
    ->order('CustomFields|cv_event_date_start.field_value');

Here's the resulting getQuery...
Code:
SELECT `xf_thread`.*, `xf_forum_Forum_1`.*, `xf_node_Node_2`.*, `xf_user_User_3`.*, `xf_permission_cache_content_Permissions_4`.*
FROM `xf_thread`
LEFT JOIN `xf_forum` AS `xf_forum_Forum_1` ON (`xf_forum_Forum_1`.`node_id` = `xf_thread`.`node_id`)
LEFT JOIN `xf_node` AS `xf_node_Node_2` ON (`xf_node_Node_2`.`node_id` = `xf_forum_Forum_1`.`node_id`)
LEFT JOIN `xf_user` AS `xf_user_User_3` ON (`xf_user_User_3`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_4` ON (`xf_permission_cache_content_Permissions_4`.`content_type` = 'node' AND `xf_permission_cache_content_Permissions_4`.`content_id` = `xf_node_Node_2`.`node_id` AND `xf_permission_cache_content_Permissions_4`.`permission_combination_id` = '1')
LEFT JOIN `xf_thread_field_value` AS `xf_thread_field_value_CustomFields_5` ON (`xf_thread_field_value_CustomFields_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_field_value_CustomFields_5`.`field_id` = 'cv_event_date_start')
LEFT JOIN `xf_thread_field_value` AS `xf_thread_field_value_CustomFields_6` ON (`xf_thread_field_value_CustomFields_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_field_value_CustomFields_6`.`field_id` = 'cv_event_date_end')
WHERE (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread_field_value_CustomFields_5`.`field_value` <> '') AND (`xf_thread_field_value_CustomFields_5`.`field_value` <= '2020-06-02') AND ((`xf_thread_field_value_CustomFields_6`.`field_value` = '') OR (`xf_thread_field_value_CustomFields_6`.`field_value` >= 'CustomFields|cv_event_date_start.field_value'))
ORDER BY `xf_thread_field_value_CustomFields_5`.`field_value` ASC

So in particular, this...
Code:
    ->whereOr(
        ['CustomFields|cv_event_date_end.field_value', '=', ''],
        ['CustomFields|cv_event_date_end.field_value', '>=', 'CustomFields|cv_event_date_start.field_value']
    )
... is turning into this...
Code:
AND (
    (`xf_thread_field_value_CustomFields_6`.`field_value` = '') 
    OR (`xf_thread_field_value_CustomFields_6`.`field_value` >= 'CustomFields|cv_event_date_start.field_value')
)
... but I was trying to accomplish this:
Code:
AND (
    (`xf_thread_field_value_CustomFields_6`.`field_value` = '') 
    OR (`xf_thread_field_value_CustomFields_6`.`field_value` >= `xf_thread_field_value_CustomFields_5`.`field_value`)
)

🤔
 

TickTackk

Well-known member
Try wrapping 'CustomFields|cv_event_date_start.field_value' with $this->columnSqlName() and see if it works?
 
Top