I am trying to get my filter query ready, but I don't understand how any of the query object works. my actual SELECT query looks like this:
SELECT nid FROM `field_data_field_verein` verein LEFT JOIN `ds_vereine` ON ds_vereine.tid=verein.tid WHERE ds_vereine.plz IN (SELECT ds_plz_distance.plz_first FROM `ds_plz_distance` WHERE ds_plz_distance.distance < 20 AND ds_plz_distance.plz_first=ds_vereine.plz) OR ds_vereine.plz IN (SELECT ds_plz_distance.plz_second FROM `ds_plz_distance` WHERE ds_plz_distance.distance < 20 AND ds_plz_distance.plz_second=ds_vereine.plz) OR ds_vereine.plz IN (SELECT ds_plz_ort.plz FROM `ds_plz_ort` ort WHERE ds_plz_ort.kreis=306)
I honestly do not know how to use
$this->query->add_where() function to incorporate this query. All it gives me is a fatal sql error.
you can see my views filter handler functions here: views filter: broken filter - but what's broken about it?
Following Countzero's advice I took the plunge and delved deep into the views-api/code. Two problems I struggled very strongly with were a) joining the required tables the right way and b) implementing the subqueries. I'll shortly explain what I found worked for me.
a) I followed the api-docs and told views my used tables in
hook_views_data. Don't. Instead join it in your filter. In short:
$join = new views_join(); $join->construct('table','left_table','left_field','field'); $this-query->ensure_table('table',$this->relationship,$join);
b) It is actually not a problem at all to nest conditions and thus subqueries. It was quite difficult to find out what you have to pass as an argument in the
add_where function, so I tell you here for your convenience (;)):
$subquery=db_select('table','table_alias') ->fields('table_alias',array('field')) ->condition('table_alias.condition_field',20,'<') ->where('table_alias.condition_field=base_table.bt_field',array()); $this->query->add_where($group,'base_table.bt_field',$subquery,'in');
Note: The key lies in constructing the select query without executing it and passing it as a value directly.