1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Incorrect SQL query on list

Discussion in 'Community' started by flashitool, Oct 11, 2018.

  1. flashitool

    flashitool Member

    Level: Community
    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.
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  3. flashitool

    flashitool Member

    Level: Community
    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.
     
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  5. flashitool

    flashitool Member

    Level: Community
    Here are both queries :

    table: mergeJoinedData get ids
    Code (Text):
    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 (Text):
    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!
     
  6. flashitool

    flashitool Member

    Level: Community
    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.
     
  7. troester

    troester Well-Known Member Staff Member

    Level: Community
    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)
     
  8. troester

    troester Well-Known Member Staff Member

    Level: Community
    Ah, posts crossing.
    You may close solved threads by unchecking "open" in Thread tools.
     
  9. flashitool

    flashitool Member

    Level: Community
    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?
     
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  11. flashitool

    flashitool Member

    Level: Community
    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','%');.
     
  12. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     

Share This Page