Convert Simple Query into tsvector & tsquery format.

Started by Adarsh Sharmaabout 15 years ago2 messagesgeneral
Jump to latest
#1Adarsh Sharma
adarsh.sharma@orkash.com

Dear all,

I have a simple query mentioned below :

select count(*) from page_content where (content like '%Militant%'
OR content like '%jihad%' OR content like '%Mujahid%' OR
content like '%fedayeen%' OR content like '%insurgent%' OR content
like '%terrORist%' OR
content like '%cadre%' OR content like '%civilians%' OR content like
'%police%' OR content like '%defence%' OR content like '%cops%' OR
content like '%crpf%' OR content like '%dsf%' OR content like '%ssb%')
AND (content like '%kill%' OR content like '%injure%');

I need to convert it into other format that use tsvector & tsquery for
Full-Text Searching.

I try and convert it like :

SELECT count(*) from page_content WHERE publishing_date like '%2010%' and
content_language='en' and content is not null and isprocessable = 1 and
to_tsvector('english',content) @@ to_tsquery('english','Mujahid' || ' | '

|| 'jihad' || ' | ' || 'Militant' || ' | ' || 'fedayeen' || ' | ' ||
'insurgent' || ' | ' || 'terrORist' || ' | ' || 'cadre'

|| ' | ' || 'civilians' || ' | ' || 'police' || ' | ' || 'cops' || ' |
' || 'crpf' || ' | ' || 'defence' || ' | ' || 'dsf' || ' | ' || 'ssb') ;

But not able to convert and use the condition

AND condition ( *AND (content like '%kill%' OR content like
'%injure%')* ) also.*

Please *help me , how to add this to the query.

Thanks & best Regards,
Adarsh Sharma

#2Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Adarsh Sharma (#1)
Re: Convert Simple Query into tsvector & tsquery format.

On Fri, 18 Mar 2011 12:30:50 +0530
Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

Dear all,

I have a simple query mentioned below :

select count(*) from page_content where (content like
'%Militant%' OR content like '%jihad%' OR content like
'%Mujahid%' OR content like '%fedayeen%' OR content like
'%insurgent%' OR content like '%terrORist%' OR
content like '%cadre%' OR content like '%civilians%' OR content
like '%police%' OR content like '%defence%' OR content like
'%cops%' OR content like '%crpf%' OR content like '%dsf%' OR
content like '%ssb%') AND (content like '%kill%' OR content like
'%injure%');

It's not clear where the problem is.

select to_tsquery('english', '(yellow | blue) & (red | black)');
to_tsquery
---------------------------------------------
( 'yellow' | 'blue' ) & ( 'red' | 'black' )

select to_tsvector('english', 'yellow red') @@ to_tsquery('english',
'(yellow | blue) & (red | black)');

The only thing I can see that could cause problems is you may have
previously "mangled" words in the ilike query while you'd leave that
task to ts engine that will find a proper lexeme.

Could you be more specific about the problem you're encountering.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it