Hi All

I am trying to build a where clause to use in a DBIx::Class query.

Scenario:
A user can search for a name by typing in one or more words in a search field. Multiple words will be separated by a blank space.
If the search field contains multiples words the application will split the search term into an array, then use the array to build the where
clause. Note: A name can be made up of many words (ie: Orlando Miguel Jose Fernando). So, I want to be able to use the each
of the search term words to build the where clause.

The SQL query would look like so:
SELECT *
FROM People
WHERE Name like "%Orlando%' AND
Name like '%Miguel%' AND
Name like '%Jose%' AND
Name like '%Fernando%

The order of the words in the where clause is irrelevant, since it
will still return a result set that matches that condition, if the record(s) exist.

If I manually write out the DBIx::Class code, it would appear as follows:

my @results = $schema->( 'People' )->search({
         Name => [ -and => { like => '%Orlando%' },            
                           { like => '%Miguel%' },
                           { like => '%Jose%' },
                           { like => '%Fernando%' } ]
          } );

[download]

After I split the search term into an array, how can I build the where clause with each item,
to represent the where clause in the DBIx::Class query, above? I attempted many ways with no
success. The code would look something like this:

if($search_term =~ /\s+/) {
     my @search_term_array = split /\s+/, $search_term;

     my $whrcls:
     foreach my $searchitem (@search_term_array) {
          $whrcls = ......
           .....
           .....
     }
}

my @results = $schema->( 'People' )->search({
         Name => [ -and => $whrcls ]
       } );

[download]

I know I am missing something, after searching via Google, I am at a dead end.
Any ideas or a document you can direct me to?

Thanks

-Phil-

In reply to Trying to build a where clause to insert into a DBIx::Class Query by phildeman

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.