CodeSOD: Filter Overflow

Onboarding was complete, and Denise finally had her PC. Time for her first assignment!

"It's really pretty straightforward," her new boss had said, with a resigned look in his eye that betrayed it wasn't actually straightforward. "Your predecessor almost had this new CMS complete, we just need filtering on the frontend search."

It wasn't glamorous, but it was probably a good way to get her feet wet in a new organization. The requirement was to fetch a list of image IDs that corresponded with a business ID so that users only saw the images for their group. Simple, straightforward, obvious. She had it done inside of a few days and sent to the users for UAT.

And that's when the change requests started.

"Can you make it filter by state? Like, hidden images, public images, that sort of thing?" Sure, easy enough.

"What about filtering by upload date? Like if I gave you a date range?" Um, sure, but it'll add to the time...

Even after the filter was in production, the list of requests kept rolling in, bypassing the helpdesk and going right to her email inbox. "We're getting a lot of spam images, can we filter so only verified users' uploads show up?"

Denise's boss told her it'd be over faster if she just gave in and made the change. Management would ultimately decide to do it after wasting her time with meeting after meeting to discuss priority anyway.

"Can we put videos in the CMS?"

"Can we filter by tags?"

"I need to filter images that are actually images."

"Can we sort on the tags?"

How do you sort on tags? Denise wondered. Apparently, by their business IDs. Sure. Whatever.

"I'm still getting images that shouldn't be showing up. Can we filter for only images that show up?"

"There's too many images, can I limit it to ten per ID?"

Finally, she ended up with this giant Ruby monstrosity:

filter(ext_id: ext_ids, type_id: type_id) .filter_state(params['state'] || 'public') .filter_created_at(params['start_date'], params['end_date']) .default_filters(params) .left_outer_join(:image_tags, image_relations_id: :id) .select { columns.map { |x| "image_relations__#{x}".intern } + [ COLLECT(:name).cast(:'VARCHAR2_NT').as(:tags), MIN( [ [{ image_tags__name: 'profile' }, 1], [{ image_tags__name: 'cover' }, 2], [{ primary: 1 }, 3] ].case(4) ).as(:sort_priority) ] } .where { ( { recurring: 0 } & (start_date time) ) | ( { recurring: 1 } & ( ( (to_char(end_date, 'MMDD') >= to_char(start_date, 'MMDD')) & (to_char(start_date, 'MMDD') = time.strftime('%m%d')) ) | ( (to_char(end_date, 'MMDD') = time.strftime('%m%d')) ) ) ) ) } .group(columns.map { |x| "image_relations__#{x}".intern }) .from_self .select { columns + [ :tags, ROW_NUMBER( :over, partition: :ext_id, order: [:sort_priority, *column] ) { }.as(:partition_row_number) ] } .from_self .select { columns + [:tags] } .where { (partition_row_number > offset) & (partition_row_number 

(Note: there are start_date and end_date parameters to filter on, but also, start_date and end_date columns in the table for deducing if the image is valid or not.)

Finally, enough was enough. Seeing a lateral position open up in another team, Denise went for it. Now, at least, it'd be her successor's problem to cater to the scope avalanche.

[Advertisement] Release!is a light card game about software and the people who make it. Play with 2-5 people, or up to 10 with two copies - only $9.95 shipped!