Bug: Missing quotes if pre-filter with IN condition and additional user filter

Status
Not open for further replies.
Not that I know of.

The list contains some calc elements using AJAX calculation.

Only special thing about the list and the dropdown element used for the pre-filter and the list filter is that its content is generated using "Eval populate".
Chris
 
cheesegrits said:
dimoss Can you do fabrikdebug and paste the getData query?

VOI is your list AJAX-ified?

-- hugh
Click to expand...


Hi Hugh

This is the getData query when the pre-filters applied and I get no results

Code: 
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_plist`.`id` AS `fab_plist___id`, `fab_plist`.`id` AS `fab_plist___id_raw`, `fab_plist`.`create_date` AS `fab_plist___create_date`, `fab_plist`.`create_date` AS `fab_plist___create_date_raw`, `fab_plist`.`current_date` AS `fab_plist___current_date`, `fab_plist`.`current_date` AS `fab_plist___current_date_raw`, `fab_plist`.`create_user` AS `fab_plist___create_user_raw`, `entr_users`.`id` AS `fab_plist___create_user`, `fab_plist`.`update_user` AS `fab_plist___update_user_raw`, `entr_users_0`.`id` AS `fab_plist___update_user`, `fab_plist`.`tournament` AS `fab_plist___tournament_raw`, `fab_tournaments`.`tournament` AS `fab_plist___tournament`, `fab_plist`.`assoc` AS `fab_plist___assoc_raw`, `entr_users_1`.`username` AS `fab_plist___assoc`, `fab_plist`.`gender` AS `fab_plist___gender`, `fab_plist`.`gender` AS `fab_plist___gender_raw`, `fab_plist`.`player_id` AS `fab_plist___player_id_raw`, `fab_players`.`name` AS `fab_plist___player_id`, `fab_plist`.`name` AS `fab_plist___name`, `fab_plist`.`name` AS `fab_plist___name_raw`, `fab_plist`.`dob` AS `fab_plist___dob`, `fab_plist`.`dob` AS `fab_plist___dob_raw`, `fab_plist`.`year` AS `fab_plist___year`, `fab_plist`.`year` AS `fab_plist___year_raw`, `fab_plist`.`passport` AS `fab_plist___passport`, `fab_plist`.`passport` AS `fab_plist___passport_raw`, `fab_plist`.`cat` AS `fab_plist___cat`, `fab_plist`.`cat` AS `fab_plist___cat_raw`, `fab_plist`.`transport` AS `fab_plist___transport`, `fab_plist`.`transport` AS `fab_plist___transport_raw`, `fab_plist`.`arrival` AS `fab_plist___arrival`, `fab_plist`.`arrival` AS `fab_plist___arrival_raw`, `fab_plist`.`arrival_flight` AS `fab_plist___arrival_flight`, `fab_plist`.`arrival_flight` AS `fab_plist___arrival_flight_raw`, `fab_plist`.`departure` AS `fab_plist___departure`, `fab_plist`.`departure` AS `fab_plist___departure_raw`, `fab_plist`.`departure_flight` AS `fab_plist___departure_flight`, `fab_plist`.`departure_flight` AS `fab_plist___departure_flight_raw`, `fab_plist`.`hospitality` AS `fab_plist___hospitality_raw`, `fab_types`.`hosp_type` AS `fab_plist___hospitality`, `fab_plist`.`room` AS `fab_plist___room_raw`, `fab_room_types`.`room_type` AS `fab_plist___room`, `fab_plist`.`no_days` AS `fab_plist___no_days`, `fab_plist`.`no_days` AS `fab_plist___no_days_raw`, `fab_plist`.`room_price` AS `fab_plist___room_price_raw`, `fab_room_prices`.`price` AS `fab_plist___room_price`, `fab_plist`.`early_checkin` AS `fab_plist___early_checkin`, `fab_plist`.`early_checkin` AS `fab_plist___early_checkin_raw`, `fab_plist`.`late_checkout` AS `fab_plist___late_checkout`, `fab_plist`.`late_checkout` AS `fab_plist___late_checkout_raw`, `fab_plist`.`player_fees` AS `fab_plist___player_fees_raw`, `fab_fees`.`player_fees` AS `fab_plist___player_fees`, `fab_plist`.`total_price` AS `fab_plist___total_price`, `fab_plist`.`total_price` AS `fab_plist___total_price_raw`, `fab_plist`.`currency` AS `fab_plist___currency_raw`, `fab_currency`.`currency` AS `fab_plist___currency`, `fab_plist`.`id` AS slug , `fab_plist`.`id` AS `__pk_val` FROM `fab_plist` LEFT JOIN `#__users` AS `entr_users` ON `entr_users`.`id` = `fab_plist`.`create_user` LEFT JOIN `#__users` AS `entr_users_0` ON `entr_users_0`.`id` = `fab_plist`.`update_user` LEFT JOIN `fab_tournaments` AS `fab_tournaments` ON `fab_tournaments`.`id` = `fab_plist`.`tournament` LEFT JOIN `entr_users` AS `entr_users_1` ON `entr_users_1`.`username` = `fab_plist`.`assoc` LEFT JOIN `fab_players` AS `fab_players` ON `fab_players`.`player_id` = `fab_plist`.`player_id` LEFT JOIN `fab_types` AS `fab_types` ON `fab_types`.`id` = `fab_plist`.`hospitality` LEFT JOIN `fab_room_types` AS `fab_room_types` ON `fab_room_types`.`id` = `fab_plist`.`room` LEFT JOIN `fab_room_prices` AS `fab_room_prices` ON `fab_room_prices`.`id` = `fab_plist`.`room_price` LEFT JOIN `fab_fees` AS `fab_fees` ON `fab_fees`.`id` = `fab_plist`.`player_fees` LEFT JOIN `fab_currency` AS `fab_currency` ON `fab_currency`.`id` = `fab_plist`.`currency` WHERE ( fab_plist.tournament IN ('SELECT tournament_code FROM fab_organizers WHERE user_id = 33 AND code = 2014') OR fab_plist.tournament IN ('SELECT id FROM fab_tournaments') ) AND ( `fab_plist`.`tournament` IN ('9') ) ORDER BY `fab_plist`.`gender` ASC, `entr_users_1`.`username` ASC, `fab_plist`.`name` ASC

