I've posted a patch that adds views support to image_gallery module, essentially making the existing hardcoded galleries entirely with views.

The quick gist of it is that we need a "cover image" for each gallery in a list of galleries -- in other words, a list of terms needs a "representative node" for each term -- say cover node as it's shorter.

I've implemented several ways of getting this: relationships from term to node and also regular fields, with fairly complex options for how to handle subterms.

There is a detailed explanation of my approach in this comment: http://drupal.org/node/405456#comment-1466564

Predictably, someone has asked me if this could be generalized to non-gallery terms. Obviously it could, but image_gallery is no longer the place for it.
Would part of this approach have a place in Views module?

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

merlinofchaos’s picture

Yes, term to node and node to term relationships should be in Views itself. I should've done both of those long ago but I haven't had time recently.

joachim’s picture

If you let me know which parts of my patch you'd like for Views I can work on a patch for Views.

Taking another look at it just now, it occurs to me than instead of implementing several relationships for different options (eg first node by title, newest node, etc) it might be cleaner to have only one relationship with a slew of options. But then this is less easily extended by third party modules.

merlinofchaos’s picture

It's generally best to have a relationship with as few options as possible and rely on filters to provide the options. For terms, 'vocabulary' is the best thing to make the relationship use optionally.

joachim’s picture

You've lost me now...

My approach has been to use a subquery in the join, so the relationship does this:

LEFT JOIN node node_term_data ON ([SUBQUERY]) = node_term_data.nid

Each relationship implements a different subquery to get a cover node nid, eg:
- nodes ordered by creation date
- nodes ordered by title

The patch is here: http://drupal.org/files/issues/image_gallery_views_3_B.patch

joachim’s picture

So I had a bit of a brainwave this morning while still half asleep :D

To reiterate first, the current approach is made up of: (with the current names from the patch; these should become more generic)

- class image_gallery_join_subquery: expects to be given a subquery by the relationship handler that creates it. This creates a join where the left side of the join clause is a subquery that returns a single value. It can be used with any tables as long as the subquery it's given is correct.
- class image_gallery_handler_relationship_gallery_cover: this is used by several relationship data definitions. Each definition provides a different ORDER clause for the subquery. The relationship handler knows how to build the rest of the subquery.
- about three different relationship data definitions, each giving a different ORDER clause (eg, order nodes by title, by time created, etc). This is that way that users select how the gallery node is chosen: do they want the gallery cover image to be the most recent node, the first node by title, etc.

My new idea is: why not use another view to provide the subquery?

We'd then have:
- class image_gallery_join_subquery: as before, expects to be given a subquery for its left side of the clause.
- class image_gallery_handler_relationship_subquery: Provides a user option to choose a view, and code for checking the view is suitable, and code to get just the query SQL from this chosen view.
- relationship data definition: specifies that the chosen query must be a node query, and gives the correlated field to use in the query.

Benefits:
This would allow the user to set the sort order themselves, and save us having to keep defining another relationship whenever there's a feature request for another one.
The system would be lot more reusable, as a new relationship definition could allow something completely different with different bases (I can't think of useful examples yet... say the most recent ubercart order for each user?)

Questions I'm not sure about:
- how do you get a view to return just the query SQL without running it? I seem to remember I had problems doing this.
- also, the query SQL would have to be built with a dummy argument for the correlated field -- we want the SQL to come back with a %s where we can put the name of the correlated field.

dawehner’s picture

i think http://drupal.org/node/488314 is what you need :)

joachim’s picture

Won't that will return ALL nodes for each term? That just joins term via term_node to node, so if several nodes have that term, the relationship join will produce multiple rows.

That's useful in some cases, but not what we're after here.
The idea here is to get ONE node for each term that is a sort of 'characteristic' for that term.

For image galleries, that translates as the 'cover image node' for each gallery term.
Here's another example use case:

Suppose I have a node type 'animals' and a taxonomy 'species'.
I want to make a list view of species, with the latest animal in each one, like this:

  Horse   Latest horse node title   Latest horse imagefield  Latest horse author
  Cat     Latest cat node title     Latest cat imagefield    Latest cat author
  ...

My original approach can provide a handful of relationships that cover major cases like "latest created", "first created", "first by node title".

But this new approach, if it's possible, would allow a user who has installed a voting module to set the sort criteria on the relationship subquery view themselves, and then get this:

  Horse   Favourite horse node title   Favourite horse imagefield  Favourite horse author
  Cat     Favourite cat node title     Favourite cat imagefield    Favourite cat author
  ...
joachim’s picture

Update from Drupalcamp UK :)

I've coded a fair bit of this approach and found a problem.
As soon as a sort is added to the subquery view, it gains an extra column. This means it can't be used as a subquery.
The way round this is to wrap it up in another SELECT. But then the outer reference field can't find its outer reference.
The full query is this:

SELECT term_data.tid AS tid,
   term_data.name AS term_data_name,
   term_data.vid AS term_data_vid,
   node_term_data.nid AS node_term_data_nid,
   node_term_data.title AS node_term_data_title
 FROM term_data term_data 
 LEFT JOIN node node_term_data ON (SELECT subquery.nid FROM (SELECT node.nid AS nid,
   node.title AS node_title
 FROM node node 
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 WHERE term_node.tid = tid  <--- this bit doesn't find the tid on the first line.
   ORDER BY node_title ASC
 LIMIT 1) AS subquery) = node_term_data.nid
 WHERE term_data.vid in ('4')

Longwave & I are looking at an alternative method without subqueries: http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-i...

Stay tuned :)

joachim’s picture

Quick update:
Doing the join method seems terribly complex as just about the entire view query needs to be recreated to join on to. This is because we're not just dealing with one table, but with a dataset that's probably made of several tables.
I've yet to manage to get this to work properly even as a bare query in phpmyadmin.

So I've gone back to the subquery approach. The relationship handler has an option that lets the user pick a sort -- this gives the criterion for picking the 'best' item in the subquery (eg comment count, post date).

It all works :)
Just need to get it into a fresh checkout of views and roll a patch.

joachim’s picture

Title: Relationships from terms to nodes » Groupwise maximum ('representative') relationships
Component: taxonomy data » Code
Status: Active » Needs review
FileSize
3.94 KB
17.88 KB

Here's a patch on views HEAD.

It contains the following:
- a join handler that allows joins with a subquery as the left hand side of the join clause. This is a subclass of views_join.
- a relationship handler that allows groupwise maximum relationships to be defined. For end-users I'm calling these 'representative relationships' as 'groupwise maximum' is a pretty awful term. This allows users to pick a sort within the relationship options to determine the 'maximum' part -- the criterion for choosing a representative in other words. When the user options are saved this whips up a temporary view to generate a query string which is stored. The handler uses this and the subquery join to add itself to the query when it is run in a view.
- a relationship from terms to nodes that uses the handler.
- a help file

