Search
Search Functionality in Wonderful Relations DataTables
The search functionality in Wonderful Relations DataTables is designed to be powerful and flexible, leveraging advanced techniques to ensure comprehensive and intuitive results.
How It Works
- Searchable Columns:
All columns marked as searchable in a query are included in the search process. This ensures a thorough search across all relevant fields in the dataset.
- Search Term Matching:
• The search logic uses RLIKE (a powerful regular expression-based search function in SQL) to match terms.
• The terms can appear in any order across the searchable columns. For example:
• Searching for “firstname lastname” matches rows where both terms appear, regardless of their order or the column they are in.
- Subtable Search:
• If the search term matches an entry in a subtable (e.g., an employee name in a location table), the entire subtable related to the matching row is included in the results.
• For instance:
• If a search matches an employee in a location’s subtable, the entire location and all its employees are displayed.
Key Features of the Search Implementation
Word Combination Logic
The search functionality utilizes a word combination algorithm to create multiple patterns from the search terms. This ensures flexibility and captures matches in various orders or combinations.
• Step 1: Split Search Terms
The input search value is split into individual words using a space (” ”) as the delimiter.
• Step 2: Generate Combinations
The wordcombos function generates all possible word combinations, ensuring that all potential matches are considered. For example:
• Input: “John Smith”
• Combinations: [“John”, “Smith”, “John Smith”, “Smith John”]
• Step 3: Build Search Pattern
The combinations are converted into a single search pattern using .* (wildcard) between words, enabling flexible matches. For example:
• “John Smith” becomes a pattern like:
“(John)+.*(Smith)+”
• Step 4: Use RLIKE
The pattern is applied in the query using the SQL RLIKE function to match terms flexibly across columns and rows.
Example Query Flow
- Input:
The user searches for “John Smith”.
- Pattern Generation:
• The search term is split and processed into a pattern:
“(John)+.*(Smith)+”
- Query Execution:
• The search pattern is applied to all searchable columns using RLIKE.
- Result Handling:
• If a match is found in a subtable, the entire subtable is included in the results for the relevant parent row.
Benefits
The search functionality in Wonderful Relations DataTables is designed to be intuitive and effortless for users. Here’s why it stands out:
• No Need to Worry About Complex Logic:
As a user, you only need to mark the columns as searchable and subqueries as subquery searchable in your configuration. The system automatically handles the complex search mechanics in the background.
• Comprehensive Matching Made Simple:
Whether the search term spans multiple columns, rows, or even subtables, the system ensures the relevant results are returned without requiring additional configuration or custom logic from the user.
• Subtable Integration Without Hassle:
By marking subtables as searchable, the system ensures that if a match is found in a subtable (e.g., a specific employee in a location), the entire subtable is displayed for the parent entry. No manual adjustments or queries are needed.
• Effortless Setup, Powerful Results:
The search is fully managed by Wonderful Relations, allowing you to focus on your data structure. The intricate handling of RLIKE, pattern generation, and subtable searches happens seamlessly in the background.
With this approach, setting up a powerful and flexible search is as simple as configuring the searchability of columns and subtables—Wonderful Relations takes care of the rest.