http://qs321.pair.com?node_id=1207967

phildeman has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,

I am having trouble trying to create query, using DBIx::Class, where 2 different values may exist in one column.
When I run the following query from SQL Server, all works well;

SELECT *
FROM People
WHERE Name like '%Franklin%' and Name like '%Linsey%'

[download]

However, I tried writing a script with DBIx::Class, with the following:

@results = $schema->resultset( 'Peoplel' )->search({
             Name => { like => '%Franklin%' },
             Name => { like => '%Linsey'%' }
          }
 );

[download]

The problem here is the the column name is a hash key, so the second hash key
overwrites the value of the first hash key.

Is there a way to produce the results I want based on the SQL statement above?

Thanks.

-Phil-

Replies are listed 'Best First'.
Re: Querying 2 Values Form 1 Column in DBIx::Class
by poj (Abbot) on Jan 26, 2018 at 19:29 UTC
[reply]
[d/l]

      Thanks poj,

      That is exactly what I was looking for!

      I finally got it working using the code below, but your way is cleaner and less code!</p 

      my $results = $schema->resultset( 'People' )->search({
            Name => {  like => '%Franklin%' }
      },

if($results) {
     $results = $results->search( { Name => {like => '%Linsey%' } } );
}

my @result_objs = $results->all;

      [download]

      Thanks again!

      -Phil-

[reply]
[d/l]
Re: Querying 2 Values Form 1 Column in DBIx::Class
by Your Mother (Archbishop) on Jan 26, 2018 at 19:31 UTC

    Untested but if I understand the question (your examples make it odd... you want OR, I think, not AND), this should do–

    my @results = $schema->resultset('People1')
    ->search({ -or => [ Name => { like => '%Franklin%' },
                        Name => { like => '%Linsey%' } ] });

    [download]

    Update, I understand now. To get AND, just s/-or/-and/ as poj showed. Both orders/nesting of syntax should work fine in this simple case.

[reply]
[d/l]
[select]

      Your Mother,

      Thanks for your response. Actually, I need AND. The scenario is to search
      the Name field. Since we are unable to control what the user types in search
      field. If the user was searching for Franklin Linsey Smith, but typed in
      any of the following words:

      Franklin Linsey
      Smith Franklin
      Franlin Smith
      Franklyn Linsey Smith
      Smith Linsey

      I needed the result set to include records with the search criteria, above.
      Using OR would return a much larger result set than I was looking for.
      So, AND helps narrow the search.

      Thanks again.

      -Phil-

[reply]
        SELECT *
FROM People
WHERE Name like '%Franklin%' and Name like '%Linsey%'

        [download]

        Actually, I need AND. [...] Using OR would return a much larger result set than I was looking for. [...] So, AND helps narrow the search.

        Just a thought: You might get the same result with OR by changing the search terms.

        SELECT *
FROM People
WHERE Name LIKE '%Franklin%Linsey%' OR Name LIKE '%Linsey%Franklin%'

        [download]

        I have no idea what difference in performance this will get out of your database. I'm quite sure there will be a difference. Talk to your DB admin.

        Another thing that I've learned from an Oracle guru is to avoid the LIKE operator where INSTR (other databases may call it POSITION or STRPOS) is sufficient. The reason behind that is that it is quite easy for an RDBMS to optimize (i.e. create an index) for a substring search, but LIKE most times requires a slow full table scan, operating on a search pattern. Again, talk to your DB admin.

        SELECT *
FROM People
WHERE INSTR(Name,'Franklin')>0 AND INSTR(Name,'Linsey')>0

        [download]

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom