Thread

1. Search (select) options

   Jeff Davis <jdavis@genesiswd.com> — 2000-08-16T04:05:55Z

   I would like to be able to use searches that seem somewhat intelligent.
   Can you 'ORDER BY' number of matching 'OR' clauses? For example, someone
   searches for "x y z", so I would do "select * from mytable where col1
   like '%x%' or col1 like '%y%' or col1 like '%z%';", but I want it to
   order by number of matches (so a match of y and z would turn up before a
   match of just x).
   
   If anyone has suggestions, or can point me to some reading, I would
   really appreciate it. The only thing I can think of is a complicated
   application-side program.
   
   Thanks,
   Jeff Davis
   
   
   

2. Re: Search (select) options

   Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> — 2000-08-16T05:04:28Z

   Jeff Davis wrote:
   > 
   > I would like to be able to use searches that seem somewhat intelligent.
   > Can you 'ORDER BY' number of matching 'OR' clauses? For example, someone
   > searches for "x y z", so I would do "select * from mytable where col1
   > like '%x%' or col1 like '%y%' or col1 like '%z%';", but I want it to
   > order by number of matches (so a match of y and z would turn up before a
   > match of just x).
   > 
   > If anyone has suggestions, or can point me to some reading, I would
   > really appreciate it. The only thing I can think of is a complicated
   > application-side program.
   
   
   chrisb=# create table t(a text, b text, c text);
   CREATE
   chrisb=# insert into t values(null, 'x', null);
   INSERT 18955 1
   chrisb=# insert into t values(null, 'x', 'x');
   INSERT 18956 1
   chrisb=# insert into t values(null, 'x', null);
   INSERT 18957 1
   chrisb=# insert into t values(null, 'x', 'x');
   INSERT 18958 1
   chrisb=# insert into t values('x', 'x', 'x');
   INSERT 18959 1
   chrisb=# insert into t values(null, null, null);
   INSERT 18960 1
   chrisb=# select * from t;
    a | b | c 
   ---+---+---
      | x | 
      | x | x
      | x | 
      | x | x
    x | x | x
      |   | 
   (6 rows)
   
   chrisb=# select *, case when a='x' then 1 else 0 end + case when b='x'
   then 1 else 0 end + case when c='x' then 1 else 0 end as match from t
   order by match;
    a | b | c | match 
   ---+---+---+-------
      |   |   |     0
      | x |   |     1
      | x |   |     1
      | x | x |     2
      | x | x |     2
    x | x | x |     3
   (6 rows)
   
   chrisb=# select *, case when a='x' then 1 else 0 end + case when b='x'
   then 1 else 0 end + case when c='x' then 1 else 0 end as match from t
   order by match desc;
    a | b | c | match 
   ---+---+---+-------
    x | x | x |     3
      | x | x |     2
      | x | x |     2
      | x |   |     1
      | x |   |     1
      |   |   |     0
   (6 rows)
   
   

3. Re: Search (select) options

   Highway80 dude <pgsql_general@highway80.net.au> — 2000-08-16T10:44:44Z

   Perhaps you can use the UNION statement like so...
   
   SELECT * FROM mytable WHERE col1='x'
   UNION
   SELECT * FROM mytable WHERE col1='y'
   UNION
   SELECT * FROM mytable WHERE col1='z'
   
   Although, I have never tried stacking them before so I don't know if the 
   above will work (with more than one UNION).
   
   Of course, this means there are 3 separate select statements instead of 
   your 1 but perhaps it isn't as inefficient as it sounds since pg should 
   process the whole transaction in the one commit.
   
   Jeff Davis wrote:
   > 
   > I would like to be able to use searches that seem somewhat intelligent.
   > Can you 'ORDER BY' number of matching 'OR' clauses? For example, someone
   > searches for "x y z", so I would do "select * from mytable where col1
   > like '%x%' or col1 like '%y%' or col1 like '%z%';", but I want it to
   > order by number of matches (so a match of y and z would turn up before a
   > match of just x).
   > 
   > If anyone has suggestions, or can point me to some reading, I would
   > really appreciate it. The only thing I can think of is a complicated
   > application-side program.
   > 
   > Thanks,
   > Jeff Davis
   > 
   > 
   > 
   
   
   

4. Re[2]: Search (select) options // Max SQL length?

   Barry Hill <barry@penrallt.clara.co.uk> — 2000-08-17T16:22:20Z

   Hello,
   
   Wednesday, August 16, 2000, 11:44:44 AM, you wrote:
   
   Hd> Perhaps you can use the UNION statement like so... [...]
   Hd> Although, I have never tried stacking them before so I don't know if the
   Hd> above will work (with more than one UNION).
   
   UNION works (fortunately) with 100 SELECTs at least!
   
   Although when I forgot to close the DB connection (PHP) I got an ugly
   "out of file descriptors" error on the server (and a DOS for about 5
   mins until I could ssh in) as each table (with indexes) required 8
   file descriptors and my box only had 4096 free. 
   
   
   Does anyone know how long a PostGRES SQL statement can be? In the near
   future, my above-mentioned query will grow to a few hundred SELECTS
   (Phorum) and I'm wondering how it will cope (but too lazy to
   experiment) ???
   
   
   
   Best regards,
   
    Barry                            mailto:barry@penrallt.clara.co.uk
   
   
   
   

5. Re: Re[2]: Search (select) options // Max SQL length?

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-19T05:09:24Z

   Barry Hill <barry@penrallt.clara.co.uk> writes:
   > Does anyone know how long a PostGRES SQL statement can be? In the near
   > future, my above-mentioned query will grow to a few hundred SELECTS
   > (Phorum) and I'm wondering how it will cope (but too lazy to
   > experiment) ???
   
   As of 7.0 there's no hard upper limit.  UNIONing a few hundred SELECTs
   might take longer than you really want to wait however ... perhaps some
   rethinking of your data model is in order.
   
   			regards, tom lane