SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql count + order

    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
    Code:
    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
    Code:
    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 -
    Code:
    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 -

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 -
    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by goldkiller07 View Post
    here is the first one done in a way easier to read -
    it still has the error, though

    try this --
    Code:
    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



    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 --
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •