Обсуждение: Search (select) options

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

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)

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
>
>
>

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

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