adjust ticket-search to support search via Lens Dynamic Fields

[MichaelThumes]

Implement the missing functionality in TicketSearch to support search via Lens DF

Test scenario:

• Have a custom Ticket DF of type Text
• Have a custom Ticket DF of type Reference-to-Ticket
• Have a custom Ticket DF of type Lens with reference set to above Ticket Ref DF and attribute set to above Text DF
• Create a Ticket A to be referenced, set some value to Text DF
• Create a second Ticket B, set the Ticket Reference to the first Ticket

When searching for a value of a Lens field, the search should return all Tickets where the value is referenced. So we want to find the referencing tickets, not the referenced ticket. In this test scenario, when searching for value, we want to find Ticket B, not Ticket A - if we wanted to find Ticket A, we could have just used a search for Text DF = value, omitting to search via Lens at all.

According to Sven it would be nice to accomplish this with "minimal invasive changes" to existing ticket search without sacrificing performance.

[MichaelThumes]

re performance: example EXPLAIN PLan for the generated SQL of a Lens query

MariaDB [otobo]> explain SELECT DISTINCT st.id, st.tn, st.create_time FROM ticket st INNER JOIN dynamic_field_value lensdfv1 ON ( st.id = lensdfv1.object_id AND lensdfv1.field_id = 4 ) INNER JOIN dynamic_field_value dfv1 ON ( lensdfv1.value_int = dfv1 .object_id AND dfv1.field_id = 3 )  WHERE 1=1 AND (((dfv1.value_text LIKE 'TheSimple%' ) )) ORDER BY st.create_time DESC;
+------+-------------+----------+--------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+----------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                                                                                                   | key                             | key_len | ref                        | rows | Extra                                                               |
+------+-------------+----------+--------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+----------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | dfv1     | range  | dynamic_field_value_field_values,dynamic_field_value_search_date,dynamic_field_value_search_int,dynamic_field_value_search_text | dynamic_field_value_search_text | 607     | NULL                       | 1    | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | lensdfv1 | ref    | dynamic_field_value_field_values,dynamic_field_value_search_date,dynamic_field_value_search_int,dynamic_field_value_search_text | dynamic_field_value_search_int  | 13      | const,otobo.dfv1.object_id | 1    |                                                                     |
|    1 | SIMPLE      | st       | eq_ref | PRIMARY                                                                                                                         | PRIMARY                         | 8       | otobo.lensdfv1.object_id   | 1    |                                                                     |
+------+-------------+----------+--------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+----------------------------+------+---------------------------------------------------------------------+
3 rows in set (0.001 sec)

for comparison, EXPLAIN PLAN for a Simple DF search for a Dynamic Text Field (no Lens):

MariaDB [otobo]> explain SELECT DISTINCT st.id, st.tn, st.create_time FROM ticket st INNER JOIN dynamic_field_value dfv1 ON (st.id = dfv1.object_id AND dfv1.field_id = 3)  WHERE 1=1 AND (((dfv1.value_text LIKE 'TheSimple%' ) )) ORDER BY st.create_time DESC;
+------+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+----------------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys                                                                                                                   | key                             | key_len | ref                  | rows | Extra                                                               |
+------+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+----------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | dfv1  | range  | dynamic_field_value_field_values,dynamic_field_value_search_date,dynamic_field_value_search_int,dynamic_field_value_search_text | dynamic_field_value_search_text | 607     | NULL                 | 1    | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | st    | eq_ref | PRIMARY                                                                                                                         | PRIMARY                         | 8       | otobo.dfv1.object_id | 1    |                                                                     |
+------+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+----------------------+------+---------------------------------------------------------------------+
2 rows in set (0.001 sec)

Apparently the costly operation is in Line 1 both times, and that is exactly the same. The only overhead introduced for Lens search is the extra Line which implements the "indirection reference" behavior of Lens which goes directly to the dynamic_field_value_search_int index.

Looking at this, I would not try to improve performance at this point?