The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
OpenInteract-1.99_00
River stage zero No dependents
/ OpenInteract2::Action::CommonSearch

NAME

OpenInteract2::Action::CommonSearch - Implement searching functionality for SPOPS::DBI-based objects

SYNOPSIS

 # Just subclass and the tasks 'search_form' and 'search' are
 # implemented!
 
 package OpenInteract2::Action::MyAction;
 
 use strict;
 use base qw( OpenInteract2::Action::CommonSearch );
 
 # In your action configuration:
 
 [myaction]

SUPPORTED TASKS

This common action supports two tasks, explained in detail below:

search_form

Display a form for searching an object.

search

Collect search criteria from the user, build a query against an object and return results

DESCRIPTION FOR 'search_form'

This is a very simple action -- all we really do is get the specified template and display it.

TEMPLATES USED FOR 'search_form'

c_search_form_template

Fully qualified template name for your search form. If undefined you'll get the standard error page.

METHODS FOR 'search_form'

No additional methods are available for you to override.

CONFIGURATION FOR 'search_form'

None. All you need to do is specify the template name as mentioned above.

DESCRIPTION FOR 'search'

This task builds a SQL query from the given search criteria and returns the results as an iterator, just in case you accidentally selected 1000 records.

It also supports stepping through the search results a page at a time -- just set the c_search_results_paged parameter to 'yes'. When you're generating URLs to page through searches, you should only need to pass the following parameters after the first search:

  • search_id: ID of the search you're requesting.

  • page_num: Page of the result set you'd like to view.

TEMPLATES USED FOR 'search'

c_search_results_template

Fully qualified template name for your search results. If undefined you'll get the standard error page.

The parameters passed to your template are different depending on the value of c_search_results_paged (see below).

If it is set to 'yes', you'll get the following::

  • iterator: An SPOPS::Iterator with your search results.

  • page_num: Page of the results we're currently on.

  • total_pages: The total number of pages in the result set.

  • total_hits: The total number of hits in the result set.

  • search_id: The ID of this search.

If it is set to no, you'll get:

METHODS FOR 'search'

_search_additional_params() (\%)

If you want to pass additional parameters directly to the SPOPS::DBI fetch_iterator() call, return them here. For instance, if you want to skip security for a particular search you would create:

 sub _search_additional_params {
     return { skip_security => 1 };
 }

Default: undef (no parameters)

_search_criteria_customize()

If you'd like to modify the search criteria after it's been read in from the user but before it's been translated to SQL and executed, override this method. You have access to the parameter 'c_search_critieria', a hashref of fields to values searched for. The value can be a scalar or an arrayref, depending on how many values the user submitted.

For instance, you can play nasty with your users and ensure that when a certain search term is entered they get something entirely different:

 sub _search_criteria_customize {
     my ( $self ) = @_;
     my $criteria = $self->param( 'c_search_criteria' );
     if ( $critieria->{full_name} eq 'Bill Gates' ) {
         $criteria->{full_name} = 'Larry Wall';
     }
 }

_search_query_customize()

This is called after the pieces for the query have been built but not yet put together to create the query. You have the opportunity to modify the parameters:

  • c_search_query_tables - an arrayref of the tables used

  • c_search_query_where - an arrayref of the sections to be used in the WHERE clause that will eventually be joined by 'AND' later in the process.

  • c_search_query_values - an arrayref of the values to be plugged into placeholders from c_search_query_where.

So if you wanted to set a value depending on multiple values you might do something like this:

 sub _search_query_customize {
     my ( $self ) = @_;

     # Our query operator depends on $date_type...

     my $request = CTX->request;
     my $date_type = $request->param( 'date_order' );
     my $date_search = $request->param_date( 'filter_date' );

     # Don't do anything unless both are defined

     return unless ( $date_type and $date_search );
     my $where = $self->param( 'c_search_query_where' )  || [];
     my $value = $self->param( 'c_search_query_values' ) || [];

     # ...now define the different operators

     if ( $date_type eq 'after' ) {
         push @{ $where }, 'object_time >= ?';
     }
     elsif ( $date_type eq 'before' ) {
         push @{ $where }, 'object_time <= ?';
     }

     # ... but the value is the same

     push @{ $value }, $date_search;

     # Now reset the parameters to the new values, just in case they
     # were previously undefined

     $self->param( c_search_query_where  => $where );
     $self->param( c_search_query_values => $value );
 }

_search_customize( \%template_params )

This is called just before we generate the content. You're passed a hashref of the parameters that will be passed to the template, and you can modify them as needed. Typically you'll use this to pass additional parameters to the template.

CONFIGURATION FOR 'search'

These are in addition to the template parameters defined above.

Basic

c_object_type ($) (REQUIRED)

SPOPS key for object you'll be searching. You can build a search that spans tables from other objects, but you still have to return a single type of object. (See OpenInteract2::Common.)

Specifying search fields

In these configuration entries you're presenting a list of fields used to build a search. This can include fields from other tables. Fields from other tables must be fully-qualified with the table name.

For instance, for a list of fields used to find users, I might list:

 c_search_fields_like = login_name
 c_search_fields_like = last_name
 c_search_fields_like = group.name

Where 'group.name' is a field from another table. I would then have to configure c_search_table_links (below) to tell the query builder how to link my object with that table.

These are the actual parameters from the form used for searching. If the names do not match up, such as if you fully-qualify your names in the configuration but not the search form, then you will not get the criteria you think you will. An obvious symptom of this is running a search and getting many more records than you expected, maybe even all of them.

To be explicit -- in the HTML page corresponding to the above example you should have something like:

 Group Name: <input type="text" name="group.name">

c_search_fields_like ($ or @)

Zero or more fields to search using 'LIKE' and a wildcard '%' on both sides of the search value.