This works perfectly for the relationship from terms to nodes :)
You can create a term view and add to it a representative node that gives you...
- the latest node for each term
- the node with the most comments
- the first node by title alphabetically
- ... and so on. Just pick a different sort in the drop-down in the relationship options.
I've attached an example view.

However... when I tested a relationship from users to nodes I found a problem: the field name I need for the outer reference in the subquery is the same as the base field of the subquery, so the WHERE clause in the subquery becomes a tautology and fails completely.
The upshot is that for this to work on other bases, it needs a way of altering all the aliases in the subquery.
Ie, we currently get a subquery of:
SELECT node.nid AS nid
FROM node node
INNER JOIN users users ON node.uid = users.uid
WHERE users.uid = users.uid
ORDER BY node.created DESC
LIMIT 1

and we need something like:
SELECT node.nid AS nid_INNER
FROM node node_INNER
INNER JOIN users users_INNER ON node_INNER.uid = users_INNER.uid
WHERE users_INNER.uid = users.uid /* this one is the outer reference */
ORDER BY node_INNER.created DESC
LIMIT 1

But I am stumped and it's late -- anyone got any ideas?

I am marking as 'needs review', because as it is this is fine for the term relationship.
A subsequent improvement to how the subquery is generated so it works on users won't break that.

joachim’s picture

Status: Needs review » Needs work

Got it working -- I have in front of me a view of users showing the latest node for each one :)

Basically, I've added some stuff to generate the query string differently for our subquery:
- a dummy view class whose only change from the regular query class to...
- our own query class that puts a namespace on all tables.

These classes are only needed when the user saves the relationship options on the main view, as they're just used in options_submit() to generate the subquery which is then saved. So they don't even need to be loaded with the handler. So before I roll a patch: should I put them in an INC file in the same folder as the handler and load it with include_once, or in the includes folder and use views_include?

joachim’s picture

Status: Needs work » Needs review
FileSize
4.04 KB
2.81 KB
26.8 KB

Patch :)
All works.

Try either:
- term view, with most recent node for each term
- user view, with most recent node from each user.

I tried seeing if I could implement a 2nd method that lets the user pick another view to generate the subquery -- this would let you do "List of users with their most recent nodes of type X", but I ran into horrendous problems with namespacing all the tables: can't figure out how to consistently change the aliases for all tables and the fields that are on them. I reckon we can leave this extra feature till later though.

jdblank’s picture

joachim

I think this patch is exactly what I am looking for. I applied it against the latest head. Can you provide any instructions on how to implement a view as outlined below:

I have a cck type called catalog-Item and a taxonomy vocabulary called Catalog-Categories that is associated with my CCK type.

I would like to create a view that pulls one node per vocabulary term.

So I have three nodes:

Category (term) | Node Title

Breaker | Product 1
Breaker | Product 2
Generater | Product 3

And I want the View to return the following:

Breaker | Product 1
Generator | Product 3

joachim’s picture

Like this:
- create a term view that shows the terms for your vocab
- add a relationship for 'representative node'
- set the sort order -- not sure what it is you want from your example. Maybe node title?
- add the node title field on the relationship.

jdblank’s picture

Thanks.

I don't see an option under relationship for 'representative node' maybe the patch did not apply?

I would like node title and node image field to display.

joachim’s picture

@15: clear the Views cache.
You can get anything you like on the node to display -- just add fields.

jdblank’s picture

OK I think I am getting closer.

I cleared the views cache and was able to set up the relationship.

If I set node title to us "Representative Node" i still get two results displaying in the preview code for the same taxonomy term. The node titles are the same, the displayed taxonomy term is displayed but the image paths are different (which is odd since the image path should be tied to the node title).

Circuit Breakers [taxonomy term]
Catalog Item 2 [node title]
/yc156-arc-grid2-web.jpg [node image path]

Circuit Breakers [taxonomy term]
Catalog Item 2 [node title]
/catalog-images/05035d.jpg [node image path]

If I set all fields to be Representative Node then I get two results returned in the preview code although each item is identical but still I think there should be just one result returned.

Circuit Breakers [taxonomy term]
Catalog Item 2 [node title]
/catalog-images/05035d.jpg [node image path]

Circuit Breakers [taxonomy term]
Catalog Item 2 [node title]
/catalog-images/05035d.jpg [node image path]

Thanks for your help on this!

joachim’s picture

You may be getting terms multiple times simply because of the term view itself -- try making the view again but only with the term stuff. See what happens with that.

jdblank’s picture

I set up a brand new view but it still returns two items of the same term. I only used Node Term as the field and filter by node type.

Here is the SQL code if that helps:


SELECT node.nid AS nid, node_term_data__term_data.name AS node_term_data__term_data_name, node_term_data__term_data.vid AS node_term_data__term_data_vid, node_term_data__term_data.tid AS node_term_data__term_data_tid FROM node node  LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN node node_term_data ON (SELECT node_INNER.nid AS nid FROM node node_INNER  INNER JOIN term_node term_node_INNER ON node_INNER.vid = term_node_INNER.vid WHERE term_node_INNER.tid = term_data.tid ORDER BY node_INNER.title DESC LIMIT 1) = node_term_data.nid LEFT JOIN term_node node_term_data__term_node ON node_term_data.vid = node_term_data__term_node.vid LEFT JOIN term_node node_term_data_node_term_data__term_node ON node_term_data.vid = node_term_data_node_term_data__term_node.vid LEFT JOIN term_data node_term_data__term_data ON node_term_data__term_node.tid = node_term_data__term_data.tid WHERE node.type in ('catalog_item')

joachim’s picture

I meant only do the term part -- don't add the relationship. That bit alone might be returning too many rows.
Though that view's SQL looks wrong -- you sure that's a term view? Surely it shouldn't have node as its first table in that case?

jdblank’s picture

OK I was creating a node type view not a taxonomy type view.

Now it is just about working except for one thing. The Preview returns just one term and image path but it also brings up two empty results:

Term: Circuit Breakers
/catalog-images/05035d.jpg

Term:

Term:


