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]

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...

$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...

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...

    ->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...

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:

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]

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

 

[Kevin]

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

That seems to do it, thanks! Is that a 'known' thing that I missed?

 

[TickTackk]

Doesn't matter, now you know the "known" thing

 

[Kevin]

Doesn't matter, now you the "known" thing

And it's much appreciated. It's about 3:45AM local time, now that I've learned something new, I can call it a night and go to bed.

Thanks