how to filter join query with Flask and SQLAlchemy

Question

Hello I'm trying to query something like this in SQLAlcehmy, Flask and Graphene

select d."name", e."name" 
from departments d 
join employees e on e.department_id = d.id 
where e."name" like '%M%' and d."name" = 'IT'

so, it would return all employee with name contains 'M' in department 'IT'

here's my query in python using sqlalchemy

find_department = graphene.List(Department, name = graphene.String(), employee = graphene.String())
def resolve_find_department(self, info, name, employee):
  like_query = '%{0}%'.format(employee)
  department = (Department.get_query(info)
                          .join(EmployeeModel)
                          .filter(DepartmentModel.name == name)
                          .filter(EmployeeModel.name.ilike(like_query)))
        
  return department

and in my graphql

{
  findDepartment(name:"IT", employee:"M"){
    name
    employees{
      edges{
        node{
          name
        }
      }
    }
  }
}

and the result is it returns all employee instead of 1 with name contains 'M'

{
  "data": {
    "findDepartment": [
      {
        "name": "IT",
        "employees": {
          "edges": [
            {
              "node": {
                "name": "Chris"
              }
            },
            {
              "node": {
                "name": "Dori"
              }
            },
            {
              "node": {
                "name": "Mario"
              }
            }
          ]
        }
      }
    ]
  }
}

why is that happening? how to show only 1 just like the SQL query returns? thanks

UPDATE: the query from SQLAlchemy was fine

SELECT departments.id AS departments_id, departments.name AS departments_name 
FROM departments JOIN employees ON departments.id = employees.department_id
WHERE departments.name = 'IT' AND employees.name ILIKE '%M%'

but somehow when it's called using graphql, with query above, it returns all employee instead of the filtered one

how to return only the filtered Employee?

Answer 1

I think you need to use an "and_" filter to make sure sqlalchemy returns only rows which fulfil all the requirements (rather than at least one of the filters):

from sqlalchemy import and_
department = (Department.get_query(info)
                      .join(EmployeeModel)
                      .filter(and_(DepartmentModel.name == name, EmployeeModel.name.ilike(like_query)))

not 100% sure if this works with graphene or whatever (i think this is unrelated in this case...). Also check this link: sqlalchemy tutorial

Give it a try!