Wonderful Relations Logo

Get Wonderful Relations

Server side processing

We use the server-side approach of DataTables.net for all DataTable representations. This approach ensures efficient data handling by processing queries on the server rather than transferring all data to the client.

Key Features:

  • Pagination: Uses LIMIT and OFFSET in SQL queries to fetch only the required rows for the current page.

  • Filtering and Search**: Supports dynamic filtering and search functionality through “selectors,” which are applied to the query on the server.

  • Efficiency: Only the data required for the current page is sent to the client, minimizing data transfer and improving performance.

How Does It Work?

When a query is set as a “Datatable Query” and used in a Wonderful Relations DataTable, the query is processed in the background. This involves two main components:

  1. An aggregate query is executed to calculate metadata, such as the total number of rows or other aggregates (e.g., summing a column). This query processes all relevant data to compute the required results.

  2. A paged query retrieves only a subset of the data—for example, 100 rows for the current page. This query ensures that only the data needed for display is processed and sent to the client.

By applying all filters and search criteria to both queries, we ensure accurate results. Only the necessary information for pagination (e.g., total rows and current page data) and footer summaries is sent to the client, allowing even large datasets to be displayed efficiently.

Query Processing

  1. Count Query:

The server executes a query to determine the total number of rows that match the current filters (selectors). This helps calculate the total pages and provides pagination metadata to the client.

Example:

SELECT COUNT(*) FROM tablename WHERE column1 = 'value';
  1. Data Query:

A second query retrieves the actual data for the current page. This query uses LIMIT and OFFSET to fetch only the rows required for the displayed page.

Example:

SELECT * FROM tablename WHERE column1 = 'value' LIMIT 10 OFFSET 20;

Here:

• LIMIT 10 specifies the number of rows to fetch.

• OFFSET 20 skips the first 20 rows.

Subtable Search

  1. Default Behavior:

By default, only the paged data is transferred to the client. The server does not construct or send the full “tree” of related data (e.g., linked tuples from subtables). As a result, searching across subtables is not supported out of the box.

  1. Extended Search in Subtables:

To include linked tuples in the search, the query must:

• Perform all necessary joins with the subtables. • Apply the search criteria to the joined data.

Example:

SELECT * 
FROM tablename 
LEFT JOIN subtable ON tablename.id = subtable.foreign_id
WHERE tablename.column LIKE '%search%' OR subtable.column LIKE '%search%'
LIMIT 10 OFFSET 20;

• Phrase Matching with Subtable Context:

If the search phrase matches an entry in a subtable, all subtable entries related to the parent row should be included in the result.

For example:

• Searching for “keyword” in subtable.column should include all subtable rows linked to the matching parent row, not just the matching entry itself.

All of this complex behavior is automatically handled in the background when using DataTables with Wonderful Relations. This ensures a “hassle-free” experience for creating queries and DataTables on the frontend, as Wonderful Relations takes care of the “heavy lifting” like filtering, pagination, and handling large datasets efficiently.

Graph View