SELECT term_data.tid AS tid, node_term_data_node_data_field_catalog_item_image.field_catalog_item_image_fid AS node_term_data_node_data_field_catalog_item_image_field_catalog_item_image_fid, node_term_data_node_data_field_catalog_item_image.field_catalog_item_image_list AS node_term_data_node_data_field_catalog_item_image_field_catalog_item_image_list, node_term_data_node_data_field_catalog_item_image.field_catalog_item_image_data AS node_term_data_node_data_field_catalog_item_image_field_catalog_item_image_data, node_term_data.nid AS node_term_data_nid, node_term_data.type AS node_term_data_type, node_term_data.vid AS node_term_data_vid, node_term_data__term_data.name AS node_term_data__term_data_name, node_term_data__term_data.vid AS node_term_data__term_data_vid, node_term_data__term_data.tid AS node_term_data__term_data_tid FROM term_data term_data  LEFT JOIN node node_term_data ON (SELECT node_INNER.nid AS nid FROM node node_INNER  INNER JOIN term_node term_node_INNER ON node_INNER.vid = term_node_INNER.vid WHERE term_node_INNER.tid = term_data.tid ORDER BY node_INNER.created DESC LIMIT 1) = node_term_data.nid LEFT JOIN content_type_catalog_item node_term_data_node_data_field_catalog_item_image ON node_term_data.vid = node_term_data_node_data_field_catalog_item_image.vid LEFT JOIN term_node node_term_data__term_node ON node_term_data.vid = node_term_data__term_node.vid LEFT JOIN term_node node_term_data_node_term_data__term_node ON node_term_data.vid = node_term_data_node_term_data__term_node.vid LEFT JOIN term_data node_term_data__term_data ON node_term_data__term_node.tid = node_term_data__term_data.tid WHERE term_data.vid in ('8')
joachim’s picture

Once again: please make sure your terms show properly WITHOUT the relationship or any of its fields. If that works ok then we know it's a problem with this patch.

jdblank’s picture

Sorry for not being clear, with just a Term View filtered by my vocabulary the results returns as expected:

Term: Circuit Breakers
Term: Generators
Term: Load Centers

SELECT term_data.tid AS tid, term_data.name AS term_data_name, term_data.vid AS term_data_vid FROM term_data term_data  WHERE term_data.vid in ('8')

There are two nodes created under Circuit Breakers and no nodes under the other tems so with Representative Relationship I am expecting

Term: Circuit Breakers & One Image path from a representative node to be returned which is happening but then I get two extra empty results returned.

joachim’s picture

Ah right -- this happens with any relationship unless you tick the 'Require this relationship' box. I've just tested this on the 'most recent node per user' test view and it works fine.

jdblank’s picture

OK this is now working as expected. Is this patch going to make it into Views?

joachim’s picture

Has anyone tried one of the views while not logged in as UID 1?
I wonder if this might have an effect: http://drupal.org/node/151910
If it causes problems, an alternative approach might be to use http://api.drupal.org/api/function/db_query_temporary/6

sun’s picture

Leeteq’s picture

Subscribing.

tizzo’s picture

Version: 6.x-3.x-dev » 6.x-2.x-dev
Status: Needs review » Needs work

Thanks for this GREAT patch! I spent a good deal of time today figuring out how to do just this before merlinofchaos turned me onto this patch. The one problem I've discovered is that with some choices selected in the "Representative sort criterion:" the field will not be properly prefixed with the table it belongs to. Example: I selected "Comment: Id" and received a SQL error. The relevant part of the query is as follows:

term_node_INNER.vid LEFT JOIN comments comments_INNER ON node_INNER.nid = comments_INNER.nid WHERE term_node_INNER.tid = term_data.tid ORDER BY .cid DESC LIMIT 1)

Note that commets is properly joined but in the ORDER BY section the .cid doesn't have a table prefixed to it. It should read comments_INNER.cid. Manually changing it and running the same query from the MySQL terminal works properly.

I've found the same behavior with all book, content, comment, taxonomy and user options. Node and search options work fine.

I don't have time to trouble shoot this at the moment but will try to make time for it soon. This is a fantastic addition to views!

joachim’s picture

Status: Needs review » Needs work

Urgh. I hadn't tested comments.
The problem is that the subquery has to be namespaced -- the 'INNER' suffix on everything -- and that requires a version of the Views object (class view_aliased) that's hacked in several places.
IIRC I left comments at all the points of hackery, so it should be figurable if you get to this before I do :)

jumpfightgo’s picture

Thanks for your work on this. subscribing.

merlinofchaos’s picture

Version: 6.x-2.x-dev » 6.x-3.x-dev
Status: Needs work » Needs review

I would like to get this into Views 3.x now that we're seriously working on it, so I'd really love to get some extra attention on this. I apologize that I've let this fall off my radar in all the bajillion thigns I have to do, but this is a really cool feature, often requested. Let's see if we can get this into something we'd like to actually commit.

joachim’s picture

The problem this was hitting was reliably producing a query for a view in which everything has a namespace prefix -- in the View 2.x query object, prefixes for tables and fields seem to come from a whole load of different places, and I remember problems with them overlapping but not covering everything (eg, I'd get some table names with no prefix at all, then fixing that would result in fieldnames with the prefix twice!).

So the first thing would be to see how this will work with the 3.x query building system.

dagmar’s picture

Version: 6.x-2.x-dev » 6.x-3.x-dev

Ok, for now this comment is basically a "subscribe".

I have applied this patch using views 3. It applies, however (as probably we know) this doesn't work.

Some comments to define several tasks:

views_query_aliased should be now a query plugin.

All the include views_include("query") should be removed.

I will see how can I help with this issue soon.

joachim’s picture

A quick recap of how this works, as much for my benefit as anyone else's ;)

1. Define some new relationships, using a new relationship handler. Eg: 'Term: Representative node' is a relationship from term_data -> node.
2. The options form for the relationship lets the user pick a sort (or possibly even a whole view). Eg: 'Creation time: Desc.'
3. When the options are saved, the handler builds a view on the fly, based on the relationship's base table and correlating argument and the sort. For performance, the SQL for this view is saved as a string in the handler options.
4. This saved SQL is used as the right hand side of the relationship join clause, so we get the main view like this:

SELECT blah FROM term_data LEFT JOIN node ON node.nid = (SELECT blah FROM node_INNER WHERE innerclause ORDER BY sortclause LIMIT 1)

5. This gets us, for example, a list of taxonomy terms with for each term its most recent node. The relationship defined the inner query's base table and argument for the correlating clause; the sort order the user selected defined the sort clause.

It's crucial that everything in the inner query be namespaced, except for the argument clause which is taken care of by the relationship handler.

So the biggest problem to solve and the first we should tackle is a query plugin that cleanly does this. Setting up the relationship handler and defining the relationships is phase 2.

I'm attaching a patch with a first go at this: views_plugin_query_aliased. This includes a hack in view.inc to use that plugin instead of the regular one -- we can use this to test, since the namespacing should be such that the resulting query runs perfectly normally.

This doesn't work yet ;)

Like with the Views 2 query class, aliases are set in a lot of places and I'm finding it tricky to get them all. There's also a lot of redundancy; eg I'm having to override query() and change just one line to alias the base table, which is not terribly clean or maintainable.

So I suggest we look at making changes to views_plugin_query_default so generating and adding aliases happens in separate methods. I could really use help on this from people who are more familiar with the query plugin class :D

joachim’s picture

Currently on other stuff; just stopping by to report a conversation merlinofchaos and I had on IRC, in which he said subclassing views_plugin_query should only be done for database implementations; hence we shouldn't make views_plugin_query_aliased but rather change views_plugin_query to allow easy prefixing across the whole query.

dawehner’s picture

Issue tags: +views 3.x roadmap

add tag

vitis’s picture

sub

davideads’s picture

Mostly subscribing. I'd really love to see this -- I'd like to be able to build a relationship handlers that can a) get the most recent comment on a node and b) get all comments with a given flag (using flag.module) and/or other criteria.

