Hi all,
I got a strange notification last night using hoptoad. For a rails app
running rails 2.3.5 on a mysql database.
I have written a search engine for a site that combines a few named
scopes. One of those named scopes is this;
named_scope :matching, lambda { |word| { :joins
=> :consumer, :conditions => [''(jobs.name like :word) OR
(jobs.description like :word) OR (users.zip_code like :word) OR
(users.city like :word)'', { :word => "%#{word}%"}]} }
All works fine and the statement above (in combination with another
default named scope) generates a nice sql statement;
SELECT `jobs`.* FROM `jobs` INNER JOIN `users` ON `users`.id `jobs`.consumer_id
AND (`users`.`type` = ''Consumer'' ) WHERE
(((`users`.locale = ''nl'') AND ((jobs.name like
''%test%'') OR
(jobs.description like ''%test%'') OR (users.zip_code like
''%test%'') OR
(users.city like ''%test%''))) AND (active = 1)) ORDER BY
auction_ends_at <= ''2010-02-26 09:25'',
`jobs`.updated_for_search_at
DESC LIMIT 0, 8
However; when a user would enter a dot in the string to search it all
goes wrong. The dot inside the search string, seems to blow up the
query like this;
SELECT `jobs`.`id` AS t0_r0, `jobs`.`consumer_id` AS t0_r1,
`jobs`.`name` AS t0_r2, `jobs`.`description` AS t0_r3,
`jobs`.`field_id` AS t0_r4, `jobs`.`auction_ends_at` AS t0_r5,
`jobs`.`materials_provided` AS t0_r6, `jobs`.`desired_first_day` AS
t0_r7, `jobs`.`price_type_id` AS t0_r8, `jobs`.`created_at` AS t0_r9,
`jobs`.`updated_at` AS t0_r10, `jobs`.`active` AS t0_r11,
`jobs`.`best_bid` AS t0_r12, `jobs`.`bid_count` AS t0_r13,
`jobs`.`updated_for_search_at` AS t0_r14, `jobs`.`highlight` AS
t0_r15, `jobs`.`featured_till` AS t0_r16, `jobs`.`thumbnail` AS
t0_r17, `jobs`.`expire_notification_sent` AS t0_r18,
`jobs`.`worker_id` AS t0_r19, `jobs`.`asap` AS t0_r20,
`jobs`.`worker_reminder_count` AS t0_r21,
`jobs`.`next_worker_reminder_at` AS t0_r22, `jobs`.`view_count` AS
t0_r23, `users`.`id` AS t1_r0, `users`.`username` AS t1_r1,
`users`.`email` AS t1_r2, `users`.`crypted_password` AS t1_r3,
`users`.`password_salt` AS t1_r4, `users`.`persistence_token` AS
t1_r5, `users`.`created_at` AS t1_r6, `users`.`updated_at` AS t1_r7,
`users`.`type` AS t1_r8, `users`.`first_name` AS t1_r9,
`users`.`last_name` AS t1_r10, `users`.`address` AS t1_r11,
`users`.`zip_code` AS t1_r12, `users`.`city` AS t1_r13,
`users`.`country` AS t1_r14, `users`.`mobile` AS t1_r15,
`users`.`company_name` AS t1_r16, `users`.`vat_number` AS t1_r17,
`users`.`terms_and_conditions` AS t1_r18, `users`.`show_in_dictionary`
AS t1_r19, `users`.`lat` AS t1_r20, `users`.`lng` AS t1_r21,
`users`.`show_mobile` AS t1_r22, `users`.`single_access_token` AS
t1_r23, `users`.`newsletter` AS t1_r24, `users`.`real_range` AS
t1_r25, `users`.`extend_range_till` AS t1_r26, `users`.`score` AS
t1_r27, `users`.`description` AS t1_r28, `users`.`url` AS t1_r29,
`users`.`avatar_file_name` AS t1_r30, `users`.`avatar_content_type` AS
t1_r31, `users`.`avatar_file_size` AS t1_r32,
`users`.`avatar_updated_at` AS t1_r33, `users`.`current_login_at` AS
t1_r34, `users`.`last_login_at` AS t1_r35, `users`.`locale` AS t1_r36,
`users`.`vat_requested` AS t1_r37, `users`.`is_legal` AS t1_r38,
`users`.`state_id` AS t1_r39, `users`.`fod_registered` AS t1_r40,
`users`.`fod_registration_number` AS t1_r41,
`users`.`fod_last_updated_at` AS t1_r42, `users`.`fod_last_checked_at`
AS t1_r43, `fields`.`id` AS t2_r0, `fields`.`name_nl` AS t2_r1,
`fields`.`name_fr` AS t2_r2, `fields`.`name_en` AS t2_r3,
`fields`.`created_at` AS t2_r4, `fields`.`updated_at` AS t2_r5,
`fields`.`jobs_count` AS t2_r6, `fields`.`aliases_nl` AS t2_r7,
`fields`.`aliases_fr` AS t2_r8, `fields`.`aliases_en` AS t2_r9,
`price_types`.`id` AS t3_r0, `price_types`.`name_nl` AS t3_r1,
`price_types`.`position` AS t3_r2, `price_types`.`created_at` AS
t3_r3, `price_types`.`updated_at` AS t3_r4, `price_types`.`name_fr` AS
t3_r5, `price_types`.`name_en` AS t3_r6, `price_types`.`short_code` AS
t3_r7 FROM `jobs` LEFT OUTER JOIN `users` ON `users`.id `jobs`.consumer_id AND
(`users`.`type` = ''Consumer'' ) LEFT OUTER JOIN
`fields` ON `fields`.id = `jobs`.field_id LEFT OUTER JOIN
`price_types` ON `price_types`.id = `jobs`.price_type_id INNER JOIN
`users` ON `users`.id = `jobs`.consumer_id AND (`users`.`type`
''Consumer'' ) WHERE (((`users`.locale = ''nl'')
AND ((jobs.name like
''%v.c.s.%'') OR (jobs.description like
''%v.c.s.%'') OR (users.zip_code
like ''%v.c.s.%'') OR (users.city like
''%v.c.s.%''))) AND (active = 1))
ORDER BY auction_ends_at <= ''2010-02-26 09:26'',
`jobs`.updated_for_search_at DESC LIMIT 0, 8
Currently I impletmented a small "hack" in the named scope, which
would replace a . with a \.;
{ :word => "%#{word.gsub(''.'',
''\.'')}%"}
Has anyone got any ideas on what may cause this behaviour?
Is it worth making a ticket in rails core lighthouse?
Stijn
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.