Issue filtering query

💬 Queries and Resources
, , ,
1

Hello!
I have an issue with my query:
So I have a database where we collect our members, we also have a table called focus areas which has an n:m relation to our experts(members) table.
additionally I also have a department table which is connected to a faculty table as well as a keyword table connected to experts via the expertise table.
I have filtered the query by selecting the faculties and the keywords, that is when I select them the query only returns the members with the respective keyowrds or and faculties. I want to do the same for focusarea. The issue Im encountering is the following: I have only 4 focus areas, when I filter for the first just one it works fine, but whenever I select more than one, it filters by only the smallest id number.
what I want is to display all experts that have at least one of the selected focus areas but also show all the focus areas they have.
Here are the table/multiselects by which I want to filter:

image
image913×397 35.7 KB

each of the items has an id.

here is my query:

SELECT 
    e.*, 
    d.name AS department_name,
    d.department_id AS department_id, 
    f.name AS faculty_name, 
    COALESCE(GROUP_CONCAT(DISTINCT k.name SEPARATOR ', '), '') AS keywords,
    COALESCE(GROUP_CONCAT(DISTINCT fo.focus ORDER BY fo.focus SEPARATOR ', '), '') AS focus_id, 
    COALESCE(GROUP_CONCAT(DISTINCT fa.name ORDER BY fa.name SEPARATOR ', '), '') AS focus_name,
    COALESCE(GROUP_CONCAT(DISTINCT fa.color ORDER BY fa.color SEPARATOR ', '), '') AS focus_color
FROM Expert e
JOIN Department d ON e.department = d.department_id
JOIN Faculty f ON d.faculty = f.faculty_id
LEFT JOIN Expertise ex ON e.expert_id = ex.expert 
LEFT JOIN Keyword k ON ex.keyword = k.keyword_id
LEFT JOIN Focus fo ON e.expert_id = fo.expert
LEFT JOIN FocusAreas fa ON fo.focus = fa.focus_id
WHERE f.faculty_id IN (
    {{
        rt_faculty_multiselect.value.length == 0 
        ? rt_faculty_multiselect.values.map(x => parseInt(x)) 
        : rt_faculty_multiselect.value.map(x => parseInt(x)) 
    }}
)
GROUP BY e.expert_id
HAVING (
   
    {{ rt_focusArea_multiselect.value.length === 0 }}
    OR (
        COUNT(CASE 
            WHEN fo.focus IN (
                {{
                    rt_focusArea_multiselect.value.map(x => parseInt(x)).join(',')
                }}
            )
            THEN 1 ELSE NULL 
        END) > 0
    )
)
AND (
    
    {{ rt_keywords_table.selectedRows.length > 0 }}
    AND (
        COUNT(CASE 
            WHEN k.keyword_id IN (
                {{ 
                    rt_keywords_table.selectedRows.map(x => parseInt(x.keyword_id)).join(',')
                }}
            )
            THEN 1 ELSE NULL 
        END) > 0
        OR COUNT(k.keyword_id) = 0
    )
    OR {{ rt_keywords_table.selectedRows.length == 0 }}
)
ORDER BY e.last_name;

I have also teid to use IN as follows:

SELECT 
    e.*, 
    d.name AS department_name,
    d.department_id AS department_id, 
    f.name AS faculty_name, 
    COALESCE(GROUP_CONCAT(k.name SEPARATOR ', '), '') AS keywords,
  COALESCE(GROUP_CONCAT(fo.focus SEPARATOR ', '), '') AS focus_id, 
  COALESCE(GROUP_CONCAT(fa.name SEPARATOR ', '), '') AS focus_name
FROM Expert e
JOIN Department d ON e.department = d.department_id
JOIN Faculty f ON d.faculty = f.faculty_id
LEFT JOIN Expertise ex ON e.expert_id = ex.expert 
LEFT JOIN Keyword k ON ex.keyword = k.keyword_id
LEFT JOIN Focus fo ON e.expert_id = fo.expert
LEFT JOIN FocusAreas fa ON fo.focus = fa.focus_id
WHERE f.faculty_id IN (
    {{
        rt_faculty_multiselect.value.length == 0 
        ? rt_faculty_multiselect.values.map(x => parseInt(x)) 
        : rt_faculty_multiselect.value.map(x => parseInt(x)) 
    }}
) AND fo.focus IN (
  {{
        rt_focusArea_multiselect.value.length == 0 
        ? rt_focusArea_multiselect.values.map(x => parseInt(x)) 
        : rt_focusArea_multiselect.value.map(x => parseInt(x)) 
    }}
  
)
GROUP BY e.expert_id
HAVING (
    {{ rt_keywords_table.selectedRows.length > 0 }}
    AND (
        COUNT(CASE WHEN k.keyword_id IN (
            {{ 
                rt_keywords_table.selectedRows.length > 0 
                ? rt_keywords_table.selectedRows.map(x => parseInt(x.keyword_id)) 
                : [-1] 
            }}
        ) THEN 1 ELSE NULL END) > 0
        OR COUNT(k.keyword_id) = 0
    )
)
OR {{ rt_keywords_table.selectedRows.length == 0 }}
ORDER BY e.last_name