@joachim -- do you have a better idea of what you'd do to accomplish this, 6 months on?

dawehner’s picture

FileSize
20.38 KB

Here is a first working version for drupal-6--3, where working is not brake something :)

Here is the example i used:

$view = new view;
$view->name = 'users';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'users';
$view->is_cacheable = FALSE;
$view->api_version = 3.0-alpha1;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: User: Representative node */
$handler->display->display_options['relationships']['status']['id'] = 'status';
$handler->display->display_options['relationships']['status']['table'] = 'users';
$handler->display->display_options['relationships']['status']['field'] = 'status';
$handler->display->display_options['relationships']['status']['required'] = 0;
$handler->display->display_options['relationships']['status']['subquery_sort'] = 'users.uid';
$handler->display->display_options['relationships']['status']['subquery_regenerate'] = 1;
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['name']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['name']['alter']['trim'] = 0;
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['name']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['name']['alter']['html'] = 0;
$handler->display->display_options['fields']['name']['hide_empty'] = 0;
$handler->display->display_options['fields']['name']['empty_zero'] = 0;
$handler->display->display_options['fields']['name']['link_to_user'] = 1;
$handler->display->display_options['fields']['name']['overwrite_anonymous'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['relationship'] = 'status';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 0;
joachim’s picture

Yup, nothing's broken :)

The namespacing isn't getting applied to the inner query though:

SELECT users.name AS users_name,
users.uid AS uid,
node_users.title AS node_users_title
 FROM users users 
 LEFT JOIN node node_users ON (SELECT users.uid AS users_uid
 FROM node node 
 INNER JOIN users users ON node.uid = users.uid
 WHERE users.uid = users.uid <-- that is probably meant to be the correlating bit.
   ORDER BY users_uid DESC
 LIMIT 1) = node_users.nid

Also, a sort order of user ID in the example view doesn't make sense for this relationship. All the inner nodes have the same user id! It may make sense to limit the available sorts to those that apply to the relationship's new base.

dawehner’s picture

OH i was so happy that i got a result what i expected(yes it was random data).

At least we have some progress here.

joachim’s picture

set_namespace() never gets called.

I'm not sure at what point this should happen.

Currently the groupwise relationship handler does this:

  /**
   * Helper function to create a pseudo view with a namespace added.
   */
  function view_aliased() {
    views_include('view');
    $view = new view();
    $view->vid = 'new';
    $view->add_display('default');
    
    dsm($view);
    // we can't set_namespace here -- these is no query!

    return $view;
  }

So it looks like we need to set the namespace as a property of the $view, which it can then propagate to the $query when it builds one.

dawehner’s picture

So here is a new version which has a working preview

SELECT users.name AS users_name,
users.uid AS uid,
node_users.title AS node_users_title
 FROM users users 
 LEFT JOIN node node_users ON (SELECT users.uid AS users_uid
 FROM node node_INNER 
 INNER JOIN users users_INNER ON node_INNER.uid = users_INNER.uid
 WHERE users_INNER.uid = users.uid
   ORDER BY users_uid ASC
 LIMIT 1) = node_users.nid
joachim’s picture

(SELECT users.uid AS users_uid

Should be user_INNER surely?

Also: ORDER BY users_uid ASC

dawehner’s picture

This was the part i couldn't figure out yet and asked earl and got distracted.

Would this query be right?

SELECT users_INNER.uid AS users_uid
FROM node node_INNER
INNER JOIN users users_INNER ON node_INNER.uid = users_INNER.uid
WHERE users_INNER.uid = users.uid
   ORDER BY users_uid ASC
LIMIT 1)

The field alias part might be optional for sort. There is a lot of stuff to be done for this patch.

joachim’s picture

Looking better, but can we be sure that 'AS users_uid' won't clash with an alias Views uses in the outer query?

The query as a whole doesn't make sense anyway because the correlation is equating a nid with a uid, isn't it?

dawehner’s picture

That's a bug which has a patch already :)

joachim’s picture

I've applied the latest patch, and I'm still getting it.

Something must have changed in Views 3 -- the sort order is getting added as the first column, whereas what we need is the base table's ID field, and *only* that.

(SELECT node_INNER.created AS node_INNER_created
 FROM node node_INNER 
 INNER JOIN users users_INNER ON node_INNER.uid = users_INNER.uid
 WHERE users_INNER.uid = users.uid
   ORDER BY node_INNER_created DESC
 LIMIT 1)
donquixote’s picture

guys, you don't need a patch for this.
- make a taxonomy view
- add a relation (node)
- add some fields (such as an imagefield)
- important: set Distinct = Yes.

Or did I miss something?

merlinofchaos’s picture

joachim: In Views 3, due to the possibility of grouping, I believe the base table field is now only added if specifically requested. This might be the problem?

joachim’s picture

@donquixote: yes, probably. Can your solution get me: a) the most recent node per term, b) the first node per term, c) the most commented node per term, d) the node with most votes per term?

@merlinofchaos: yup, that's part of it. Also the sort field is getting added to the SELECT. That's not some wacky Postgres support is it?

donquixote’s picture

@joachim (#52):
You are right. Any sort orders specified for the nodes within a term have no effect on which node is chosen. I don't know why, but I guess this is what you are trying to do with your patches.
In this case, I am curious for the next patches.

joachim’s picture

Hmm, so given a base table, eg 'node', how do I determine the right ID field to add to the view?

joachim’s picture

NM figured that out :)

Here is an updated patch.....

