I can’t figure out why my sql query below doesn’t return the expected results. The part that doesn’t seem to work is the “find_in_set(j.job_type_id, ‘2’)” part (If I remove this part the SQL returns the expected results).
SQL query:
select * from users
inner join
(
select user_id, CONCAT_WS(', ', GROUP_CONCAT(industry)) as skills,
industry_id
from users_industries
left join industries on industries.id = users_industries.industry_id group by user_id
) as i on i.user_id = users.id
inner join
(
select user_id, CONCAT_WS(', ', GROUP_CONCAT(value)) as salaries,
salary_id from users_salaries
left join salaries on salaries.id = users_salaries.salary_id group by user_id
) as s on s.user_id = users.id
inner join
(
select user_id, CONCAT_WS(', ', GROUP_CONCAT(html)) as seniority,
seniority_id from users_seniorities
left join seniority on seniority.id = users_seniorities.seniority_id group by user_id
) as s2 on s2.user_id = users.id
inner join
(
select user_id, CONCAT_WS(', ', GROUP_CONCAT(job_type)) as job_type,
job_type_id from users_job_types
left join job_types on job_types.id = users_job_types.job_type_id group by user_id
) as j on j.user_id = users.id
inner join
(
select user_id, group_concat(location_name separator '<br />') as display_name from users_locations
left join locations on locations.id = users_locations.location_id group by user_id
) as l on l.user_id = users.id
where
users.status='1'
and users.cv_hide='0'
and find_in_set(i.industry_id, '3,4,5')
and find_in_set(s.salary_id, '4')
and find_in_set(s2.seniority_id, '1,5,6')
and find_in_set(j.job_type_id, '2')
order by cv_date desc
and find_in_set(i.industry_id, '3,4,5')
and find_in_set(s.salary_id, '4')
and find_in_set(s2.seniority_id, '1,5,6')
and find_in_set(j.job_type_id, '2')
Are you sure you have records match with all of these conditions? Basically, you use this function right.
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.
mysql> SELECT FIND_IN_SET(‘b’,‘a,b,c,d’);
-> 2
Any way to tell if the letter-dot-whatever strs are what you expect them to be?
I think I may need to rewrite the query slightly as there could be multiple values that I need to search and find_in_set will only work for 1 value. Would something like this work?
select * from users
inner join
(
select
user_id,
CONCAT_WS(', ', GROUP_CONCAT(industry)) as skills,
CONCAT_WS(',', GROUP_CONCAT(industry_id)) as industry_id
from users_industries
left join industries on industries.id = users_industries.industry_id group by user_id
) as i on i.user_id = users.id
inner join
(
select
user_id,
CONCAT_WS(', ', GROUP_CONCAT(value)) as salaries,
CONCAT_WS(',', GROUP_CONCAT(salary_id)) as salary_id
from users_salaries
left join salaries on salaries.id = users_salaries.salary_id group by user_id
) as s on s.user_id = users.id
inner join
(
select
user_id,
CONCAT_WS(', ', GROUP_CONCAT(html)) as seniority,
CONCAT_WS(',', GROUP_CONCAT(seniority_id)) as seniority_id
from users_seniorities
left join seniority on seniority.id = users_seniorities.seniority_id group by user_id
) as s2 on s2.user_id = users.id
inner join
(
select
user_id,
CONCAT_WS(', ', GROUP_CONCAT(job_type)) as job_type,
CONCAT_WS(',', GROUP_CONCAT(job_type_id)) as job_type_id
from users_job_types
left join job_types on job_types.id = users_job_types.job_type_id group by user_id
) as j on j.user_id = users.id
inner join
(
select user_id, group_concat(location_name separator '<br />') as display_name from users_locations
left join locations on locations.id = users_locations.location_id group by user_id
) as l on l.user_id = users.id
where
users.status='1'
and users.cv_hide='0'
and i.industry_id IN ('3,4,5')
and s.salary_id IN ('4')
and s2.seniority_id IN ('1,5,6')
and j.job_type_id IN ('2')
order by cv_date desc
I’m using this sql now, which I thought would work, but it still doesn’t seem to be matching the find_in_set part of the clause even though there are 2 rows in “jobs_industries”, one with industry_id=4 and one with industry_id=3. It seems to be matching the first row (industry_id=3) but not the second one (industry_id=4).
Here is the SQL i’m using:
select
jobs.job_title,
jobs.job_url,
jobs.job_perks,
jobs.job_description,
jobs.date_posted,
companies.company_name,
companies.company_url,
companies.logo_thumbnail,
count(applications.id) as applications,
count(featured_jobs.id) as featured,
i.industry,
j.job_type,
s.job_salary,
s2.seniority,
l.display_name
from jobs
inner join companies on companies.id = jobs.company_id
left outer join applications on applications.job_id = jobs.id
left outer join featured_jobs on featured_jobs.job_id = jobs.id
inner join (
select
job_id,
CONCAT_WS(', ', GROUP_CONCAT(industry)) as industry,
industry_id
from jobs_industries
left join industries on industries.id = jobs_industries.industry_id
group by job_id
) as i on i.job_id = jobs.id
inner join (
select
job_id,
group_concat(job_type_id SEPARATOR ',') as job_type
from jobs_job_types
group by job_id
) as j on j.job_id = jobs.id
inner join (
select
job_id,
CONCAT_WS(', ', GROUP_CONCAT(value)) as job_salary,
salary_id
from jobs_salaries
left join salaries on salaries.id = jobs_salaries.salary_id
group by job_id
) as s on s.job_id = jobs.id
inner join (
select
job_id,
CONCAT_WS(', ', GROUP_CONCAT(html)) as seniority,
seniority_id
from jobs_seniority
left join seniority on seniority.id = jobs_seniority.seniority_id
group by job_id
) as s2 on s2.job_id = jobs.id
inner join (
select
job_id,
CONCAT_WS(', ', GROUP_CONCAT(location_name)) as display_name,
location_id
from jobs_locations
left join locations on locations.id = jobs_locations.location_id
group by job_id
) as l on l.job_id = jobs.id
where
jobs.status='on'
and (find_in_set(i.industry_id, '4'))
group by jobs.id
order by featured desc, date_posted desc
