I have a problem that could be easily solved if I could add an additional inner join and a where clause to filter my view. Specifically, I'm working with viewfield, and I'd like to filter out nodes that do not have any items in their subviews. The filter provided by viewfield doesn't support that feature (I think because it doesn't get the subitems until it renders them, but at that point views has already loaded the data).

It looks like there should be something in the views API to extend things (I think that's what views_or uses), but I can't find any documentation, and looking at the views_or code hasn't given me any information about exactly where or how to do that).

Any tips appreciated.

Thanks.

Colin

Comments

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

but I can't find any documentation

You're not looking very hard then. I hide the links to the documentation in plain site: on the project page. Also, most of the documentation is in the advanced help, which Views complains about not having installed unless you turn that off. And if you turned that off, why are you submitting issues?

colin_young’s picture

colin_young commented
Status: Fixed » Closed (fixed)

Wow. I did a spectacular job of explaining my problem. I can see why there is some confusion. I like to think I usually do a better job of explaining myself (http://drupal.org/node/337649 which has an actual example of the sql I wanted to generate, but I've since come up with what I think is a better approach -- which I'll get to below). I blame severe sleep deprivation (a five year-old and an infant will do that to you).

Just to clear things up, I do know where the documentation is, and I've even read a bunch of it, both in advanced help and the API (understanding is a whole other issue however). I wish many other modules had documentation to hide. What I should have said was I couldn't find anything that seemed relevant to what I was trying to do.

Originally, my idea was to do an inner join with a subquery (and MyValue would be the filter field):

SELECT ..., B.MyValue
FROM Node node
LEFT JOIN (select count(*) as MyValue, parentNid from tblB
where Column1 = arg(0) and Column2 = arg(1) group by parentNid) B
on B.parentNid = node.nid
WHERE MyValue ...

That was based on a line of reasoning something like "if only viewfield exposed the count of items in each embedded view I could just use the existing filter mechanisms". I'm now thinking it might be simpler and more useful in other situations if there was an exists option (maybe a new module like views_or) to produce something like this:

SELECT ...
FROM ...
WHERE ...
AND EXISTS (select * from tblB B where B.ParentNid = node.nid and Column1 = arg(0) and Column2 = arg(1))

which seems a lot less intrusive than my first idea.

I think that I've already got all my fields and relationships defined (thanks to the node reference CCK field), so I'm just going to need to implement a filter handler. I'm guessing based on the success of the views_or module that I'll be able to put an exists into my where clause.

I'm still mildly curious about whether it would be possible to put a subquery in place of a table for joining though. The docs seem to indicate that it needs to be a table.

merlinofchaos’s picture

merlinofchaos commented

I've never given any thought to actually joining on subqueries too much. There are a couple of views handlers that do use subqueries; for the most part, they don't do it with the API but the handler puts it in there via a manually created $join object or an add_where that includes the entirety of the subquery.

I don't think I actually join on subqueries at all, anywhere. I don't know if it's possible, therefore. You could consider this new territory for Views.

colin_young’s picture

colin_young commented

That gives me some direction to start exploring then. I need to figure out what all the objects in views hold, so I'm in the process of getting and installing a PHP debugging solution (unfortunately it looks like that is more difficult than it needs to be on OS X). That's just how I learn -- by taking apart existing stuff to see how it works.

I've got a (badly) hacked-together that adds an exists clause for me, but the fact that the entirety of the exists clause is hard-coded and very specific to my solution doesn't even begin to describe what's wrong with my solution. I do want to come up with a more generic solution, but first I need to learn a fair bit more about how views works. Then I'll probably solicit suggestions for how a exists module should work.

Thanks.

sinn’s picture

sinn commented
Issue summary: View changes

it works:

class example_handler_field_number_users extends views_handler_field {

  function construct() {
    parent::construct();
    $this->additional_fields['nid'] = 'nid';
  }

  function query() {
    $this->ensure_my_table();
    $this->add_additional_fields();
    $table = '(select m.nid, count(*) as cnt FROM masterclass_user m GROUP BY m.nid)';
    $join = new views_join();
    $join->construct($table, $this->table_alias, 'nid', 'nid');
    $this->user_table = $this->query->ensure_table('number_users', $this->relationship, $join);
    $this->user_field = $this->query->add_field($this->user_table, 'cnt');
  }

  function render($values) {
    return $values->{$this->user_field};
  }
}