This also fixes a bug we hadn't yet found -- the base table for the relationship wasn't being set as the base table of the new temporary view (mostly because so far we're always using {node}).

However, this is broken because the sort field is getting added as a SELECT field.

dawehner’s picture

http://drupal.org/node/470258#comment-3054078

The exported view worked fine.
The user-id get's added in this case, because user:name needs the uidu

joachim’s picture

I'm not sure that demo view makes sense. I seem to remember it doesn't have a sensible sort option for the relationship.

Try getting the latest node per user. The problem is that both the base ID AND the sort field are getting added to the query -- they can't. A subquery MUST have only one field in the SELECT.

How do we stop Views from adding the sort field to the SELECT?

joachim’s picture

Here is a view to list users and their most recent node.

It's broken ;)

$view = new view;
$view->name = 'representative_users';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'users';
$view->is_cacheable = FALSE;
$view->api_version = 3.0-alpha1;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: User: Representative node */
$handler->display->display_options['relationships']['status']['id'] = 'status';
$handler->display->display_options['relationships']['status']['table'] = 'users';
$handler->display->display_options['relationships']['status']['field'] = 'status';
$handler->display->display_options['relationships']['status']['required'] = 0;
$handler->display->display_options['relationships']['status']['subquery_sort'] = 'node.created';
$handler->display->display_options['relationships']['status']['subquery_regenerate'] = 1;
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['name']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['name']['alter']['trim'] = 0;
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['name']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['name']['alter']['html'] = 0;
$handler->display->display_options['fields']['name']['hide_empty'] = 0;
$handler->display->display_options['fields']['name']['empty_zero'] = 0;
$handler->display->display_options['fields']['name']['link_to_user'] = 1;
$handler->display->display_options['fields']['name']['overwrite_anonymous'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['relationship'] = 'status';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 0;
joachim’s picture

And what on earth is this?

$handler->display->display_options['relationships']['status']['id'] = 'status';
$handler->display->display_options['relationships']['status']['table'] = 'users';
$handler->display->display_options['relationships']['status']['field'] = 'status';

That looks all wrong!

Why is the rep rel on the 'status' field?

  // published status
  $data['users']['status'] = array(
    'title' => t('Active'), // The item it appears as on the UI,
    'help' => t('Whether a user is active or blocked.'), // The help that appears on the UI,
     // Information for displaying a title as a field
    'field' => array(
      'handler' => 'views_handler_field_boolean',
      'click sortable' => TRUE,
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_boolean_operator',
      'label' => t('Active'),
      'type' => 'yes-no',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'relationship' => array(
      'title' => t('Representative node'),
      'label'  => t('Representative node'),
      'help' => t('Obtains a single representative node for each user, acccording to a chosen sort criterion.'),
      'handler' => 'views_handler_relationship_groupwise_max',
      'base'   => 'node',
      'field'  => 'nid',
      'outer field' => 'users.uid',
      'argument table' => 'users',
      'argument field' =>  'uid',
    ),
    
  );
joachim’s picture

New patch with that corrected -- should be on an alias of uid, not status.

Here is the exported view to go with this patch:

$view = new view;
$view->name = 'representative_users';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'users';
$view->is_cacheable = FALSE;
$view->api_version = 3.0-alpha1;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: User: Representative node */
$handler->display->display_options['relationships']['uid_representative']['id'] = 'uid_representative';
$handler->display->display_options['relationships']['uid_representative']['table'] = 'users';
$handler->display->display_options['relationships']['uid_representative']['field'] = 'uid_representative';
$handler->display->display_options['relationships']['uid_representative']['required'] = 0;
$handler->display->display_options['relationships']['uid_representative']['subquery_sort'] = 'node.created';
$handler->display->display_options['relationships']['uid_representative']['subquery_regenerate'] = 0;
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['name']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['name']['alter']['trim'] = 0;
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['name']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['name']['alter']['html'] = 0;
$handler->display->display_options['fields']['name']['hide_empty'] = 0;
$handler->display->display_options['fields']['name']['empty_zero'] = 0;
$handler->display->display_options['fields']['name']['link_to_user'] = 1;
$handler->display->display_options['fields']['name']['overwrite_anonymous'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['relationship'] = 'status';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 0;
davideads’s picture

I got this working with a change in the `add_orderby` method of `views_plugin_query_default`. I opened a new ticket for it: #844910: Change behavior of add_orderby. I also had to require the relationship -- otherwise the join sub-query didn't get written.

What I'd really like to be able to do is to swap in a View as the input to the sub-query. I'm not sure if I fully understand the namespacing issues in play, but I think it would be highly useful to allow the "representative node/entity" to be filtered by flag status and sorted by a weight field, or some other arbitrary criteria.

davideads’s picture

Thinking about #844910: Change behavior of add_orderby got me pondering how to implement this. At first, I though it should possibly be split out as a views module, but I think it might be hard to pull off and require some clever hacks around the Views API. Another option that occured to me, however, is that it might make sense to allow relationships to have a "use subquery" setting, along with some appropriately stern warnings about performance dangers and/or unexpected results.

If this setting is enabled, the relationship handler would create a subquery join instead of a regular join -- basically pushing Joachim's patch deeper into Views. I don't see a good way around that, currently, if this functionality is to happen...

When enabled:

  • Any sort or filter handlers will be applied to the generated subquery and used as join clauses to select statements which always and only return the proper entity ID to make the joining happen.
  • I think inspecting hook_views_data could allow us to determine the right way to create the subquery.
  • Perhaps the join TYPE could also be specified, but that might be gettin' too crazy.
  • The subquery's tables aliases get namespaced with the relationship's machine name.

So, instead of "embedding" one view as a subquery of another, subquery relationships simply add their filter and sort handlers (which use the relationship!) to the subquery and not the main query. My operating hypothesis is that this will enable arbitrarily filtered "representative entities", which would be really freakin' cool.

joachim’s picture

> Another option that occured to me, however, is that it might make sense to allow relationships to have a "use subquery" setting, along with some appropriately stern warnings about performance dangers and/or unexpected results.

You mean as a views_data setting?
I don't think it makes sense in the UI, as the relationships that use representative and those that don't are often quite different. Eg there's currently no user->node relationship.

What I'm quite curious about is you seem to be suggesting any sort or filter could get added to the subquery. If we had this in the UI, it would mean a representative relationship would add TWO, not one, options to the 'relationship' option on the handlers:

- add the handler on the relationship
- add the handler to the subquery

> So, instead of "embedding" one view as a subquery of another, subquery relationships simply add their filter and sort handlers (which use the relationship!)

Yikes!!! That's going to take a LOT of replumbing I think!

davideads’s picture

You mean as a views_data setting?

No, as an option of the relationship handler.

I don't think it makes sense in the UI, as the relationships that use representative and those that don't are often quite different. Eg there's currently no user->node relationship.

But you can *create* a user->node relationship...

I'm going to play around with this -- I think it might be easier than you suspect.

davideads’s picture

Yup, you are right. My approach might require a ton of replumbing. I was hoping I could introspect the query object in views_join_subquery and modify it somehow, but I don't think there's a enough context / information about handlers in that object to mangle the query properly....

davideads’s picture

Here's a version of this patch where using another view for the subquery seems to work, at least in preliminary testing. Namespacing was seemingly working properly for me, so I added a namespace option to the relationship's options, and the patch forces the proper field no matter what fields are set in the included view.

joachim’s picture

I am actually starting to think the replumbing approach might be pretty cool.

Consider this:

Currently, a view goes through its handlers, and each one gets to affect the $query object. Once they've all run, the $query object assembles an SQL query. Some handlers are marked as being on a particular relationship, and that affects what they do to the query.

Suppose now:

A relationship handler is marked as being a subquery. The $query object knows to start up a subquery. This has its fields set, but sorts and filters may be marked as being in the relationship subquery, and they get added to this.

Like I said, a lot of replumbing, and the innards of how the query object gets build it mostly a black box to me -- so far I've just tinkered on the edges.

Any thoughts from merlinofchaos or dereine or anyone more familiar with the innards?

davideads’s picture

A relationship handler is marked as being a subquery. The $query object knows to start up a subquery. This has its fields set, but sorts and filters may be marked as being in the relationship subquery, and they get added to this.

That's exactly what I'm saying. It is hard to see where to hook in to make this happen, though, if it is even possible.

At the same time, with refinement, I think the approach you use for embedding another View as the subquery is sane and works with the current model -- no replumbing required. Given that D7's database layer is going to be very different, and Views 3 isn't an overhaul like Views 2 was, it seems like it could be a lot of work, break compatibility, and still wind up being obsolete pretty quickly. I think the Views API is nuts, and the mixed use of dispatch/build and OO techniques is unholy. But it works, and developers know it...

davideads’s picture

Also, I've already noted several problems in the last patch. I should have another today.

davideads’s picture

Dang. This is tricky. I force-required the relationship in the last patch, but that doesn't seem necessary for the relations you've added -- they seem to work properly both ways now.

Now I'm trying to create a "representative comment" for a given node. I'd LOVE to be able to get either the latest comment or even better, the latest comment with a specific flag. My use case is to show a list of "ticket" nodes, and then show the comment flagged as the resolution. The correlation in the query doesn't seem to be getting replaced properly. Also, the comment field handler adds nid to additional fields no matter what, which also screws up the select statement, again.

Maybe I just need a different structure, but that is also a pain -- I don't want to muck with Drupal entities; I'd rather just use a different system, which probably isn't much of an option.

joachim’s picture

> I'd LOVE to be able to get either the latest comment

If you need this right now, you should be able to do this with one of the older patches on Views 2. Views 3 won't work at all due to #844910: Change behavior of add_orderby.

> or even better, the latest comment with a specific flag

Again, if this is something you need right now, you probably need to go with custom SQL.

> Also, the comment field handler adds nid to additional fields no matter what, which also screws up the select statement, again.

The idea of handlers switching to the subquery would need to be very deep in them -- everything they do has to affect ONLY the subquery and not the main one. Everything a handler does would need to test this.

davideads’s picture

If you need this right now, you should be able to do this with one of the older patches on Views 2. Views 3 won't work at all...

I should mention that this is ASSUMING #844910: Change behavior of add_orderby is applied, seeing as I wrote it (:

> Also, the comment field handler adds nid to additional fields no matter what, which also screws up the select statement, again.

The idea of handlers switching to the subquery would need to be very deep in them -- everything they do has to affect ONLY the subquery and not the main one. Everything a handler does would need to test this.

Handlers shouldn't have to know WHAT they are contributing to; I suspect that at least some of the handlers (like the comment field handler) could be refactored to ONLY add the extra fields in the inherited handlers that actually must add the fields to allow display.

I can actually get the oldest/latest comment sort to work if I remove the additional NID field from the comment field handler's init function -- I guess I should look into the build function and see what (if any) hooks it calls. That might be an option.

When I try to use flag, the join that flag uses for its relationship is somehow losing the join table it is supposed to and causing the query to blow up.

joachim’s picture

> Handlers shouldn't have to know WHAT they are contributing to;

Yes they should!

Suppose you have a relationship that's a CCK noderef, and a filter on nodes.

That filter handler has to know if it is on the node base, or the relationship -- what it does to the query affects this.

Now suppose the relationship is a subquery one. The filter needs to know whether it's on the base, on the relationship to limit the overall results (ie, if the best related node isn't a FOO, skip the row), or if it's filtering within the subquery (ie, the best nodes, but only the FOOs).

Flag is going to be hairy -- because it's a relationship to the flag table. So now you're needing a relationship INSIDE the subquery.

Oh my head...

davideads’s picture

Yes they should!

Maybe I misstated this: With proper namespacing, a handler shouldn't have to know if it is contributing to a subquery or a regular query.

Flag is going to be hairy -- because it's a relationship to the flag table. So now you're needing a relationship INSIDE the subquery.

True enough. But a representative comment with a given flag for a node is what I need (:

And yeah, I could write the custom SQL for it and be done, but at the moment I've got some time to contribute to this while I wait for my coworker to finish an API he's building.

davideads’s picture

So I actually got this working today at work, but I had to leave to catch the train before I could write up my notes. I'll clean things up a bit and post a patch tomorrow.

As before, the crux of the matter is getting namespacing working properly. It isn't, still, but I hacked around it. I had to:

  • Add a proper argument handler in comment_views_data for the comment.nid database field. I'd thought I was doing something wrong in the "representative comment" handler I added tonode_views_data, but the issue was that Views didn't know how to add the argument because it wasn't registered.
  • The table passed to the subquery is already name-spaced, which messed up the join. I wrote a rather hackish solution (I strip out any namespace in the incoming table in the views_join query method, but I suspect using the setter method is the way to go -- currently I'm just changing the property of the object directly, and it might make sense to recalculate the proper table name for the join when the property is set.
  • I hacked up the comment field handler so it doesn't add the nid to the field list when it is initialized. Obviously, this will break things -- I need to improve my approach. I think I'll simply create a "plain cid" field handler.

So, my codebase is kind of a mess, but the upshot is that I was able to grab the oldest comment flagged "fixed" for a given node, and Views wrote the correct query and gave the right answer.

davideads’s picture

Okay, it was a little painful, and I'm still not really convinced the namespacing works in a particularly sane way, but I'm not convinced Views works in a particularly sane way all the time, either. Unfortunately, this patch is messing up the representative users view. Of course! I dunno if I have the bandwidth to tackle it anymore today, though.

davideads’s picture

Please disregard that last bit about the representative user view not working properly. It is working fine; I'd just forgotten to apply the add_orderby patch to my working copy.

joachim’s picture

Dereine and I looked at this a bit at Drupalcon CPH and we thought there wasn't much left to do... unfortunately I don't remember what we figured out.

Dereine, do you remember?

dawehner’s picture

There should be a piece of paper somewhere in my room. Omg i own some handwriting of joachim.

The problem was that

+      'outer field' => 'term_data.tid',

is no table alias.

It just get's replaced on the fly

+    // Replace the placeholders with the arguments.
+    _db_query_callback($args, TRUE);
+    $subquery = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $subquery);
+        
+    // Add in the outer field.
+    $subquery = preg_replace('/%d/', $this->definition['outer field'], $subquery);
Coupon Code Swap’s picture

+1 subscribing

Khalor’s picture

Also subscribing

nicholas.alipaz’s picture

subscribing

Leeteq’s picture

Should this issue be handled for 7.x (first)? (change the version to 7.x-3.x-dev?)

szantog’s picture

I was some problem to apply patch in #85 (i think, because of cvs), so i rerolled agains current 6.x-3.x-dev.

joachim’s picture

Version: 6.x-3.x-dev » 7.x-3.x-dev
Status: Needs work » Needs review

Changing version and status.

dawehner’s picture

@szantog
Nice patch, which just removes code :)

szantog’s picture

Uuups, wrong order HEAD HEAD^, sorry :)

joachim’s picture

If we're doing this on 7 first -- I'm guessing we should -- then DB stuff needs updating:

- _db_query_callback doesn't exist on 7.
- other things in left_query() such as the DB placeholders

> + 'title' => t('CID - Plain'),

Doesn't seem related to this issue... or the next chunk for comment data either.

dawehner’s picture

And things like

+    $subquery = preg_replace('/%d(?!.*%d.*)/', '%%d', $subquery);

will not work because you don't have access to the generated sql string.

joachim’s picture

We don't?
Yarggghhhh... how are we going to get the SQL to store in the handler options? Could storing a DB-TNG query object work instead?

dawehner’s picture

It's not impossible, but it's not easy. see views_query_alter or the search filter handler.

joachim’s picture

Status: Needs review » Needs work
FileSize
20.55 KB

Patch applies with fuzz, though not working.

Some things I've fixed:

- taxo --> node relationship declared in the wrong place
- handler inc files must be declared in the .info file
- Patches 84 and 87 lost the views_join_subquery class. I've put it back in but not got as far as seeing if it works on 7, due to problem below...

What I'm stumped on:

- After we build the temporary, inner view, we need to get a query we can work with. It can be an SQL string, or it could be a DBTNG object that we can manipulate -- the point is we *must* have something we can use, otherwise this feature can't work at all.

Here's a reroll with those fixes and the above marked with 'TODO-470258' where it needs looking at in the code.

dawehner’s picture


+    $subquery = $temp_view->build_info['query'];

This is the dbtng query object, but it's really not that easy to alter it, because dsm() does not work as you expect it.
I highly recommend to change all this variable to protected/public for development in dbtng.

joachim’s picture

Brilliant, thanks!

So we can get the SQL out with this:

$subquery->__toString()

and that's nice and easy.

However, I now remember that we are blocked by this: #844910: Change behavior of add_orderby.

The reason is that currently the SQL from the inner view is this:

SELECT node.nid AS nid, node.created AS node_created
FROM 
{node} node
INNER JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (taxonomy_index.tid = :db_condition_placeholder_0) ))
ORDER BY node_created DESC

