[Resolved] Trouble with LIKE and AND...

angleiron

Heyo -

I've cribbed a few snippets of code here and there to try and build a relevancy-ranked search (from devshed, this site, and others), but I'm getting stuck on one part. I found one at devshed that sets up a search table that's populated with noise-filtered words that are associated with the article ID.

I've adapted it so that it works with wildcards to the left and right of the search terms. It even works with multiple terms. It works great. However, there's one problem. For multiple search terms, it's an OR search. I'd much rather it work like Google as an AND search. When I swap AND for OR, however, nothing is found. I've tried every combination of paranthesis I can think of. Here's the MySQL query:

SELECT count(search_table.word) as score, search_table.qid,articles.body_text WHERE articles.id = search_table.qid AND search_table.word LIKE '%term1%' OR articles.id = search_table.qid AND search_table.word LIKE '%term2%' GROUP BY search_table.qid ORDER BY score DESC

Basically, if I leave the OR in there, everything's sanguine. However, if I make it an AND, nothing gets returned. Any ideas? Much obliged! Again, to restate the obvious thing to try, I've tried every combination of parantheses I can think of.

BTW, this is my first post, but I've been reading up for a year. TIA.

barand

Shouldn't there be a FROM... somewhere between SELECT and WHERE ?

Try

... WHERE articles.id = search_table.qid AND ((search_table.word LIKE '%term1%') OR (search_table.word LIKE '%term2%')) ...

angleiron

Thanks, but I've tried exactly this, and yes, it does work, but again, I want the two search terms to be selected with AND rather than OR.

If I use AND instead of OR, it doesn't work.

Also, this query works okay without a FROM in there. Something about the count() command syntax. maybe?

Sxooter

OK, let me ask you a logic question.

Give me a list of all birds that are blue or red and have black feet.

Now, what does that question mean?

If a bird is blue, does it match? Or does it have to have black feet as well? Or does the AND only apply to red birds? Answer: We don't know.

Now, give me a list of all birds that are (blue or red) and have black feet.

I.e. you need to use parens to tell the SQL parser that you're ANDing a series of ORs.

select * from table where (condition OR condition, OR condition) AND (condition OR condition OR condition)

csn

AND has precedence over OR 😉 (I agree it's good to use parenthesis to make it clear).

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-syntax.html#SQL-PRECEDENCE