0

I've been struggling to find the correct way to format my mysql expression to match against a related keyword table.

The tables are set up as follows:

photos: id,path,various exif data
photos_to_tags: photos_id,photos_tags_id
photo_tags: id,name

The SQL is as follows:

SELECT DISTINCT (photos.path),photos.name, 
MATCH (photo_tags.name) AGAINST('+bamboo* +flooring* ') AS relevance 
FROM photo_tags 
LEFT JOIN photos_to_tags ON photo_tags_id = photo_tags.id 
LEFT JOIN photos ON photos.id = photos_id 
WHERE (MATCH (photo_tags.name) AGAINST('+bamboo* +flooring* ' IN BOOLEAN MODE)) 
  OR photo_tags.name = 'bamboo flooring' 
GROUP BY path 
ORDER BY relevance 
LIMIT 0,24;

It is not returning results unless the exact phrase "bamboo flooring" is a tag. If 'bamboo' is a tag, and 'flooring' is a tag, then it won't return results.

Some more elaboration to further describe the problem: This is for a contractor's web site. He has hundreds of photos of work. He may have 30 roofing photos, some of the roofing photos may be steel roofs, others may be cedar shake, lifetime, etc.

The keyword tags are in a separate table, and they can be used to list all photos that are of roofing, or the search can be used to further isolate only the steel roofs, or perhaps steel roof with trim details, etc.

There may be another way to write this query-- I have not been able to find a good way to return relevant results. For example, if someone searches for lifetime roof, they don't want to see steel roof photos.

Thanks!

Jason Silver
  • 527
  • 7
  • 23
  • I don't know anything about `MATCH`, so I can't help you with that, but I am wondering why you have left joins here? I'd think you'd want inner joins, no? Otherwise, you may get a bunch of `NULL`s in your result set, which don't seem useful. – Brandon Oct 13 '14 at 15:21

1 Answers1

0

"It is not returning results unless the exact phrase "bamboo flooring" is a tag"-

Yeah it will not return as you wrote '+'(that means it is required to be present) in front of both. Read Mysql Devsite to know how it works.

you can also write like this: for photos without the 'bamboo' tag would return no results.

SELECT DISTINCT (photos.path),photos.name, FROM photo_tags 
INNER JOIN photos_to_tags ON photo_tags_id = photo_tags.id INNER JOIN 
photos ONphotos.id=photos_id WHERE photo_tags.name LIKE '%bamboo%' // 'bamboo' tag should be there or the search text..
GROUP BY path , photos.name 
LIMIT 0,24;
Riad
  • 3,822
  • 5
  • 28
  • 39
  • hmm, that link is broken. – Jason Silver Oct 13 '14 at 18:12
  • Now it is ok.. just appended the text.. :( – Riad Oct 13 '14 at 18:16
  • The problem I'm having is that no operators returns flooring hits, but not necessarily bamboo hits I would think that bamboo flooring should be the first results returned, and photos without the bamboo tag would return no results. – Jason Silver Oct 13 '14 at 18:29
  • could not get what you exactly want to see as result... you asked for not getting result without the "full tag" which is because of the + operator into the parenthesis of "againt". Please update the question and clarify details. – Riad Oct 13 '14 at 18:45
  • OK, I really think it's pretty clear, but I've given some more detail in my question. – Jason Silver Oct 13 '14 at 18:51
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM photo_tags INNER JOIN photos_to_tags ON photo_tags_id = photo_tags.id INN' at line 1 – Jason Silver Oct 26 '14 at 13:15