0

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.

0

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.

  • thank you very much. however, two problems keep me from progressing: a) I was keep reading into the drupal database api (since I figured that the query object is a lot like the core one), but I still do not know, how to implement subqueries (since add_where() seems like condition() but then again it doesn't). but most of all b) sql spurrs error: Unknown column 'node.Array' in 'on clause', so I cannot really try and error things out.. – alex Mar 3 '12 at 18:02
  • Yes, Views development is difficult. You have to refer to the sources to know (a bit) what you're doing. Please go in the source, understand which objects are used and how they're structured. Devel is extremely useful in this matter : it allows you to dump complete objects with the dpm function, letting you investigate what's happening in the objects. For example, dpm($view->query) should be enlightening. Put it in hook_views_alter, for example. – Countzero Mar 3 '12 at 18:32
  • yeah I agree, Views development is quite difficult. But then again, the only difficult part is finding working examples. It is quite uncommon for a module to be that much documented inline, so that's a plus. But it is very hard finding the right clues. See my edit how I solved my problem above and you may know what I mean, when I say it is frustrating to debug and pseudo-reverse-engineer high quality (as in high level) code. Cheers! (P.S. greetings to France I presume?) – alex Mar 4 '12 at 18:56
  • God ... Is my english that bad ??? – Countzero Mar 4 '12 at 19:24
  • haha. no, during my research I found a drupal node on drupal.org written by someone called Countzero. And in the posted sourcecode I noticed that the table-names (or was it the fields, the variables, who knows...) were in french. So I guessed. ;) – alex Mar 4 '12 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.