but this then only shows me the focus areas I have selected and not all the focus areas the member has.

I am not sure if this is retool specific or sql in general but I figured I am going to ask here anyway.

as an example, I have selected focus areas with ids 2 and 3 but the result is the following when I console.log:

  • expert_id: Array(20)

  • first_name: Array(20)

  • last_name: Array(20)

  • title_pre: Array(20)

  • title_post: Array(20)

  • gender: Array(20)

  • membership_since: Array(20)

  • membership_until: Array(20)

  • career_level: Array(20)

  • mail: Array(20)

  • phone: Array(20)

  • website: Array(20)

  • profile_image: Array(20)

  • orcid: Array(20)

  • ucris: Array(20)

  • google_scholar: Array(20)

  • files: Array(20)

  • department: Array(20)

  • department_name: Array(20)

  • department_id: Array(20)

  • faculty_name: Array(20)

  • keywords: Array(20)

focus_id: Array(20)

  • 0: "2, 3"

  • 1: "2, 4"

  • 2: "2, 3"

  • 3: "2"

  • 4: "1, 2"

  • 5: "2"

  • 6: "2"

  • 7: "2"

  • 8: "2, 4"

  • 9: "2, 4"

  • 10: "2, 4"

  • 11: "2, 4"

  • 12: "2, 4"

  • 13: "2"

  • 14: "2, 4"

  • 15: "2"

  • 16: "2"

  • 17: "2, 3"

  • 18: "2"

    1. 19: "2"

  • focus_name: Array(20)

it doesnt show members who only have focus area 3 (or in general members that dont have focus area 2).

Thank you for your help in advance!

2

I'm not as familiar with using MySQL in Retool, but it may be that you need to pass the array as a string variable using .join(',') (in Postgres, would just use the x = ANY( [array] ) syntax). Maybe something like this:

SELECT 
  e.*, 
  d.name AS department_name,
  d.department_id AS department_id, 
  f.name AS faculty_name, 
  COALESCE(GROUP_CONCAT(DISTINCT k.name SEPARATOR ', '), '') AS keywords,
  COALESCE(GROUP_CONCAT(DISTINCT fo.focus ORDER BY fo.focus SEPARATOR ', '), '') AS focus_id, 
  COALESCE(GROUP_CONCAT(DISTINCT fa.name ORDER BY fa.name SEPARATOR ', '), '') AS focus_name,
  COALESCE(GROUP_CONCAT(DISTINCT fa.color ORDER BY fa.color SEPARATOR ', '), '') AS focus_color
FROM
  Expert e
JOIN
  Department d  ON e.department = d.department_id
JOIN
  Faculty    f  ON d.faculty    = f.faculty_id
LEFT JOIN
  Expertise  ex ON e.expert_id = ex.expert 
LEFT JOIN
  Keyword    k  ON ex.keyword   = k.keyword_id
LEFT JOIN
  Focus      fo ON e.expert_id = fo.expert
LEFT JOIN
  FocusAreas fa ON fo.focus    = fa.focus_id
WHERE
-- Not totally sure how Retool works with MySQL, but add .join(',') to pass a list of strings
  {{ rt_faculty_multiselect.value.length === 0 }}  -- all faculties (none selected)
  OR f.faculty_id IN (                             -- the faculty is in the array of selected values (joined into a string)
       {{ rt_faculty_multiselect.value
            .map(x => parseInt(x))
            .join(',')
       }}
     )
GROUP BY
  e.expert_id
HAVING
-- Not totally sure how Retool works with MySQL, but add .join(',') to pass a list of strings
  (
     {{ rt_focusArea_multiselect.value.length === 0 }}  -- no focus area selected
   OR SUM(
        fo.focus IN (                                   -- focus area is selected...
          {{ rt_focusArea_multiselect.value
               .map(x => parseInt(x))
               .join(',')
          }}
        )
     ) > 0                                              -- ...and the focus areas have at least 1 of the selected strings
  )
  AND
  (
     {{ rt_keywords_table.selectedRows.length === 0 }}
   OR SUM(
        k.keyword_id IN (
          {{ rt_keywords_table.selectedRows             -- keywords selected...
               .map(r => parseInt(r.keyword_id))
               .join(',')
          }}
        )
     ) > 0                                              -- ...and the keywords have at least 1 of the selected strings
  )
ORDER BY
  e.last_name;