BOOLEAN order by relevance

Databases
1

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”.


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_description) AGAINST('+pilot' IN BOOLEAN MODE) AS relevance 
group by jobs.id order by relevance
2

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

3

Thanks, so I tried:

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_description) AGAINST('+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:

#1054 - Unknown column 'relevance' in 'order clause'

Any idea what’s wrong?

4

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

Ok, im now using the following query, but it returns all rows (so basically the boolean search is not working).


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,
MATCH (
jobs.job_title, jobs.job_description
)
AGAINST (
'+pilot +flying -british'
IN BOOLEAN
MODE
) AS relevance, 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'
GROUP BY jobs.id
ORDER BY relevance
LIMIT 0 , 30
6

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

7

So there will be two MATCH conditions like this:


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,
MATCH (
jobs.job_title, jobs.job_description
)
AGAINST (
'+pilot +flying -international'
IN BOOLEAN
MODE
) AS relevance, 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_description
)
AGAINST (
'+pilot +flying -british'
IN BOOLEAN
MODE
)
GROUP BY jobs.id
ORDER BY relevance
LIMIT 0 , 30
8

yup :slight_smile: