An option for filtering for "One to many Relationships" doesn't work.

[blue-mouse]

Hi,
I have fabrik version from GitHub from 2 days ago.

I try some option in:
Fabrik: edit list --> Data --> Prefilter

I use it for table that is "One to Many Relationships"
- parent it is myjoomla_documents table
- child it is myjoomla_subdocuments table

Join (where) Field (myjoomla_subdocuments___subdocument-field3) condition (is null).

I need to see all child items that have field subdocuments-field3 empty with their parent items.

Instead of that after that filtering i have only that parents items that have no child item - what is wrong.

I try other option and that works OK:
Join (where) Field (myjoomla_subdocuments___subdocument-field3) condition (equals) value (testblabla) Type(text)

I have all child items with field subdocument-field3 value= "testblabla" and their parents item,


I think that condition:
Join (where) Field (myjoomla_subdocuments___subdocument-field3) condition (is null)

is proper because i use it for other table that is not "One to Many Relationships" and it works.

Can i get any advice or solution of that?

 

[troester]

How did you join the tables (left/right join...) and on which elements?

You can enable fabrikdebug (in fabrik options), then append &fabrikdebug=1 to your frontend URL (or check debug in backend)
A click on getListData: Documents will show you the generated query

 

[blue-mouse]

left JOIN ccjom_cc_cele ON ccjom_cc_cele_ogolne.id_c_o = ccjom_cc_cele.id_c_o

Exactly my table names are:
- parent it is myjoomla_documents table ---> ccjom_cc_cele_ogolne
- child it is myjoomla_subdocuments table ---> ccjom_cc_cele

Join (where) Field (ccjom_cc_cele___miernik) condition (is null).

Not understand enought that query...

