in reply to Querying 2 Values Form 1 Column in DBIx::Class

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.

Replies are listed 'Best First'.
Re^2: Querying 2 Values Form 1 Column in DBIx::Class
by phildeman (Scribe) on Jan 26, 2018 at 21:41 UTC

    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]
        ... to avoid the LIKE operator where INSTR is sufficient

        Some databases do even better: in postgres there is the option to index on regular expressions (or any string) via the pg_trgm contrib extension.

        Its disadvantage is huge index size but the gain can be enormous, as indeed in this, the OP's example: 

        -- table t is 112 MB, 1 million rows.  The index is 190 MB (!)
-- all queries measured on second run (to avoid uninteresting cold cac
+he slowness)

-- no index:
SELECT * FROM t WHERE name like '%Franklin%' and name like '%Linsey%';
Time: 118.004 ms

SELECT * FROM t WHERE position('Franklin' in name)>0 AND position('Lin
+sey' in name)>0;
Time: 444.716 ms

create index t_trgm_re_idx on t using gin (name gin_trgm_ops);

-- with index:
SELECT * FROM t WHERE name like '%Franklin%'  and name like '%Linsey%'
+ ;
Time: 1.582 ms

-- postgres specific regex-search syntax with '~' 
SELECT * FROM t WHERE name ~ 'Franklin' and name ~ 'Linsey';
Time: 1.895 ms

        [download]

        The latter syntax has the added advantage that one can use regex like 'Frankl[iy]n' (it will use the trgm index as well).

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom