SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    BOOLEAN order by relevance

    Hi Guys..

    I have just started to use the BOOLEAN mode in MySQL, however how do I order by relevance using it?

    I am using the following sql query, but it throws unknown column "relevance".

    PHP Code:
    select 
        jobs
    .job_title
        
    companies.company_name
        
    companies.company_url
        
    companies.logo_thumbnail
        
    jobs.job_url
        
    jobs.job_salary
        
    jobs.job_ote
        
    jobs.job_perks
        
    jobs.job_description
        
    jobs.location
        
    jobs.date_posted
        
    industries.industry
        
    job_types.job_type
        
    count(applications.id) as applications
        
    count(featured_jobs.id) as featured from jobs 
    inner join companies on companies
    .id jobs.company_id 
    inner join industries on industries
    .id jobs.industry 
    inner join job_types on job_types
    .id jobs.job_type 
    left outer join applications on applications
    .job_id jobs.id 
    left outer join featured_jobs on featured_jobs
    .job_id jobs.id 
        where jobs
    .status='on' and MATCH(jobs.job_title,jobs.job_descriptionAGAINST('+pilot' IN BOOLEAN MODE) AS relevance 
    group by jobs
    .id order by relevance 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you cannot assign a column alias in the WHERE clause, remove the "AS relevance"

    copy the MATCH expression, including the alias, into the SELECT clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you cannot assign a column alias in the WHERE clause, remove the "AS relevance"

    copy the MATCH expression, including the alias, into the SELECT clause
    Thanks, so I tried:

    PHP Code:
    select 
        jobs
    .job_title
        
    companies.company_name
        
    companies.company_url,
        
    companies.logo_thumbnail
        
    jobs.job_url
        
    jobs.job_salary
        
    jobs.job_ote
        
    jobs.job_perks
        
    jobs.job_description
        
    jobs.location
        
    jobs.date_posted
        
    industries.industry
        
    job_types.job_type
        
    count(applications.id) as applications
        
    count(featured_jobs.id) as featured
        
    MATCH(jobs.job_title,jobs.job_descriptionAGAINST('+pilot' IN BOOLEAN MODE
    from jobs 
        inner join companies on companies
    .id jobs.company_id 
        inner join industries on industries
    .id jobs.industry 
        inner join job_types on job_types
    .id jobs.job_type 
        left outer join applications on applications
    .job_id jobs.id 
        left outer join featured_jobs on featured_jobs
    .job_id jobs.id 
    where jobs
    .status='on' group by jobs.id 
    order by relevance 
    However, it produces:

    PHP Code:
    #1054 - Unknown column 'relevance' in 'order clause' 
    Any idea what's wrong?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    copy the MATCH expression, including the alias, into the SELECT clause
    That means in the SELECT you have to leave the "AS relevance" part, because that is what gives the name "relevance" to the column.

  5. #5
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    That means in the SELECT you have to leave the "AS relevance" part, because that is what gives the name "relevance" to the column.
    Ok, im now using the following query, but it returns all rows (so basically the boolean search is not working).

    PHP Code:
    SELECT jobs.job_titlecompanies.company_namecompanies.company_urlcompanies.logo_thumbnailjobs.job_urljobs.job_salaryjobs.job_otejobs.job_perksjobs.job_descriptionjobs.locationjobs.date_postedindustries.industryjob_types.job_type,
    MATCH (
    jobs.job_titlejobs.job_description
    )
    AGAINST (
    '+pilot +flying -british'
    IN BOOLEAN
    MODE
    ) AS relevancecountapplications.id ) AS applicationscountfeatured_jobs.id ) AS featured
    FROM jobs
    INNER JOIN companies ON companies
    .id jobs.company_id
    INNER JOIN industries ON industries
    .id jobs.industry
    INNER JOIN job_types ON job_types
    .id jobs.job_type
    LEFT OUTER JOIN applications ON applications
    .job_id jobs.id
    LEFT OUTER JOIN featured_jobs ON featured_jobs
    .job_id jobs.id
    WHERE jobs
    .status 'on'
    GROUP BY jobs.id
    ORDER BY relevance
    LIMIT 0 
    30 

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, it is not returning all rows, it is returning only 'on' status rows

    the reason it's not filtering for pilot flying british is because you no longer have the MATCH condition in the WHERE clause

    if you check post #2, i suggested that you leave it in the WHERE clause, just remove the "AS relevance" there
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, it is not returning all rows, it is returning only 'on' status rows

    the reason it's not filtering for pilot flying british is because you no longer have the MATCH condition in the WHERE clause

    if you check post #2, i suggested that you leave it in the WHERE clause, just remove the "AS relevance" there
    So there will be two MATCH conditions like this:

    PHP Code:
    SELECT jobs.job_titlecompanies.company_namecompanies.company_urlcompanies.logo_thumbnailjobs.job_urljobs.job_salaryjobs.job_otejobs.job_perksjobs.job_descriptionjobs.locationjobs.date_postedindustries.industryjob_types.job_type,
    MATCH (
    jobs.job_titlejobs.job_description
    )
    AGAINST (
    '+pilot +flying -international'
    IN BOOLEAN
    MODE
    ) AS relevancecountapplications.id ) AS applicationscountfeatured_jobs.id ) AS featured
    FROM jobs
    INNER JOIN companies ON companies
    .id jobs.company_id
    INNER JOIN industries ON industries
    .id jobs.industry
    INNER JOIN job_types ON job_types
    .id jobs.job_type
    LEFT OUTER JOIN applications ON applications
    .job_id jobs.id
    LEFT OUTER JOIN featured_jobs ON featured_jobs
    .job_id jobs.id
    WHERE jobs
    .status 'on'
    AND MATCH (
    jobs.job_titlejobs.job_description
    )
    AGAINST (
    '+pilot +flying -british'
    IN BOOLEAN
    MODE
    )
    GROUP BY jobs.id
    ORDER BY relevance
    LIMIT 0 
    30 

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yup
    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
  •