views filter: problems with the query object

Question

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?

thanks!

Edit

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.

Answer 1

Your add_where syntax doesn't follow the documentation given in Views code. Here is a paste :

/**
   * Add a simple WHERE clause to the query. The caller is responsible for
   * ensuring that all fields are fully qualified (TABLE.FIELD) and that
   * the table already exists in the query.
   *
   * @param $group
   *   The WHERE group to add these to; groups are used to create AND/OR
   *   sections. Groups cannot be nested. Use 0 as the default group.
   *   If the group does not yet exist it will be created as an AND group.
   * @param $field
   *   The name of the field to check.
   * @param $value
   *   The value to test the field against. In most cases, this is a scalar. For more
   *   complex options, it is an array. The meaning of each element in the array is
   *   dependent on the $operator.
   * @param $operator
   *   The comparison operator, such as =, <, or >=. It also accepts more complex
   *   options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is an array
   *   = otherwise. If $field is a string you have to use 'formula' here.
   *
   * @see QueryConditionInterface::condition()
   */
  function add_where($group, $field, $value = NULL, $operator = NULL) {

So basically you cannot inject a complete query like you tried to, but you must use the different methods provided by the objects to carefully construct the different parts.

The less clear part is the group argument : it refers to the groups defined in the filters part of Views UI, the one created when you use Create new groupin the filters contextual menu.