That simply can't be used for a subquery because it has more than one field -- a subquery must return only one value so it can be used in an expression. The node.created AS node_created must go. That's what #844910: Change behavior of add_orderby is about.

dawehner’s picture

I'm not 100% sure whether you should rely on __toString. It's probably better to alter the query object itself even if is might hard.

There are some examples how to alter a query object:

modules/search/views_handler_filter_search.inc
views.module

joachim’s picture

We need the inner query as a string so we can put it in the outer query as an expression:

SELECT outer FROM outer_stuff WHERE (SELECT inner FROM inner_stuff) = argument

dawehner’s picture

There is no problem in using subqueries as a query object in dbtng. It automatically builds it when building it at the end.

But there is a bug #1112854: Subqueries use wrong arguments

joachim’s picture

> There is no problem in using subqueries as a query object in dbtng. It automatically builds it when building it at the end.

That's neat!

But as far as I can tell it can't do a correlated field.
Here's some demo code which you can stick in a menu callback.
It doesn't work :)

  /* desired query: lists all users with the most recent node of each user.
  
  SELECT u.uid AS uid, u.name AS name, u.status AS status, u.created AS created, u.access AS access, n.nid AS nid, n.title AS title FROM users u
    LEFT OUTER JOIN node n ON n.nid = 
    	(SELECT n_inner.nid FROM node n_inner 
    		WHERE n_inner.uid = u.uid -- correlated field here
    		ORDER BY n_inner.created LIMIT 1
    	) 
    WHERE (u.uid <> 0) LIMIT 50 OFFSET 0
    */
  
  $output = '';
  
  // Make a subquery.
  $subquery = db_select('node', 'n_inner');
  $subquery->condition('n_inner.uid', 'u.uid', '=')
    // The problem seems to be that since 'u.uid' is unknown at this point, it is treated as
    // a variable and we end up with (n_inner.uid = :db_condition_placeholder_0) 
    // being stored in the SelectQuery object.
    // You can see the same thing happen if you do, for eg, condition('n_inner.uid', 'cake', '=')
    ->orderBy('created', 'DESC')
    ->range(0, 1);
   
  // Check its SQL. 
  $output .= $subquery->__toString() . "<br>";  
  
  $options = array(
    'fetch' => PDO::FETCH_ASSOC,
  );
  $query = db_select('users', 'u', $options);
  
  $fields = array('uid', 'name', 'status', 'created', 'access');
  $query->condition('u.uid', 0, '<>')
    ->fields('u', $fields)
    ->range(0, 50);
    
  $query->leftJoin('node', 'n', 'n.nid = :subquery', array(':subquery' => $subquery));
  $query->fields('n', array('nid', 'title'));
  
  $tables =& $query->getTables();
  dsm($tables);
  
  $c = $tables['n']['arguments'][':subquery']->where->conditions();
  dsm($c);
  // This still has 'u.uid' set, but its tostring method goes in and changes that to ':db_condition_placeholder_0'.
  // Hence it looks like there is no way to get a correlated field in a subquery.
  
  $result = $query->execute();  
  
  // Set everything in the SelectQuery class to public to get stuff out here.
  dsm($query);
  
  $output .= $query->__toString() . "<br>";
  
  
  // Retrieve all records into an indexed array of stdClass objects.
  $users = $result->fetchAll();
  dsm($users);
  
  $output .= theme('table', array(
    'header' => $fields,
    'rows' => $users,
  ));
