I have made my own 'views_plugin_display', it's actually based on one from the CCK Node Reference project. This display is used by hundreds of users of my module which integrates Views, and I've been getting a few bug reports about this - but nobody really understands Views well enough to figure out a solution.

I am using Views to allow people to choose a field (in a config form that displays options from views_fetch_fields()), and then I am adding conditions on the field to restrict the query results, and I am doing this by calling the add_where() method on the query.

Here is a snippet of my code (that composes the conditions into an array, ready to be added to the query) that I believe is incorrect:

(FYI, the table_name.field_name value chosen in the config form I mentioned is split by the dot (.) into $field['table'] and $field['field'] for use in the following.)

<?php
               $alias = NULL;
               $alias = $this->view->query->ensure_table($field['table']); 
               if (!$alias) {
                 // This is a hack to get fields present via a relationship to work.
                 // Doesn't work if you haven't added the field to the view via UI though.
                 $field_info = $this->view->field[$field['field']];
                 $table = $field_info->definition['table'];
                 $alias = $field_info->table_alias;
                 // Alternate idea that might work without adding the field to the view:
                 // $table_data = views_fetch_data($field['table']);
               }
               else {
                 $join_data = $this->view->query->get_join_data($alias, $options['base_table']);
                 $table = $join_data->table ? $join_data->table : $field['table'];
               }
?>

Specifically, the setting of the $alias variable is sometimes incorrect. The correct value for $table is also required to check the drupal table schema and determine the column type (string/numeric/etc..) so I know how to form the conditions.

Link to full source code of that file in case it doesn't make sense out of context.

Now in like 95% of cases that code does seem to work, just from the return value of ensure_table(). But clearly I am taking a guess at how to fetch the alias of the table, and sometimes it remains NULL/blank resulting in SQL errors.

I've noticed the problem mostly in cases where 'relationships' are used by the underlying view. Even with the additional 'hack' I put together there, I am having problems.

Can you please point me in the right direction on how to identify these tables and ultimately the fields correctly, as according to the intended design of Views?

Comments

esmerel’s picture

esmerel commented
Status: Active » Postponed (maintainer needs more info)

Did you figure this out? It's been inactive a while, so if it's been finished, I want to clear out the issue.

asiby’s picture

asiby commented

Good luck man. The view module is so poorly documented for developers. My only hope is that someone who knows it well enough will find time to document it. I am willing to raise money to pay them for that if need be.

When it comes to table aliases, it was no longer economically viable for me to find the answer. What I did was to implement hook_views_pre_execute(&$view) and to grab $query = &$view->build_info['query']. This is the rendered query. Then I have used to preg_match and preg_replace to deal with changing table aliases.

The other thing that I do not understand is that after adding different filters using the same table, then resulting query had an additional JOIN to the SAME TABLE for each filter. Very unusual. A manually written query would use much less resource.

Good luck.

danielb’s picture

danielb commented

Did you figure this out? It's been inactive a while, so if it's been finished, I want to clear out the issue.

No, I am still awaiting a response.

danielb’s picture

danielb commented
Status: Postponed (maintainer needs more info) » Active
savedario’s picture

savedario commented

subscribe

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Postponed (maintainer needs more info)

Good luck man. The view module is so poorly documented for developers. My only hope is that someone who knows it well enough will find time to document it. I am willing to raise money to pay them for that if need be.

There's an old saying involving flies, honey and vinegar.

               $alias = $this->view->query->ensure_table($field['table']);

You've got a problem if the above code fails. If that code fails, it was unable to actually add the table, because there was no path from the current base table (using relationship) to the table you're trying to add. Since you're not actually preserving $this->relationship in this call, your usage of this function is incorrect.

Everything after that is wrong, so your actual question is the wrong one. The question should have been "Why does ensure_table() fail." And the answer to that is probably about paths and relationships to data, and I can't answer that one further without understanding your data.

danielb’s picture

danielb commented

Thanks for your response, I'll try to explain what is happening, I'm a little lost, so this post may not be on the ball. You've given me some direction with your comments, but I'm still stuck.

Firstly, in this scenario the user creates a User view and adds a relationship "Node: Content Profile" which is supplied by the module Content profile. The content type used by Content profile has some CCK field configured, e.g. "field_fullname".

Now my module gives users a list of fields that can potentially be used by their nominated view. The data for this list is obtained via views_fetch_fields(array_keys($base_tables), 'field') and indeed the "field_fullname" data is there under the key:
"node_data_field_fullname.field_fullname_value".

My module assumes that is in the format table_name.field_name, as it is for other fields, and will use these values (table_name and field_name) when it later calls $view->add_item(). I think this is where it's going wrong. But I can't figure out the correct way to assert that the field has a path through a relationship, and how to add it in correctly.

I do this through a custom display handler plugin, which also adds a GROUP BY on the field. (It does more than that, but I've made the use-case simple here)

Upon execution I get this error:

user warning: Unknown column 'field_fullname_value' in 'field list' query: SELECT users.uid AS uid, field_fullname_value FROM users users LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'profile' WHERE users.status <> 0 GROUP BY field_fullname_value, uid in /sites/all/modules/views/includes/view.inc on line 775.

So the link between the node and the CCK field is missing.

If you want any more info, var_dumps, etc... please let me know, this has been a long-standing bug in my module :(
Cheers.

esmerel’s picture

esmerel commented
Status: Postponed (maintainer needs more info) » Active
scott_earnest’s picture

scott_earnest commented

Not sure if this will help or not. I am creating a CSV from a view and ended up coding something similar to this:


$view = views_get_view('MY_VIEW_NAME'); 
$display_id = 'default';
$view->set_display($display_id); 
$view->pre_execute();
$view->execute();

$aliasAr = array();
$fieldNames = $view->display[$display_id]->display_options['fields'];
foreach($fieldNames AS $thisField) {
	$aliasAr[] = $view->display[$display_id]->handler->handlers['field'][$thisField['field']]->field_alias;
}
var_dump($aliasAr);
iamjon’s picture

iamjon commented
Status: Active » Closed (cannot reproduce)

Closing from a lack of activity. Please feel free to reopen.

danielb’s picture

danielb commented
Status: Closed (cannot reproduce) » Closed (fixed)

I think I've figured it out now guys, merlin gave the biggest clue by mentioning incorrect use of ensure_table() and preserving the relationship.

First thing to realise is that even though a relationship object may be attached to a view, nothing will automatically happen because of this. You need to pass a $relationship parameter to $view->query->add_table() or $view->query->ensure_table() (see these functions in views/includes/query.inc). The value of $relationship is the 'alias' property in the relationship object, which you will find in $view->relationships[$relationship_key]->alias.

So how do you know whether you need this $relationship param when you are adding the table for a particular field? Well it appears you can't determine this automatically either. You need the admin/user to tell you that this field uses this relationship, such as in a configuration form. At the point of creating the configuration form you can get a list of available relationships from $view->display[$display]->display_options['relationships'] and let the admin/user pick one of these for each field.

If the admin/user has added the field in question as a field item to their view and configured it to use the relationship, you may be able to steal this information instead - or perhaps it will just work, but I haven't looked into doing this as my use-case doesn't rely on the admin/user having picked the right fields when configuring the view.

Just a little disclaimer that at the time of writing this, I only just figured this stuff out, so some of it may be misleading or inaccurate. But at least now I'm getting results and seeing some progress, so this information may help somebody out there.