ts_tovector() to_query()

"Severn, Chris" <chris_severn@chernay.com> wrote:

I have a statement that is like this

SELECT m.* FROM movies m
   WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop|DVD|Collection')

this works, but it correctly returns all the matching records
that have any of the query items in them.

What I want to do is return items that have 'Robocop' or 'Robocop
and DVD' or 'Robocop and Collection' or 'Robocop and DVD and
collection'

is there an operator for the tsvector / tsquery relationship that
will do this?  Or am I forced to sift through the results of the
initial query after the fact?

SELECT m.* FROM movies m
  WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop & (DVD | Collection)')

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Mar 28, 2013 at 2:12 PM, Kevin Grittner <kgrittn@ymail.com> wrote:

What I want to do is return items that have 'Robocop' or 'Robocop
and DVD' or 'Robocop and Collection' or 'Robocop and DVD and
collection'

SELECT m.* FROM movies m
WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop & (DVD | Collection)')

It wont return items that have 'Robocop' entry only.

[local]:5432 postgres@postgres=#
select to_tsvector('robocop') @@ to_tsquery('robocop & (dvd | collection)');
?column?
----------
f
(1 row)

But to_tsquery('robocop | (robocop & (dvd | collection))') will do the trick.

[local]:5432 postgres@postgres=#
select to_tsvector('robocop') @@ to_tsquery('robocop | (robocop & (dvd
| collection))');
?column?
----------
t
(1 row)

--
Kind regards,
Sergey Konoplev
Database and Software Consultant

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Mar 28, 2013 at 08:50:50PM +0000, Severn, Chris wrote:

What I want to do is return items that have 'Robocop' or 'Robocop and
DVD' or 'Robocop and Collection' or 'Robocop and DVD and collection'

Based on the criteria above, I would say that:
SELECT m.* FROM movies m WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop')

will do what you need, since "dvd" and "collection" are irrelevant for
the results.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Because the query is what the user is typing in. I don't know what words the user is going to search for. if they simply search for 'Robocop' that would work. But how do I handle the search if they type in more than one word and still return half way accurate results?

I suppose after talking through it a bit, this may be more of a ranking issue than an actual query issue. I will read up more on ranking in the postgres docs. I skimmed past it since it didn't seem to apply, but now looking over it again, I think it could help.

Thanks.
Chris

-----Original Message-----
From: depesz@depesz.com [mailto:depesz@depesz.com]
Sent: Friday, March 29, 2013 7:59 AM
To: Severn, Chris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ts_tovector() to_query()

On Thu, Mar 28, 2013 at 08:50:50PM +0000, Severn, Chris wrote:

What I want to do is return items that have 'Robocop' or 'Robocop and
DVD' or 'Robocop and Collection' or 'Robocop and DVD and collection'

Based on the criteria above, I would say that:
SELECT m.* FROM movies m WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop')

will do what you need, since "dvd" and "collection" are irrelevant for the results.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general