joachim’s picture

The above isn't a showstopper -- just means we have to pull out the SQL from the subquery SelectQuery object and put it in as a condition on the main query's join. In raw non-Views code I've tried, that works fine.

Also, #844910: Change behavior of add_orderby we can work around by using query alteration and basically hacking out the fields we don't want ;)

But we're now held up by #1157408: DatabaseCondition->condition() called with incorrect parameters :)

joachim’s picture

patch of work so far...

joachim’s picture

Good news!

The subquery SQL is now ok! It's a bit fragile as I've had to hack about and use workarounds, but we get the right SQL so that'll do for now :)

Next problems:

1. The relationship data is broken.

Taxonomy needs a complete rewrite to account for FieldAPI. User should be simple, but the 'real field' key here is not having any effect:

  $data['users']['uid_representative'] = array(
    'real field' => 'uid', // TODO-470258: This does not seem to be working!
    'relationship' => array(
      'title' => t('Representative node'),
      'label'  => t('Representative node'),
      'help' => t('Obtains a single representative node for each user, acccording to a chosen sort criterion.'),
      'handler' => 'views_handler_relationship_groupwise_max',
      'base'   => 'node',
      'field'  => 'nid',
      'outer field' => 'users.uid',
      'argument table' => 'users',
      'argument field' =>  'uid',
    ),
  );

