MySQL: Shouldn't this return more rows?
I'm running a simple LIKE search on a table but i can't seem to get more than one result for some reason. In the query example below i include words like "never back down" and "soul surfer" but i only get one of the movies. I have those movies in my database, alo开发者_StackOverflow社区ng with many others.
Here's the query:
SELECT movies . * , GROUP_CONCAT( categories.name ) AS categories, GROUP_CONCAT( categories.id ) AS categories_id
FROM movies
LEFT JOIN movies_categories ON ( movies.id = movies_categories.movie_id )
LEFT JOIN categories ON ( movies_categories.category_id = categories.id )
WHERE movies.movie_title LIKE '%surfer%'
OR movies.movie_title LIKE '%date%'
OR movies.movie_title LIKE '%never%'
OR movies.movie_title LIKE '%back%'
OR movies.movie_title LIKE '%down%'
OR movies.movie_title LIKE '%surfer%'
OR movies.movie_title LIKE '%soul%'
OR movies.movie_title LIKE '%the%'
OR movies.movie_title LIKE '%transporter%'
LIMIT 0 , 30
I was thinking there must be something wrong with the query, otherwise it would return all results? I am aware some of the words are duplicated in the like clauses, but that shouldn't matter. Have i missed something?
Fixed it!
The group_concat() functions i used caused the query to only return one result, and concatenate the categories and category_id's for all movies. I just removed the group_concat()'s and it works now.
You are using GROUP_CONCAT
which is an aggregate function and collapses your results into one.
Add a GROUP BY
clause:
SELECT movies . * , GROUP_CONCAT( categories.name ) AS categories, GROUP_CONCAT( categories.id ) AS categories_id
FROM movies
LEFT JOIN movies_categories ON ( movies.id = movies_categories.movie_id )
LEFT JOIN categories ON ( movies_categories.category_id = categories.id )
WHERE movies.movie_title LIKE '%surfer%'
OR movies.movie_title LIKE '%date%'
OR movies.movie_title LIKE '%never%'
OR movies.movie_title LIKE '%back%'
OR movies.movie_title LIKE '%down%'
OR movies.movie_title LIKE '%surfer%'
OR movies.movie_title LIKE '%soul%'
OR movies.movie_title LIKE '%the%'
OR movies.movie_title LIKE '%transporter%'
GROUP BY
movies.id
LIMIT 0 , 30
Also, if your movies
table is MyISAM
, you can run this:
SELECT movies.*,
GROUP_CONCAT(categories.name) AS categories,
GROUP_CONCAT(categories.id) AS categories_id
FROM movies
LEFT JOIN
movies_categories
ON movies_categories.movie_id = movies.id
LEFT JOIN
categories
ON categories.id = movies_categories.category_id
WHERE MATCH(movie_title) AGAINST ("never back down" IN BOOLEAN MODE)
GROUP BY
movies.id
LIMIT 0, 30
This query will run much faster if you create a FULLTEXT
index:
CREATE FULLTEXT INDEX fx_movies_title ON movies (movie_title)
精彩评论