And this is the getData query when the pre-filters are not applied and I get results

Code: 
SELECT SQL_CALC_FOUND_ROWS DISTINCT `fab_plist`.`id` AS `fab_plist___id`, `fab_plist`.`id` AS `fab_plist___id_raw`, `fab_plist`.`create_date` AS `fab_plist___create_date`, `fab_plist`.`create_date` AS `fab_plist___create_date_raw`, `fab_plist`.`current_date` AS `fab_plist___current_date`, `fab_plist`.`current_date` AS `fab_plist___current_date_raw`, `fab_plist`.`create_user` AS `fab_plist___create_user_raw`, `entr_users`.`id` AS `fab_plist___create_user`, `fab_plist`.`update_user` AS `fab_plist___update_user_raw`, `entr_users_0`.`id` AS `fab_plist___update_user`, `fab_plist`.`tournament` AS `fab_plist___tournament_raw`, `fab_tournaments`.`tournament` AS `fab_plist___tournament`, `fab_plist`.`assoc` AS `fab_plist___assoc_raw`, `entr_users_1`.`username` AS `fab_plist___assoc`, `fab_plist`.`gender` AS `fab_plist___gender`, `fab_plist`.`gender` AS `fab_plist___gender_raw`, `fab_plist`.`player_id` AS `fab_plist___player_id_raw`, `fab_players`.`name` AS `fab_plist___player_id`, `fab_plist`.`name` AS `fab_plist___name`, `fab_plist`.`name` AS `fab_plist___name_raw`, `fab_plist`.`dob` AS `fab_plist___dob`, `fab_plist`.`dob` AS `fab_plist___dob_raw`, `fab_plist`.`year` AS `fab_plist___year`, `fab_plist`.`year` AS `fab_plist___year_raw`, `fab_plist`.`passport` AS `fab_plist___passport`, `fab_plist`.`passport` AS `fab_plist___passport_raw`, `fab_plist`.`cat` AS `fab_plist___cat`, `fab_plist`.`cat` AS `fab_plist___cat_raw`, `fab_plist`.`transport` AS `fab_plist___transport`, `fab_plist`.`transport` AS `fab_plist___transport_raw`, `fab_plist`.`arrival` AS `fab_plist___arrival`, `fab_plist`.`arrival` AS `fab_plist___arrival_raw`, `fab_plist`.`arrival_flight` AS `fab_plist___arrival_flight`, `fab_plist`.`arrival_flight` AS `fab_plist___arrival_flight_raw`, `fab_plist`.`departure` AS `fab_plist___departure`, `fab_plist`.`departure` AS `fab_plist___departure_raw`, `fab_plist`.`departure_flight` AS `fab_plist___departure_flight`, `fab_plist`.`departure_flight` AS `fab_plist___departure_flight_raw`, `fab_plist`.`hospitality` AS `fab_plist___hospitality_raw`, `fab_types`.`hosp_type` AS `fab_plist___hospitality`, `fab_plist`.`room` AS `fab_plist___room_raw`, `fab_room_types`.`room_type` AS `fab_plist___room`, `fab_plist`.`no_days` AS `fab_plist___no_days`, `fab_plist`.`no_days` AS `fab_plist___no_days_raw`, `fab_plist`.`room_price` AS `fab_plist___room_price_raw`, `fab_room_prices`.`price` AS `fab_plist___room_price`, `fab_plist`.`early_checkin` AS `fab_plist___early_checkin`, `fab_plist`.`early_checkin` AS `fab_plist___early_checkin_raw`, `fab_plist`.`late_checkout` AS `fab_plist___late_checkout`, `fab_plist`.`late_checkout` AS `fab_plist___late_checkout_raw`, `fab_plist`.`player_fees` AS `fab_plist___player_fees_raw`, `fab_fees`.`player_fees` AS `fab_plist___player_fees`, `fab_plist`.`total_price` AS `fab_plist___total_price`, `fab_plist`.`total_price` AS `fab_plist___total_price_raw`, `fab_plist`.`currency` AS `fab_plist___currency_raw`, `fab_currency`.`currency` AS `fab_plist___currency`, `fab_plist`.`id` AS slug , `fab_plist`.`id` AS `__pk_val` FROM `fab_plist` LEFT JOIN `#__users` AS `entr_users` ON `entr_users`.`id` = `fab_plist`.`create_user` LEFT JOIN `#__users` AS `entr_users_0` ON `entr_users_0`.`id` = `fab_plist`.`update_user` LEFT JOIN `fab_tournaments` AS `fab_tournaments` ON `fab_tournaments`.`id` = `fab_plist`.`tournament` LEFT JOIN `entr_users` AS `entr_users_1` ON `entr_users_1`.`username` = `fab_plist`.`assoc` LEFT JOIN `fab_players` AS `fab_players` ON `fab_players`.`player_id` = `fab_plist`.`player_id` LEFT JOIN `fab_types` AS `fab_types` ON `fab_types`.`id` = `fab_plist`.`hospitality` LEFT JOIN `fab_room_types` AS `fab_room_types` ON `fab_room_types`.`id` = `fab_plist`.`room` LEFT JOIN `fab_room_prices` AS `fab_room_prices` ON `fab_room_prices`.`id` = `fab_plist`.`room_price` LEFT JOIN `fab_fees` AS `fab_fees` ON `fab_fees`.`id` = `fab_plist`.`player_fees` LEFT JOIN `fab_currency` AS `fab_currency` ON `fab_currency`.`id` = `fab_plist`.`currency` WHERE ( `fab_plist`.`tournament` = '9' ) ORDER BY `fab_plist`.`gender` ASC, `entr_users_1`.`username` ASC, `fab_plist`.`name` ASC
 
I think if it's IN (SELECT...) it mustn't be quoted
WHERE ( fab_plist.tournament IN ('SELECT tournament_code FROM fab_organizers WHERE user_id = 33 AND code = 2014') OR fab_plist.tournament IN ('SELECT id FROM fab_tournaments')
 
The strange behaviour with the page refresh after filtering is gone - the reason was probably a very slow internet connection.

Chris
 
OK, thanks Chris.

Troester - yes, you are correct, and now I remember why we don't automatically quote the IN() condition stuff. :)

I'll modify that safeQuote() routine to check to see if it's a string that starts with "SELECT".

-- hugh
 
Since the last Github commit solved the issue for dimoss and me I am closing this thread.

Thanks to Hugh and Troester

Chris
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    No members online now.
    Total: 157 (members: 0, guests: 157)
    Back
    Top