SELECT SQL_CALC_FOUND_ROWS DISTINCT 
`ccjom_cc_cele_ogolne`.`id_c_o` AS `ccjom_cc_cele_ogolne___id_c_o`, 
`ccjom_cc_cele_ogolne`.`id_c_o` AS `ccjom_cc_cele_ogolne___id_c_o_raw`, 
`ccjom_cc_cele_ogolne`.`cel_ogolny` AS 
`ccjom_cc_cele_ogolne___cel_ogolny`, `ccjom_cc_cele_ogolne`.`cel_ogolny` 
AS `ccjom_cc_cele_ogolne___cel_ogolny_raw`, `ccjom_cc_cele_ogolne`.`od` 
AS `ccjom_cc_cele_ogolne___od`, `ccjom_cc_cele_ogolne`.`od` AS 
`ccjom_cc_cele_ogolne___od_raw`, `ccjom_cc_cele_ogolne`.`do` AS 
`ccjom_cc_cele_ogolne___do`, `ccjom_cc_cele_ogolne`.`do` AS 
`ccjom_cc_cele_ogolne___do_raw`, `ccjom_cc_cele_ogolne`.`opracowal` AS 
`ccjom_cc_cele_ogolne___opracowal_raw`, `ccjom_users_3`.`id` AS 
`ccjom_cc_cele_ogolne___opracowal`, `ccjom_cc_cele_ogolne`.`data_opr` AS 
`ccjom_cc_cele_ogolne___data_opr`, `ccjom_cc_cele_ogolne`.`data_opr` AS 
`ccjom_cc_cele_ogolne___data_opr_raw`, 
`ccjom_cc_cele_ogolne`.`odpowiedzialny` AS 
`ccjom_cc_cele_ogolne___odpowiedzialny_raw`, `ccjom_users_4`.`username` 
AS `ccjom_cc_cele_ogolne___odpowiedzialny`, `ccjom_cc_cele_ogolne`.`uwagi` 
AS `ccjom_cc_cele_ogolne___uwagi`, `ccjom_cc_cele_ogolne`.`uwagi` AS 
`ccjom_cc_cele_ogolne___uwagi_raw`, `ccjom_cc_cele_ogolne`.`zatw_tak_nie` 
AS `ccjom_cc_cele_ogolne___zatw_tak_nie`, 
`ccjom_cc_cele_ogolne`.`zatw_tak_nie` AS 
`ccjom_cc_cele_ogolne___zatw_tak_nie_raw`, 
`ccjom_cc_cele_ogolne`.`zatwierdzil` AS 
`ccjom_cc_cele_ogolne___zatwierdzil_raw`, `ccjom_users_5`.`id` AS 
`ccjom_cc_cele_ogolne___zatwierdzil`, `ccjom_cc_cele_ogolne`.`data_zatw` AS 
`ccjom_cc_cele_ogolne___data_zatw`, `ccjom_cc_cele_ogolne`.`data_zatw` AS 
`ccjom_cc_cele_ogolne___data_zatw_raw`, `ccjom_cc_cele`.`id_c_s` AS 
`ccjom_cc_cele___id_c_s`, `ccjom_cc_cele`.`id_c_s` AS 
`ccjom_cc_cele___id_c_s_raw`, `ccjom_cc_cele`.`cel_szcz` AS 
`ccjom_cc_cele___cel_szcz`, `ccjom_cc_cele`.`cel_szcz` AS 
`ccjom_cc_cele___cel_szcz_raw`, `ccjom_cc_cele`.`date_time` AS 
`ccjom_cc_cele___date_time`, `ccjom_cc_cele`.`date_time` AS 
`ccjom_cc_cele___date_time_raw`, `ccjom_cc_cele`.`opracowal_auto` AS 
`ccjom_cc_cele___opracowal_auto_raw`, `ccjom_users_0`.`username` AS 
`ccjom_cc_cele___opracowal_auto`, `ccjom_cc_cele`.`id_c_o` AS 
`ccjom_cc_cele___id_c_o_raw`, `ccjom_cc_cele_ogolne_0`.`cel_ogolny` AS 
`ccjom_cc_cele___id_c_o`, `ccjom_cc_cele`.`miernik` AS 
`ccjom_cc_cele___miernik`, `ccjom_cc_cele`.`miernik` AS 
`ccjom_cc_cele___miernik_raw`, `ccjom_cc_cele`.`odpowiedzialny` AS 
`ccjom_cc_cele___odpowiedzialny_raw`, `ccjom_users`.`username` AS 
`ccjom_cc_cele___odpowiedzialny`, `ccjom_cc_cele`.`od` AS 
`ccjom_cc_cele___od`, `ccjom_cc_cele`.`od` AS `ccjom_cc_cele___od_raw`, 
`ccjom_cc_cele`.`do` AS `ccjom_cc_cele___do`, `ccjom_cc_cele`.`do` AS 
`ccjom_cc_cele___do_raw`, `ccjom_cc_cele`.`zrealizowal` AS 
`ccjom_cc_cele___zrealizowal_raw`, `ccjom_users_2`.`username` AS 
`ccjom_cc_cele___zrealizowal`, `ccjom_cc_cele`.`data_real` AS 
`ccjom_cc_cele___data_real`, `ccjom_cc_cele`.`data_real` AS 
`ccjom_cc_cele___data_real_raw`, `ccjom_cc_cele`.`rodz_celu` AS 
`ccjom_cc_cele___rodz_celu`, `ccjom_cc_cele`.`rodz_celu` AS 
`ccjom_cc_cele___rodz_celu_raw`, `ccjom_cc_cele`.`zatw_tak_nie` AS 
`ccjom_cc_cele___zatw_tak_nie`, `ccjom_cc_cele`.`zatw_tak_nie` AS 
`ccjom_cc_cele___zatw_tak_nie_raw`, `ccjom_cc_cele`.`uwagi` AS 
`ccjom_cc_cele___uwagi`, `ccjom_cc_cele`.`uwagi` AS 
`ccjom_cc_cele___uwagi_raw`, `ccjom_cc_cele`.`streszczenie` AS 
`ccjom_cc_cele___streszczenie`, `ccjom_cc_cele`.`streszczenie` AS 
`ccjom_cc_cele___streszczenie_raw`, `ccjom_cc_cele`.`zatwierdzajacy_auto` 
AS `ccjom_cc_cele___zatwierdzajacy_auto_raw`, `ccjom_users_1`.`username` 
AS `ccjom_cc_cele___zatwierdzajacy_auto`, `ccjom_cc_cele`.`data_zatw` AS 
`ccjom_cc_cele___data_zatw`, `ccjom_cc_cele`.`data_zatw` AS 
`ccjom_cc_cele___data_zatw_raw`, `ccjom_cc_cele`.`wymag_dopusz_do_prod` 
AS `ccjom_cc_cele___wymag_dopusz_do_prod`, 
`ccjom_cc_cele`.`wymag_dopusz_do_prod` AS 
`ccjom_cc_cele___wymag_dopusz_do_prod_raw`, 
`ccjom_cc_cele_ogolne`.`id_c_o` AS slug , `ccjom_cc_cele_ogolne`.`id_c_o` AS 
`__pk_val` FROM `ccjom_cc_cele_ogolne` LEFT JOIN `ccjom_cc_cele` AS 
`ccjom_cc_cele` ON `ccjom_cc_cele`.`id_c_o` = 
`ccjom_cc_cele_ogolne`.`id_c_o` LEFT JOIN `ccjom_cc_cele_ogolne` AS 
`ccjom_cc_cele_ogolne_0` ON `ccjom_cc_cele_ogolne_0`.`id_c_o` = 
`ccjom_cc_cele`.`id_c_o` LEFT JOIN `ccjom_users` AS `ccjom_users` ON 
`ccjom_users`.`id` = `ccjom_cc_cele`.`odpowiedzialny` LEFT JOIN `#__users` 
AS `ccjom_users_0` ON `ccjom_users_0`.`id` = 
`ccjom_cc_cele`.`opracowal_auto` LEFT JOIN `#__users` AS `ccjom_users_1` 
ON `ccjom_users_1`.`id` = `ccjom_cc_cele`.`zatwierdzajacy_auto` LEFT JOIN 
`#__users` AS `ccjom_users_2` ON `ccjom_users_2`.`id` = 
`ccjom_cc_cele`.`zrealizowal` LEFT JOIN `#__users` AS `ccjom_users_3` ON 
`ccjom_users_3`.`id` = `ccjom_cc_cele_ogolne`.`opracowal` LEFT JOIN 
`ccjom_users` AS `ccjom_users_4` ON `ccjom_users_4`.`id` = 
`ccjom_cc_cele_ogolne`.`odpowiedzialny` LEFT JOIN `#__users` AS 
`ccjom_users_5` ON `ccjom_users_5`.`id` = 
`ccjom_cc_cele_ogolne`.`zatwierdzil` WHERE ( ccjom_cc_cele.miernik is null ) 
ORDER BY `ccjom_cc_cele_ogolne`.`cel_ogolny` ASC, 
`ccjom_cc_cele`.`cel_szcz` ASC,`ccjom_cc_cele`.`cel_szcz` ASC

 

[troester]

The query looks ok.
The problem is that all fields of the joined subdocument group ARE null if the parent row has no child (that's MySQL joins), so to omit the "empty" childs you must add a second prefilter
AND
WHERE
ccjom_cc_cele___id_c_o IS NOT NULL

 

[blue-mouse]

But:
- i don't have any rows with child item after my filtering
- i have only parent items after filtering and appear only that parent items which don't have child in general (without any filtering) and don't appear that parent items which have child items with a fulfilment of a condition: (ccjom_cc_cele___miernik) is null.

--------
And your advice "IS NOT NULL" not exist beetwen selectable conditions.

I try an condition:
Grouped (yes and no - i try both)
AND
WHERE
ccjom_cc_cele___id_c_o NOT EQUAL TO value ( empty - nothing)

it looks wrong - but i have no idea in this moment what to put as second condition.

The result of that is "No records" - empty list.

 

[troester]

For "is not null" take GREATER THAN 0 (the element must be the one you are joining to, I assume in your dropdown list it's something like ccjom_cc_cele.id_c_o(raw))

Are you sure that there are subdocument records with existing parent id and miernik = NULL (NULL is not empty)? Check directly in your database with something like phpMyAdmin