8.4.2 How to join 3 tables for search by each column

Permalink December 08, 2018 at 10:57 AM

I have 3 tables: Countries, States, Cities. They have OneToMany relationships Countries->Cities, States->Cities.

I have the following in the CityList:

if ($this->search) {
    $query->innerJoin('c', 'Countries', 'co', 'co.country_id = c.country')
        ->innerJoin('c', 'States', 'st', 'st.state_id = c.state')
        ->andWhere('co.country like ?')->setParameter($paramcount++, '%'. $this->search. '%')
        ->orWhere('st.state like ?')->setParameter($paramcount++, '%'. $this->search. '%')
        ->orWhere('city like ?')->setParameter($paramcount++, '%'. $this->search. '%');
}
return $query;

This works fine, it searches for either a country or a state or a city but ONLY if the country AND state are NOT empty. And I can't figure out why it wouldn't search without them just by the city.

Does anyone have any ideas?