Mysql count + order

Databases
1

Hi,

I have 2 MySQL problems which I’m hoping someone can give me some advice with.

#1
I’m trying to develop a related posts feature for the blog section on my site, one way of doing this would be to use %LIKE% but that’s not really the most practical. I’m trying to use a boolean query and sort the results based on the relevance against the search terms. I’ve tried 2 different methods but as I’ve never done this type of query before I’m not sure which is right - seen both these methods posted on different sites.
Method 1


SELECT `blog_posts`.`title`, `blog_posts`.`pid`, `blog_posts`.`date` MATCH (`blog_posts`.`title`,`blog_posts`.`content`) AGAINST('+search* ~term' IN BOOLEAN MODE) AS relevance FROM `blog_posts` WHERE MATCH(`blog_posts`.`title`,`blog_posts`.`content`) AGAINST('+search* ~term' IN BOOLEAN MODE) >0 ORDER BY relevance DESC LIMIT 4;

Result -

#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 ‘MATCH (blog_posts.title) AGAINST(‘review*’ IN BOOLEAN MODE) AS relevance FRO’ at line 1

Method 2


SELECT `blog_posts`.`title`, `blog_posts`.`pid`, `blog_posts`.`date` FROM `blog_posts` WHERE MATCH(`blog_posts`.`title`,`blog_posts`.`content`) AGAINST('+search* ~term' IN BOOLEAN MODE) AS relevance ORDER BY relevance DESC LIMIT 4;

Result -

#1054 - Unknown column ‘relevance’ in ‘order clause’

Any advice on which way should work and the fix to that problem would be much appreciated.

#2
My 2nd problem is when showing all the blog posts I’m trying to count the number of comments that blog post has on it. I’m already using 2 MySQL joins so I tried to add in a 3rd along with a COUNT(), this doesn’t seem to work -


SELECT 														  
`blog_posts`.`title`, 
`blog_posts`.`pid`, 
`blog_posts`.`content`, 
`blog_posts`.`date`, 
`users`.`forename`, 
`users`.`surname`, 
`blog_categories`.`cat_name`,
COUNT(`blog_comments`.`co_id`) AS `comments`,
`blog_comments`.`delete`
FROM `blog_posts` 
JOIN `users` ON `blog_posts`.`uid` = `users`.`uid` 
JOIN `blog_categories` ON `blog_posts`.`cid` = `blog_categories`.`cid` 
LEFT JOIN `blog_comments` ON `blog_posts`.`pid` = `blog_comments`.`pid`
WHERE `blog_posts`.`delete` = '0' 
AND `blog_comments`.`delete` = '0'
ORDER BY `date`
DESC LIMIT 15;

Produces -

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

It looks confusing but I’ve managed to get it working with 2 joins -


SELECT  `blog_posts`.`title` ,  `blog_posts`.`pid` ,  `blog_posts`.`content` ,  `blog_posts`.`date` ,  `users`.`forename` ,  `users`.`surname` ,  `blog_categories`.`cat_name` 
FROM  `blog_posts` 
JOIN  `users` ON  `blog_posts`.`uid` =  `users`.`uid` 
JOIN  `blog_categories` ON  `blog_posts`.`cid` =  `blog_categories`.`cid` 
WHERE  `blog_posts`.`delete` =  '0'
ORDER BY  `date` DESC 
LIMIT 15 ;

I appreciate I’ve written quite a lot, any help with either issue would be much appreciated, thanks!

2

we should really take your errors one at a time

the first one is because you omitted a comma in front of the word MATCH

the second one is because you can’t assign a column alias in the WHERE clause

your queries would be a ~lot~ easier to read if you use line breaks and indentation instead of stringing them all out on a single line like that

3

ok, thanks for your help so far. I take it the 2nd query just won’t work, here is the first one done in a way easier to read -


SELECT  `blog_posts`.`title` ,  `blog_posts`.`pid` ,  `blog_posts`.`date` 
MATCH (

`blog_posts`.`title` ,  `blog_posts`.`content`
)
AGAINST (
'+search* ~term'
IN BOOLEAN
MODE
) AS relevance
FROM  `blog_posts` 
WHERE MATCH (
`blog_posts`.`title` ,  `blog_posts`.`content`
)
AGAINST (
'+search* ~term'
IN BOOLEAN
MODE
) >0
ORDER BY relevance DESC 
LIMIT 4
4

it still has the error, though :slight_smile:

try this –

SELECT title 
     , pid 
     , date 
     , MATCH (title,content) 
       AGAINST ('+search* ~term' IN BOOLEAN MODE) AS relevance
  FROM blog_posts 
 WHERE MATCH (title,content) 
       AGAINST ('+search* ~term' IN BOOLEAN MODE) > 0
ORDER 
    BY relevance DESC LIMIT 4

i suspect you don’t need the WHERE clause at all

:slight_smile:

as for your second query, i don’t know why you would think it “just wont work”

check this out and let me know if there’s anything you don’t understand, i made quite a number of minor changes –


SELECT blog_posts.title
     , blog_posts.pid
     , blog_posts.content
     , blog_posts.date
     , users.forename
     , users.surname
     , blog_categories.cat_name
     , COALESCE(c.comments,0) AS comments
  FROM blog_posts
INNER
  JOIN users 
    ON users.uid = blog_posts.uid
INNER
  JOIN blog_categories 
    ON blog_categories.cid = blog_posts.cid
LEFT OUTER
  JOIN ( SELECT pid
              , COUNT(*) AS comments
           FROM blog_comments 
          WHERE `delete` = 0
         GROUP
             BY pid ) AS c
    ON c.pid = blog_posts.pid
 WHERE blog_posts.delete = 0
ORDER 
    BY blog_posts.date DESC LIMIT 15
5

Wow, both of those work perfectly thanks!

I misunderstood what you said about ,MATCH but now I understand that.

With the 2nd query I just need to read up on the different types of joins as I’m still getting to grips with it all but again thanks for your help.

My only question is, what are you using COALESCE() for? I’ve never seen it before and looking at the MySQL manual I don’t really understand it.

6

the join to the subquery is a LEFT OUTER JOIN because there is the possibility that some posts may not have any comments

when that happens, the LEFT OUTER JOIN returns NULL for the c.pid and c.comments columns, and COALESCE turns the NULL c.comments into 0