Example:

 login name LIKE '%foo%'

c_search_fields_exact ($ or @)

Zero or more fields to search using '=', no wildcards.

Example:

 login name = 'foo'

c_search_fields_left_exact

Zero or more fields to search using 'LIKE' and a wildcard '%' on the right-hand side of the search value, thus finding all objects where the given value matches the beginning of the object field.

Example:

 login name = 'foo%'

c_search_fields_right_exact

Zero or more fields to search using 'LIKE' and a wildcard '%' on the left-hand side of the search value, thus finding all objects where the given value matches the end of the object field. (This isn't used very often.)

Example:

 login name = '%foo'

Linking tables for searches

c_search_table_links (\%)

Maps zero or more table names to the necessary information to build a WHERE clause that joins the relevant tables together on the proper fields.

Note: This discussion may seem confusing but it can be extremely useful: for instance, if you want to search by a person's city but the address information is in a separate table. If we stuck to the one-object/one-table mentality then you'd have to break normalization or some other hack.

The values assigned to each table name enable us to build a join clause to link our table (the one with the object being searched) to the table in the key. So we have two pieces to the puzzle: the 'FROM' (our object) and the 'TO' (the related object).

There are two possibilities for the configuration:

Configuration 1: Objects matched by fields

Example: Assume we have a 'person' table (holding our searchable object) and an 'address' table. We want to find all people by the 'address.city' field.

 [person c_search_table_links]
 address = person.person_id
 address = address.person_id

So we're saying that to link our object ('person') to another object ('address'), we just find all the 'address' objects where the 'person_id' field is a particular value. This is the classic one-to-many relational mapping.

Here's what the statement might look like:

 SELECT (person fields)
   FROM person, address
  WHERE address.city = 'foo'
        AND person.person_id = address.person_id

Another example: Assume we have a 'phone_log' table (holding our searchable object) and a 'person' table. We want to find all phone log records for people by last name.

 [phone_log c_search_table_links]
 person = phone_log.person_id
 person = person.person_id

This is the same as the first example but demonstrates that you can use non-key fields as well as key fields to specify a relationship.

Here's what the statement might look like:

 SELECT (phone_log fields)
   FROM phone_log, person
  WHERE person.last_name = 'foo'
        AND phone_log.person_id = person.person_id

Configuration 2: Objects linked by a third table

Example: Assume we have a 'user' table (holding our searchable object), a 'group' table and a 'group_user' table holding the many-to-many relationships between the objects. We want to find all users in a particular group.

 [user search_table_links]
 group = user.user_id
 group = group_user.user_id
 group = group_user.group_id
 group = group.group_id

This is fundamentally the same as the other two examples except we've chained two relationships together:

    FROM                 TO
 1. user.user_id         group_user.user_id
 2. group_user.group_id  group.group_id

So searching for a user by a group name with 'admin' would give:

 SELECT (user fields)
   FROM user, group, group_user
  WHERE group.name = 'admin'
    AND group.group_id = group_user.group_id
    AND group_user.user_id = user.user_id

Default: empty hashref

Other query modifications

c_search_results_order ($)

An 'ORDER BY' clause (without the 'ORDER BY') used to order your results. The query builder makes sure to include the fields used to order the results in the SELECT statement, since many databases will complain about their absence.

Note that in addition to declaring this statically you can dynamically add this in _search_query_customize().

Default: none

Paging/capping results

c_search_results_paged (boolean)

Do you want your search results to be paged ('yes') or do you want them returned all at once ('no')?

Default: 'yes'

c_search_results_page_size ($)

If c_search_results_paged is set to 'yes' we output pages of this size.

Default: 50

c_search_results_cap ($)

Constrains the max number of records returned. If this is set we run a 'count(*)' query using the search criteria before running the search. If the result is greater than the number set here, we call the task specified in c_search_results_cap_fail_task with an error message set in the normal manner about the number of records that would have been returned.

Note that this is a somewhat crude measure of the records returned because it does not take into account security checks. That is, a search that returns 500 records from the database could conceivably return only 100 records after security checks. Keep this in mind when setting the value.

Default: 0 (no cap)

Tasks to execute on failure

c_search_fail_task ($)

Task to run if your search fails. The action parameter 'error_msg' will be set to an appropriate message which you can display.

Default: 'search_form'

c_search_results_cap_fail_task ($)

Task to run in this class when a search exceeds the figure set in c_search_results_cap. The task is run with a relevant message in the 'error_msg' action parameter.

Default: 'search_form'

System-created parameters

These are created by the action when it's first initialized and during the search task.

c_object_class ($)

Set to the class corresponding to c_object_type. This has already been validated.

c_search_fields ($ or @)

Zero or more fields that users can search by. This includes all fields from c_search_fields_like, c_search_fields_exact, c_search_fields_left_exact, c_search_fields_right_exact.

c_search_criteria (\%)

These are the criteria built-up during the search process. You can change them by overriding _search_criteria_customize() and modifying the parameter.

c_search_query_tables (\@)

List of the tables used in a search.

c_search_query_where (\@)

List of the clauses to be used in a WHERE clause of a search. Will be joined together with 'AND' on submitting to the engine.

c_search_query_values (\@)

Values to be plugged into the placeholders specified in c_search_query_where.

c_search_query_limit ($)

The limit clause -- either a single number, which indicates the number of items to get from the beginning, or two numbers separated by a comma, which indicates the range of items to get.

TO DO

Modify page size on the fly

Allow the incoming URL to define page size as well as the page number. (Default page size still set in the action.) If a user sets this it should be saved in her session (or a cookie?) so it's sticky.

COPYRIGHT

Copyright (c) 2003 Chris Winters. All rights reserved.

AUTHORS

Chris Winters <chris@cwinters.com>