Where clause on 2nd, 3rd, or 4th level join

As in my previous post imagine these are my model joins:

options.include = [{
    model : <CompanyProjects>,
    include : [{
        model : <Projects>
    }]
}]

The main table is Companies, now let's say I want to look for a specific word in project name, so I'm doing the following:

options.include = [{
    model : <CompanyProjects>,
    include : [{
        model : <Projects>,
        where : {
            project_name : {
                $ilike : <myvalue>
            }
        }
    }]
}]

So far so good, but if I'm using pagination as well (using CrudlIO to generate query and if the search happens on the list, it always shows 100 rows), then my query is generated as follows:

SELECT
    *
FROM (
    SELECT
        *
    FROM Companies
    LIMIT 100
) AS 'Companies'
    LEFT OUTER JOIN CompanyProjects ON ...
    INNER JOIN Projects ON ... AND project_name ILIKE '%<myvalue>%'

the problem is that the search is performed only on the first 100 Companies, which is incorrect. If I change the include structure to this:

options.include = [{
    model : <CompanyProjects>,
    required : true, 
    include : [{
        model : <Projects>,
        where : {
            project_name : {
                $ilike : <myvalue>
            }
        }
    }]
}]

Then the CompanyProjects goes inside the subselect, which is great but adding required : true to the Projects doesn't have the same effect on the Projects. The query looks like this:

SELECT
    *
FROM (
    SELECT
        *
    FROM Companies
    WHERE
        (
            SELECT company_id
            FROM CompanyProjects
            WHERE
                (CompanyProjects.company_id = Companies.company_id)
            LIMIT 1
        ) IS NOT NULL
    LIMIT 100
) AS 'Companies'
    LEFT OUTER JOIN CompanyProjects ON ...
    INNER JOIN Projects ON ... AND project_name ILIKE '%<myvalue>%'

But this gives me first 100 companies that happen to have projects and then those 100 are filtered with the INNER JOIN.

How do I configure the includes to do this:

SELECT
    *
FROM (
    SELECT
        *
    FROM Companies
    WHERE
        (
            SELECT company_id
            FROM CompanyProjects
                INNER JOIN Projects ON ... AND project_name ILIKE '%<myvalue>%'
            WHERE
                (CompanyProjects.company_id = Companies.company_id)
            LIMIT 1
        ) IS NOT NULL
    LIMIT 100
) AS 'Companies'
    LEFT OUTER JOIN CompanyProjects ON ...
    INNER JOIN Projects ON ... AND project_name ILIKE '%<myvalue>%'

Otherwise any filters I do on anything other than deeper levels are useless

mickhansen / graphql-sequelize

Where clause on 2nd, 3rd, or 4th level join #589