2. The new handler's call to add_relationship() does not seem to be doing the right thing.

dawehner’s picture

Does it help if you move the real field into the relationship array?

dawehner’s picture

here is a new version which just fixes the problem from above. It uses 'relationship field' instead of 'real field'

joachim’s picture

Status: Needs work » Needs review
FileSize
25.05 KB

Here is a patch that WORKS!!! :D

To test:

- create a user view
- add the 'User: Representative node' relationship, specifying 'Content: Updated date' as the Representative sort order.
- add some node fields on this relationship.

This will show you a list of users, with data from each user's most recent node.

Taxonomy is working too: set that up similarly to show you the most recent node for each term.

The node --> comment relationship I've not tried.

There are a few workarounds for other pending issues in the code, eg #844910: Change behavior of add_orderby, and a few TODOs marked where it's working but it could do with a read from someone who knows the deeper parts of Views code :)

joachim’s picture

Node --> comment is not working because there is no longer a direct argument on comments for node nid -- it has to be done via a relationship.

At this stage, I'm thinking we should mothball the node -> comment relationship for now and just try to get the basic functionality into the 3.0 release.

donquixote’s picture

there is no longer a direct argument on comments for node nid

Why is this? Yet another JOIN for no reason? :(
See also
#996922: Make comment uid,nid available without a relationship

@merlin, i always caps my sql, no shouting intended :)

joachim’s picture

I've filed #1176260: Make comment nid available without a relationship, but I really don't want that to hold us up here. This issue is now 2 years old, I'd like to see it laid to rest :)

merlinofchaos’s picture

donquixote: Please reduce the attitude.

joachim’s picture

Here's a clean-up patch, with some of the more complex and not-quite-working-yet stuff taken out for now:

- comment support
- using an existing view to generate the subquery rather than just picking a sort order.

These are both pretty hairy and IMO in the 'nice to have' rather than essential camp.

I'm also attaching an exported view of all users, showing the most recent node by each one, which makes a nice demonstration of this patch :)

a.ross’s picture

Hi, I've tested your patch and your example seems to work nicely. I also found the Taxonomy representative node. Just have a small remark: the description text on the User: Reference node contains a typo.

joachim’s picture

Thanks for testing! And well spotted :)

Here is another patch with just that typo changed -- it's actually in both taxonomy and user.

Also, I realize I probably have done my usual trick of skimming over the explanations. What I mean is that this patch is complete. When I say I've taken out some of the more hairy features, my intention is that these should be added in a subsequent issue. This feature has been in the pipeline for so long, I'd really like to get something in that works, and look at further functionality after that.

merlinofchaos’s picture

Status: Needs review » Fixed

Committed. It's like the end of a journey but it's probably just the halfway point. :)

joachim’s picture

For the release notes:

* #470258: Added representative relationships. These allow relationships that ordinarily would result in multiple rows because they are one-to-many to pick just one item from the relationship (hence 'representative'). Example: a view of users with a representative relationship to nodes can show the most recently posted node by each user.

donquixote’s picture

I had this in my issue queue for such a long time (and made some noise), and only now I realize how cool and desirable this feature really is.

One question,
(useful to put in release notes or docs)
If I understand #109 correctly, the sort order is defined by another view?

And another question,
any chance this can go into D6?

EDIT:
I just noticed I did not even try 6.x-3.x yet...

joachim’s picture

> If I understand #109 correctly, the sort order is defined by another view?

You pick a sort order from those that apply to the base table you are joining to.

Eg, relationship from taxonomy -> representative node, you get to pick a sort order on nodes.

There *was* a feature where the entire subquery was taken from another view. This was getting really complicated, as the subquery SQL needs to be ENTIRELY namespaced. So I took it out in the interest of getting *something* in for the release. Now that's in, I can open up a new issue for it and post the code I had that was half-working.

> any chance this can go into D6?

The patch for 6.x-2.x worked at some point...
For 6.x-3.x, you would need a mishmash of that (for the D6 database API) and the patch that went in (for the Views 3 stuff). Not something I have time to work on right now and you should really ask merlinofchaos first whether he'd consider it.

drew reece’s picture

What versions should be used to get this to work?

I have installed…
Drupal 7.2
views 7.x-3.x-dev
libraries 7.x-2.x-dev
ctools 7.x-1.x-dev

I have also tried the current state of the views-7.x-3.x branch from git.

I add a taxonomy page view to that I add a relationship for Taxonomy term: Representative node with sort by Content: updated date.

Attempting to saving the 'Representative node' form produces Apache error log entries like…
PHP Fatal error: Call to undefined method views_handler_relationship_groupwise_max::view_aliased() in (site)/sites/all/modules/handlers/views_handler_relationship_groupwise_max.inc on line 200, referer: http://test/admin/structure/views/view/test4

And clearing the site cache causes…
Strict warning: Declaration of views_handler_relationship_groupwise_max::options_submit() should be compatible with that of views_handler::options_submit() in require_once() (line 54 of (site)/sites/all/modules/handlers/views_handler_relationship_groupwise_max.inc).
In drush's output.

The same happens with a 'user view'.

joachim’s picture

Clear your Views cache?

drew reece’s picture

Thanks Joachim,
I still have the issue after clearing the views cache, I disabled the views cache too & it still shows error dialog when applying the 'Configure Relationship: Taxonomy term: Representative node' settings.

The dialog is…
An AJAX HTTP error occurred.
HTTP Result Code: 500
Debugging information follows.
Path: /admin/structure/views/ajax/config-item/test4/page/relationship/tid_representative
StatusText: Internal Server Error
ResponseText:

I also get this in the Apache error log.
PHP Fatal error: Call to undefined method stdClass::add_item() in (site)/sites/all/modules/handlers/views_handler_relationship_groupwise_max.inc on line 223, referer: http://test/admin/structure/views/view/test4

I'll try a fresh install & see what happens, are there any specific versions that are required other than the views 7.x-3.0-dev?

Does anyone have a views export with a 'representative type' I could try to import? The #109 view 'white screens' for me.

joachim’s picture

Could you file a new bug for this please, since the code is in?

I'll file a patch for the subview thing while I'm at it...

joachim’s picture

joachim’s picture

drew reece’s picture

Sorry for the thread hijack,
See http://drupal.org/node/1198200

I'll try the patches.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

knalstaaf’s picture