A) Student Example:
ER – Model Example Project
Tables in Database:
- wp_mp_organisation
- wp_mp_student
- wp_mp_studies
See WP All-in-One Backupfile for Step A: see mail
1 – Create Table Structure (later realized with wizzard)
Create Formidable Forms
Formsettings (Formidable, Missing in Screencast before)
Create Mapping
Create Project
Create Entities
Create Queries
Create Datatables
Create Formlinks
Create Template
-> Hier nicht
B: Datatable Content
=> Example Datatable 37 (Students)
Show Students with Studies as Subtable
Examples for Search: User 4295 => Me
Subtable Search Needed: (Think about it)
C: Query Editor:
Query Editor:
- Cache Querys?
- Subtable Searchable? => Could the LIMIT + OFFSET be done in the inner Query?
- Preview
- Reset Cache
- Is Temporary Table Approach used for join Subtable
D: Example SQL
Cached Queries see in table: wp_wr_query columns: cached_query, cached_count_query and temporary_tables
Query “normal approach”
"Normal" Cached:
#Count Query
SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.entity2 = 43)
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.entity1 = 43)
LEFT JOIN (
SELECT
wp_mp_studies.id as v1397,
wp_mp_studies.name as v1398,
wp_mp_studies.skz1 as v1399,
wp_mp_studies.skz2 as v1400,
form_37_id,
md5(concat('test', wp_mp_studies.id) ) as token
FROM
wp_mp_studies
GROUP BY v1397
) sub_query_180_181 ON
(sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR
(sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 )
GROUP BY vm.v1394
HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##'))
#Query
SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.entity2 = 43)
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.entity1 = 43)
LEFT JOIN (
SELECT
wp_mp_studies.id as v1397,
wp_mp_studies.name as v1398,
wp_mp_studies.skz1 as v1399,
wp_mp_studies.skz2 as v1400,
form_37_id,
md5(concat('test', wp_mp_studies.id) ) as token
FROM
wp_mp_studies
GROUP BY v1397
) sub_query_180_181 ON
(sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR
(sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 )
GROUP BY vm.v1394
HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##'))
"Normal" Executed:
#Count Query
SELECT COUNT(*) FROM (SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student
GROUP BY v1394
) vm
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.entity2 = 43)
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.entity1 = 43)
LEFT JOIN (
SELECT
wp_mp_studies.id as v1397,
wp_mp_studies.name as v1398,
wp_mp_studies.skz1 as v1399,
wp_mp_studies.skz2 as v1400,
form_37_id,
md5(concat('test', wp_mp_studies.id) ) as token
FROM
wp_mp_studies
GROUP BY v1397
) sub_query_180_181 ON
(sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR
(sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 )
GROUP BY vm.v1394
HAVING (( 1=1 ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##'))
) a;
#Query
SELECT * FROM (SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student
GROUP BY v1394
LIMIT 10 OFFSET 0) vm
LEFT JOIN wp_wr_link sub_link_180_181_l1 ON (sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.entity2 = 43)
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON (sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.entity1 = 43)
LEFT JOIN (
SELECT
wp_mp_studies.id as v1397,
wp_mp_studies.name as v1398,
wp_mp_studies.skz1 as v1399,
wp_mp_studies.skz2 as v1400,
form_37_id,
md5(concat('test', wp_mp_studies.id) ) as token
FROM
wp_mp_studies
GROUP BY v1397
) sub_query_180_181 ON
(sub_link_180_181_l1.entity1 = 42 AND sub_link_180_181_l1.id1 = vm.v1394 AND sub_link_180_181_l1.id2 = sub_query_180_181.v1397 ) OR
(sub_link_180_181_l2.entity2 = 42 AND sub_link_180_181_l2.id2 = vm.v1394 AND sub_link_180_181_l2.id1 = sub_query_180_181.v1397 )
GROUP BY vm.v1394
HAVING (( 1=1 ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##'))
) a ORDER BY v1396 ASC
Query “temporary table approach”
"Temporary" Cached:
#Temporary Table
CREATE TEMPORARY TABLE IF NOT EXISTS sub_query_180_181
(INDEX sub_query_180_181_idx (v1397)) SELECT CASE WHEN sub_link_180_181_l1.id2 is null THEN sub_link_180_181_l2.id1 else sub_link_180_181_l1.id2 end as id1,
wp_mp_studies.id as v1397,
wp_mp_studies.name as v1398,
wp_mp_studies.skz1 as v1399,
wp_mp_studies.skz2 as v1400,
form_37_id,
md5(concat('test', wp_mp_studies.id) ) as token
FROM
wp_mp_studies LEFT JOIN wp_wr_link sub_link_180_181_l1 ON (sub_link_180_181_l1.entity1 = 43 AND sub_link_180_181_l1.id1 = wp_mp_studies.id AND sub_link_180_181_l1.entity2 = 42)
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON (sub_link_180_181_l2.entity2 = 43 AND sub_link_180_181_l2.id2 = wp_mp_studies.id AND sub_link_180_181_l2.entity1 = 42)
WHERE NOT ( sub_link_180_181_l1.entity1 is null and sub_link_180_181_l2.entity1 is null) ;
#Cached Count Query
SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm
LEFT JOIN sub_query_180_181 ON vm.v1394 = sub_query_180_181.id1
GROUP BY vm.v1394
HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##'))
#Cached Query
SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student##FILTERED_BY_LINK##
WHERE ##FILTERED_BY_LINK_WHERE##
GROUP BY v1394
HAVING ##FRONTEND_FILTER####LIMIT##) vm
LEFT JOIN sub_query_180_181 ON vm.v1394 = sub_query_180_181.id1
GROUP BY vm.v1394
HAVING (( 1=##skipsearch## ) or (CONCAT(ifnull(v1394,''), ifnull(v1395,''), ifnull(v1396,''), search_field_sub_query_180_181) rlike '##search_value##'))
"Temporary" Executed:
#Temporary Table
CREATE TEMPORARY TABLE IF NOT EXISTS sub_query_180_181
(INDEX sub_query_180_181_idx (v1397)) SELECT CASE WHEN sub_link_180_181_l1.id2 is null THEN sub_link_180_181_l2.id1 else sub_link_180_181_l1.id2 end as id1,
wp_mp_studies.id as v1397,
wp_mp_studies.name as v1398,
wp_mp_studies.skz1 as v1399,
wp_mp_studies.skz2 as v1400,
form_37_id,
md5(concat('test', wp_mp_studies.id) ) as token
FROM
wp_mp_studies LEFT JOIN wp_wr_link sub_link_180_181_l1 ON (sub_link_180_181_l1.entity1 = 43 AND sub_link_180_181_l1.id1 = wp_mp_studies.id AND sub_link_180_181_l1.entity2 = 42)
LEFT JOIN wp_wr_link sub_link_180_181_l2 ON (sub_link_180_181_l2.entity2 = 43 AND sub_link_180_181_l2.id2 = wp_mp_studies.id AND sub_link_180_181_l2.entity1 = 42)
WHERE NOT ( sub_link_180_181_l1.entity1 is null and sub_link_180_181_l2.entity1 is null) ;
#COUNT QUERY
SELECT COUNT(*) FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student
WHERE (( 1=1 ) or (CONCAT(ifnull(wp_mp_student.id,''), ifnull(wp_mp_student.firstname,''), ifnull(wp_mp_student.lastname,'')) rlike '##search_value##'))
GROUP BY v1394
) a;
#QUERY
SELECT * FROM (SELECT vm.*,
GROUP_CONCAT(DISTINCT IFNULL(CONCAT('v1397::', sub_query_180_181.v1397),''), '||', IFNULL(CONCAT('v1398::', sub_query_180_181.v1398),''), '||', IFNULL(CONCAT('v1399::', sub_query_180_181.v1399),''), '||', IFNULL(CONCAT('v1400::', sub_query_180_181.v1400),''), '||', IFNULL(CONCAT('form_37_id::', sub_query_180_181.form_37_id),''), '||', IFNULL(CONCAT('token::', sub_query_180_181.token),'') SEPARATOR '&&') as sub_query_180_181
, GROUP_CONCAT(IFNULL(v1397,''), IFNULL(v1398,''), IFNULL(v1399,''), IFNULL(v1400,'')) as search_field_sub_query_180_181
FROM (SELECT
wp_mp_student.id as v1394,
wp_mp_student.firstname as v1395,
wp_mp_student.lastname as v1396,
form_36_id,
md5(concat('test', wp_mp_student.id) ) as token
FROM
wp_mp_student
WHERE (( 1=1 ) or (CONCAT(ifnull(wp_mp_student.id,''), ifnull(wp_mp_student.firstname,''), ifnull(wp_mp_student.lastname,'')) rlike '##search_value##'))
GROUP BY v1394
LIMIT 10 OFFSET 0) vm
LEFT JOIN sub_query_180_181 ON vm.v1394 = sub_query_180_181.id1
GROUP BY vm.v1394) a ORDER BY v1396 ASC E: Results
Normal Approach
2,8 + 0,004s = ~2,8s
Temporary Table Approach
0.184 + 0,055 + 0,055 => ~0,2s
F: Parts in Source Code:
Plugin Path: volumes/wordpress/html/wp-content/plugins/wonderful_relations
SourceCode Parts:
Query Generation / Execution:
modules/queries/
DataTables:
modules/datatable/actions/GetEntries.php
=> get_entries
Logging: (show error_log output)
tail -f wp-content/debug.log
