• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Incorrect SQL query on list

Hello,

I have identified a very strange problem which seems to be linked to pre-filters in a Fabrik list.

Sometimes, in what seems to be completely random fashion: the list returns no results. The value of $this->navigation->total displays the correct number of results yet $this->rows is empty.

This applies for some values of the pre-filter but not all (we have a module on the page which adds an &category=name-of-category to the URL and reloads the page). The pre-filter is set up to look for the value in the GET variable and filter on that value, this means that the pre-filter's value can change, this is important to note as when the issue happens: it only happens on certain pre-filter values, and it appears to be completely random.

After doing a bit of digging using &fabrikdebug=1 I have found what seems to be causing the issue but I can't seem to find out WHY. In the list GetData query there is a AND 2 = -2 which obviously causes the query to return nothing.

This problem also seems to be linked to the session as the issue does not appear when I open the list in a new private browsing tab (caching is also disabled on the site). The problem does not go away when using clearfilters=1, clearordering=1, or resetfilters=1.
 
The "AND 2 = -2" is added in the code that handles merging joined data (to handle the case where the main where clause doesn't return any record) so I presume you have repeat joins and have "Merge" selected in the list's join settings?

Basically, when you have repeat joins, and are merging the results, we have to do a two step query. If you aren't merging the results, then we can figure out the pagination in one query ... it's simple, we are displaying as many rows as the main query returns (so if you have a repeat join with 10 repeats, so one parent row but 10 repeats, that's 10 rows). But if we're merging joined data into a single parent row, we have to do a two step process (because that 10 repeat rows is now just one displayed row), to first get as many main (parent) table rows as the pagination calls for, then a separate query to get all the joined data for those displayed rows. And that 2=-2 thing will happen if the second phase thinks the first phase didn't find anything.

I think the only way you can debug it is to see what the initial query is doing. Which should be in the debug output, as ...

table:mergeJoinedData get ids

-- hugh
 
Thanks a lot for your reply.

The initial query (table:mergeJoinedData get ids) is returning data and seems to be operating fine.
The query is giving us 56 results on 4 joined table witch results down to 10 results once merged (which is what the navigation->total is giving).

The GetData even has the WHERE id IN () with all of the IDs in it, just the AND 2 = -2 prevents the info from being retrieved.
 
Can you paste both queries.

Unfortunately the part of the code this is happening in is brutally complex, and really hard to debug by just looking at it. Seeing the queries will help, but ... eh, it's still a tough one.

I can see the case that's causing it ...

https://github.com/Fabrik/fabrik/blob/master/components/com_fabrik/models/list.php#L2693

... and I suspect it's something to do with the limitstart / limitlength earlier in the code ...

https://github.com/Fabrik/fabrik/blob/master/components/com_fabrik/models/list.php#L2610

... but ... meh.

-- hugh
 
Here are both queries :

table: mergeJoinedData get ids
Code:
SELECT DISTINCT `jos_emundus_setup_programmes`.`id` AS __pk_val0, `jos_emundus_setup_teaching_unity`.`id` AS __pk_val1, `jos_emundus_setup_thematiques`.`id` AS __pk_val2, `jos_emundus_setup_campaigns`.`id` AS __pk_val3 FROM `jos_emundus_setup_programmes`
LEFT JOIN  `jos_emundus_setup_thematiques` AS `jos_emundus_setup_thematiques` ON `jos_emundus_setup_thematiques`.`id` = `jos_emundus_setup_programmes`.`programmes`
LEFT JOIN  `jos_emundus_setup_campaigns` AS `jos_emundus_setup_campaigns` ON `jos_emundus_setup_campaigns`.`training` = `jos_emundus_setup_programmes`.`code`
LEFT JOIN  `jos_emundus_setup_teaching_unity` AS `jos_emundus_setup_teaching_unity` ON `jos_emundus_setup_teaching_unity`.`session_code` = `jos_emundus_setup_campaigns`.`session_code`
LEFT JOIN  `jos_emundus_setup_thematiques` AS `jos_emundus_setup_thematiques_0` ON `jos_emundus_setup_thematiques_0`.`id` = `jos_emundus_setup_programmes`.`programmes`
WHERE (   jos_emundus_setup_thematiques.title LIKE '%formations-reglementaires---securite%'  AND (  jos_emundus_setup_programmes.label LIKE '%%%'  OR   jos_emundus_setup_programmes.notes LIKE '%%%' ) AND  (jos_emundus_setup_programmes.published = 1  OR jos_emundus_setup_programmes.published LIKE '[\"1\"%' OR jos_emundus_setup_programmes.published LIKE '%\"1\"%' OR jos_emundus_setup_programmes.published LIKE '%\"1\"]') AND   jos_emundus_setup_teaching_unity.date_start > '2018-10-12 16:07:22'  AND  (jos_emundus_setup_thematiques.published = 1  OR jos_emundus_setup_thematiques.published LIKE '[\"1\"%' OR jos_emundus_setup_thematiques.published LIKE '%\"1\"%' OR jos_emundus_setup_thematiques.published LIKE '%\"1\"]') AND   jos_emundus_setup_programmes.target LIKE '%%%'  AND  (jos_emundus_setup_teaching_unity.published = 1  OR jos_emundus_setup_teaching_unity.published LIKE '[\"1\"%' OR jos_emundus_setup_teaching_unity.published LIKE '%\"1\"%' OR jos_emundus_setup_teaching_unity.published LIKE '%\"1\"]') )
ORDER BY `jos_emundus_setup_programmes`.`code` ASC


list GetData:Vous recherchez...
Code:
SELECT  DISTINCT `jos_emundus_setup_programmes`.`id` AS `jos_emundus_setup_programmes___id`,
`jos_emundus_setup_programmes`.`id` AS `jos_emundus_setup_programmes___id_raw`,
`jos_emundus_setup_programmes`.`label` AS `jos_emundus_setup_programmes___label`,
`jos_emundus_setup_programmes`.`label` AS `jos_emundus_setup_programmes___label_raw`,
`jos_emundus_setup_programmes`.`programmes` AS `jos_emundus_setup_programmes___programmes_raw`,
`jos_emundus_setup_thematiques_0`.`label` AS `jos_emundus_setup_programmes___programmes`,
`jos_emundus_setup_programmes`.`code` AS `jos_emundus_setup_programmes___code`,
`jos_emundus_setup_programmes`.`code` AS `jos_emundus_setup_programmes___code_raw`,
`jos_emundus_setup_programmes`.`audience` AS `jos_emundus_setup_programmes___audience`,
`jos_emundus_setup_programmes`.`audience` AS `jos_emundus_setup_programmes___audience_raw`,
`jos_emundus_setup_programmes`.`notes` AS `jos_emundus_setup_programmes___notes`,
`jos_emundus_setup_programmes`.`notes` AS `jos_emundus_setup_programmes___notes_raw`,
`jos_emundus_setup_programmes`.`published` AS `jos_emundus_setup_programmes___published`,
`jos_emundus_setup_programmes`.`published` AS `jos_emundus_setup_programmes___published_raw`,
`jos_emundus_setup_programmes`.`content` AS `jos_emundus_setup_programmes___content`,
`jos_emundus_setup_programmes`.`content` AS `jos_emundus_setup_programmes___content_raw`,
`jos_emundus_setup_programmes`.`numcpf` AS `jos_emundus_setup_programmes___numcpf`,
`jos_emundus_setup_programmes`.`numcpf` AS `jos_emundus_setup_programmes___numcpf_raw`,
`jos_emundus_setup_programmes`.`prerequisite` AS `jos_emundus_setup_programmes___prerequisite`,
`jos_emundus_setup_programmes`.`prerequisite` AS `jos_emundus_setup_programmes___prerequisite_raw`,
`jos_emundus_setup_programmes`.`objectives` AS `jos_emundus_setup_programmes___objectives`,
`jos_emundus_setup_programmes`.`objectives` AS `jos_emundus_setup_programmes___objectives_raw`,
`jos_emundus_setup_programmes`.`partner` AS `jos_emundus_setup_programmes___partner`,
`jos_emundus_setup_programmes`.`partner` AS `jos_emundus_setup_programmes___partner_raw`,
`jos_emundus_setup_programmes`.`certificate` AS `jos_emundus_setup_programmes___certificate`,
`jos_emundus_setup_programmes`.`certificate` AS `jos_emundus_setup_programmes___certificate_raw`,
`jos_emundus_setup_programmes`.`target` AS `jos_emundus_setup_programmes___target`,
`jos_emundus_setup_programmes`.`target` AS `jos_emundus_setup_programmes___target_raw`,
`jos_emundus_setup_campaigns`.`id` AS `jos_emundus_setup_campaigns___id`,
`jos_emundus_setup_campaigns`.`id` AS `jos_emundus_setup_campaigns___id_raw`,
`jos_emundus_setup_campaigns`.`date_time` AS `jos_emundus_setup_campaigns___date_time`,
`jos_emundus_setup_campaigns`.`date_time` AS `jos_emundus_setup_campaigns___date_time_raw`,
`jos_emundus_setup_teaching_unity`.`id` AS `jos_emundus_setup_teaching_unity___id`,
`jos_emundus_setup_teaching_unity`.`id` AS `jos_emundus_setup_teaching_unity___id_raw`,
`jos_emundus_setup_teaching_unity`.`location_city` AS `jos_emundus_setup_teaching_unity___location_city`,
`jos_emundus_setup_teaching_unity`.`location_city` AS `jos_emundus_setup_teaching_unity___location_city_raw`,
`jos_emundus_setup_teaching_unity`.`label` AS `jos_emundus_setup_teaching_unity___label`,
`jos_emundus_setup_teaching_unity`.`label` AS `jos_emundus_setup_teaching_unity___label_raw`,
`jos_emundus_setup_teaching_unity`.`published` AS `jos_emundus_setup_teaching_unity___published`,
`jos_emundus_setup_teaching_unity`.`published` AS `jos_emundus_setup_teaching_unity___published_raw`,
`jos_emundus_setup_teaching_unity`.`days` AS `jos_emundus_setup_teaching_unity___days`,
`jos_emundus_setup_teaching_unity`.`days` AS `jos_emundus_setup_teaching_unity___days_raw`,
`jos_emundus_setup_teaching_unity`.`price` AS `jos_emundus_setup_teaching_unity___price`,
`jos_emundus_setup_teaching_unity`.`price` AS `jos_emundus_setup_teaching_unity___price_raw`,
`jos_emundus_setup_teaching_unity`.`date_start` AS `jos_emundus_setup_teaching_unity___date_start`,
`jos_emundus_setup_teaching_unity`.`date_start` AS `jos_emundus_setup_teaching_unity___date_start_raw`,
`jos_emundus_setup_teaching_unity`.`date_end` AS `jos_emundus_setup_teaching_unity___date_end`,
`jos_emundus_setup_teaching_unity`.`date_end` AS `jos_emundus_setup_teaching_unity___date_end_raw`,
`jos_emundus_setup_teaching_unity`.`min_occupants` AS `jos_emundus_setup_teaching_unity___min_occupants`,
`jos_emundus_setup_teaching_unity`.`min_occupants` AS `jos_emundus_setup_teaching_unity___min_occupants_raw`,
`jos_emundus_setup_teaching_unity`.`max_occupants` AS `jos_emundus_setup_teaching_unity___max_occupants`,
`jos_emundus_setup_teaching_unity`.`max_occupants` AS `jos_emundus_setup_teaching_unity___max_occupants_raw`,
`jos_emundus_setup_teaching_unity`.`occupants` AS `jos_emundus_setup_teaching_unity___occupants`,
`jos_emundus_setup_teaching_unity`.`occupants` AS `jos_emundus_setup_teaching_unity___occupants_raw`,
`jos_emundus_setup_teaching_unity`.`location_address` AS `jos_emundus_setup_teaching_unity___location_address`,
`jos_emundus_setup_teaching_unity`.`location_address` AS `jos_emundus_setup_teaching_unity___location_address_raw`,
`jos_emundus_setup_teaching_unity`.`location_zip` AS `jos_emundus_setup_teaching_unity___location_zip`,
`jos_emundus_setup_teaching_unity`.`location_zip` AS `jos_emundus_setup_teaching_unity___location_zip_raw`,
`jos_emundus_setup_teaching_unity`.`location_title` AS `jos_emundus_setup_teaching_unity___location_title`,
`jos_emundus_setup_teaching_unity`.`location_title` AS `jos_emundus_setup_teaching_unity___location_title_raw`,
`jos_emundus_setup_teaching_unity`.`tax_rate` AS `jos_emundus_setup_teaching_unity___tax_rate`,
`jos_emundus_setup_teaching_unity`.`tax_rate` AS `jos_emundus_setup_teaching_unity___tax_rate_raw`,
`jos_emundus_setup_thematiques`.`title` AS `jos_emundus_setup_thematiques___title`,
`jos_emundus_setup_thematiques`.`title` AS `jos_emundus_setup_thematiques___title_raw`,
`jos_emundus_setup_thematiques`.`color` AS `jos_emundus_setup_thematiques___color`,
`jos_emundus_setup_thematiques`.`color` AS `jos_emundus_setup_thematiques___color_raw`,
`jos_emundus_setup_thematiques`.`published` AS `jos_emundus_setup_thematiques___published`,
`jos_emundus_setup_thematiques`.`published` AS `jos_emundus_setup_thematiques___published_raw`,
`jos_emundus_setup_thematiques`.`label` AS `jos_emundus_setup_thematiques___label`,
`jos_emundus_setup_thematiques`.`label` AS `jos_emundus_setup_thematiques___label_raw`,
CONCAT_WS(':', `jos_emundus_setup_programmes`.`id`, `jos_emundus_setup_programmes`.`label`) AS slug
, `jos_emundus_setup_programmes`.`id` AS `__pk_val`

FROM `jos_emundus_setup_programmes`
LEFT JOIN  `jos_emundus_setup_thematiques` AS `jos_emundus_setup_thematiques` ON `jos_emundus_setup_thematiques`.`id` = `jos_emundus_setup_programmes`.`programmes`
LEFT JOIN  `jos_emundus_setup_campaigns` AS `jos_emundus_setup_campaigns` ON `jos_emundus_setup_campaigns`.`training` = `jos_emundus_setup_programmes`.`code`
LEFT JOIN  `jos_emundus_setup_teaching_unity` AS `jos_emundus_setup_teaching_unity` ON `jos_emundus_setup_teaching_unity`.`session_code` = `jos_emundus_setup_campaigns`.`session_code`
LEFT JOIN  `jos_emundus_setup_thematiques` AS `jos_emundus_setup_thematiques_0` ON `jos_emundus_setup_thematiques_0`.`id` = `jos_emundus_setup_programmes`.`programmes`
WHERE `jos_emundus_setup_campaigns`.`id` IN ('115','53','4','55','5','56','6','57','40','58','43','60','45','62','47','49','50','51','41','42','44','46','31','33','78','1','66','2','67','3','68','48','69','52','70','54','71','59','72','61','74','63','76','64','77','65','103','10','99','101','102','20','15','149','151','13','152','153','182','137') AND 2 = -2
ORDER BY `jos_emundus_setup_programmes`.`code` ASC

Thanks again for your time!
 
I have found the source of the issue.

The problem comes when we go to page 2 of the list and then click on a link taking us to the same list but with a prefilter active which removes enough results for there to not be a page 2. Fabrik then tries to load page 2 of the list and returns nothing.

I am going to add an &limitstart306=0 to the links on my site which should fix the problem.

Thank you very much, your idea of the limistart / limitlength helped me find the issue.
 
To see if it's related to some limitstart/limit values you may append
&limitstartX=2&limitX=3 (this should display 3 records starting with the 3rd one)
 
Ah, posts crossing.
You may close solved threads by unchecking "open" in Thread tools.
 
I'm unsure if i should mark this as solved, as i have found a way around this problem but does not fix the root of the problem.

What are your thoughts?
 
Does the list link use the same Itemid?

We do have code that should prevent this, but iirc it works partly by comparing Itemid as part of the "listref" (the unique ID we create on the fly for each instance of a list), to keep the session data for different instances of the same list separate.

-- hugh
 
The itemId is the same.

The prefilter is set to eval a bit of code which looks at $jinput->get() and checks if it includes.


For example: Having a search bar somewhere which sends the data via POST to the list, the prefilter then does something like : WHERE CONTAINS $jinput->post->getString('search','%');.
 
Yeah, if it uses the same Itemid, it's going to use the limitstart for that 'listref' session. You could also just add &resetfilters=1, which I think will automatically reset the limit and limitstart.

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top