1

I need help with a query. I am having a really hard time going in the right direction.

I have a custom multi-value field, containing one 'name' and one 'id'. I want to filter on contents that:

  • None of the delta has a name 'test' ('test' is a name example).
  • OR, the name is 'test' and the id is equal to 0.

I was planning on having the logic in the customer filter, like so:

class my_module_handler_filter_field_name extends views_handler_filter_boolean_operator {
  function query() {
      // My custom code here
  }
}

But I don't have much success for now. I managed to filter rows where 'name' = 'test' using:

$this->query->add_where($this->options['group'], 'field_name', 'test', 'LIKE');

But if I do the opposite with 'NOT LIKE', it just shows me every row not equal to 'test' (also including rows from an item that has one name = 'test'). This behaviour is normal, I know it is simply not the right way.

A table example:

    entity_id    delta    store_name  id_in_store
    ---------------------------------------------
    1            0        Amazon      0
    1            1        BestBuy     123442111
    2            0        Apple       123442123
    2            1        Microsoft   124423459
    2            2        Google      112559002

Entity_id is a node ID for a 'product' content type. I want to be able to know which products are sold at Amazon. And there are 2 ways to know it:

  • The field have a delta where store_name is 'Amazon', but id = 0.
  • Or the the field doesn't have any delta where store name is 'Amazon'.
7
  • so you are using strings as field value deltas? It is even possible? Why you do it?
    – Codium
    Commented Dec 14, 2015 at 0:32
  • Sorry I wasn't clear. I am not using delta as a string. I will add a table example.
    – Doud
    Commented Dec 14, 2015 at 0:40
  • so you are filtering by string 'speaker', so why you want to get other entity? There is one entity with this value. What the point of all of this?
    – Codium
    Commented Dec 14, 2015 at 0:52
  • I edited again to try to be more precise. Hope this helps understand.
    – Doud
    Commented Dec 14, 2015 at 1:04
  • How is the name provided? User input, entity reference, contextual filter? Commented Dec 14, 2015 at 1:09

1 Answer 1

0

I guess you are looking for something like this, but you might need to tweak it a bit. You can group db_or and db_and, then combine them like this. Basically create two 'and' groups where all conditions need to match. Then combine those in an 'or' group, where one of the conditions needs to match.

function query() {
  $and1 = db_and();
    $and1->condition('field_name', 'test', 'LIKE');
    $and1->condition('field_id', 0);

  $and2 = db_and();
    $and2->condition('field_name', 'test', '<>');

  $combine = db_or();
    $combine->condition($and1);
    $combine->condition($and2);

  $this->query->add_where(
    $this->options['group'],
    $combine
  );
}
8
  • I will try this, but I looks like something like this! Thanks for this :) To answer your comment, I am the one who provides the name. Thanks again
    – Doud
    Commented Dec 14, 2015 at 1:39
  • Good luck, I hope this will get you somewhere! Commented Dec 14, 2015 at 1:40
  • It seems to be working. But I will have to test it more. It was returning me all the results of each 'and', so I had to group results with: $this->query->add_field('table_name', 'entity_id', 'product_id', array('function' => 'groupby')); $this->query->add_groupby('table_name.entity_id');
    – Doud
    Commented Dec 14, 2015 at 3:23
  • Great you've figured it out. If the answer helped you, you can accept it with the tick symbol or upvote it left of the answer. Commented Dec 14, 2015 at 9:12
  • Hi @Neograph734, it not working perfectly yet unfortunately but I think I am close. How you are translate sql having sum(table_name.store_name = 'Amazon) = 0 to Views query? I tried $this->query->add_having($this->options['group'], "SUM(table_name.store_name = 'Amazon')", 0, '='); but not a big success for now :s
    – Doud
    Commented Dec 14, 2015 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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