'Not unique table/alias' warning
folkertdv - June 5, 2008 - 12:19
| Project: | Views Fusion |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Description
When I was using Node Relativity relations for Views Fusion, I stumbled upon an error message saying the table names in the generated query aren't unique.
I was fusing several views using the Relativity Parent-Child relation (thus using the same join table), but the query was missing aliases for the tables.
After some code-digging, I have isolated the problem to a missing line in the code, and I've attached a patch that will fix this.
#1
Somehow the attachment didn't get sent along.
#2
The problem seems to extend a bit further than what I anticipated.
When more than two instances of the same join are used, there were still unique table errors.
I came to the conclusion that array_merge did not merge the query arrays the way they should be merged.
For instance, the query->joins array:
query1 => Array (...,
[joins] => Array (
[relativity_parent] => Array (
[2] => Array (
[left] => Array (
[table] => node
[field] => nid
[alias] => v61
)
[right] => Array (
[field] => parent_nid
)
[type] => inner
)
)
[node] => ...
array_merge'd with (notice the different index of the relativity_parent join -- 1 vs 2):
query2 => Array (...,
[joins] => Array (
[relativity_parent] => Array (
[1] => Array (
[left] => Array (
[table] => node
[field] => nid
[alias] => v62
)
[right] => Array (
[field] => parent_nid
)
[type] => inner
)
)
[node] => ...
would result in:
query1 => Array (...,
[joins] => Array (
[relativity_parent] => Array (
[1] => Array (
[left] => Array (
[table] => node
[field] => nid
[alias] => v62
)
[right] => Array (
[field] => parent_nid
)
[type] => inner
)
)
[node] => ...
While this should have been:
query1 => Array (...,
[joins] => Array (
[relativity_parent] => Array (
[2] => Array (
[left] => Array (
[table] => node
[field] => nid
[alias] => v61
)
[right] => Array (
[field] => parent_nid
)
[type] => inner
),
[1] => Array (
[left] => Array (
[table] => node
[field] => nid
[alias] => v62
)
[right] => Array (
[field] => parent_nid
)
[type] => inner
)
)
[node] => ...
Regarding the query2->tables array:
[tables] => Array(
[relativity_parent] => 2
[v62node] => 1
)
merged with query1->tables using array_merge:
[tables] => Array(
[relativity_parent] => 1
[v61node] => 1
)
results in:
[tables] => Array(
[v62node] => 1
[relativity_parent] => 1
[v61node] => 1
)
while it should result in:
[tables] => Array
(
[relativity_parent] => 2
[v62node] => 1
[v61node] => 1
)
Since the different arrays in the query object are built up in different ways, using simple array_merge will mess things up quite a bunch.
The code I'v used to fix all my problems (this is not really patch-worthy in its current form) is as follows:
<?php
function views_fusion_query_merge(&$query1, &$query2) {
if ($query2->groupby && !$query1->groupby) {
$query1->groupby = $query2->groupby;
}
else {
//add an alias to the nid field
$query2->fields[0] .= ' AS '. $query2->use_alias_prefix . 'node_nid';
}
//if query2 is set to be distinct, set query1 to distinct
if ($query2->distinct) {
$query1->set_distinct();
}
$query1->tablequeue = array_merge($query1->tablequeue, $query2->tablequeue);
// Joins
foreach ($query2->joins as $table => $joins) {
foreach ($joins as $num => $join) {
$query1->joins[$table][$num] = $join;
}
}
$query1->fields = array_merge($query1->fields, $query2->fields);
$query1->where = array_merge($query1->where, $query2->where);
$query1->where_args = array_merge($query1->where_args, $query2->where_args);
$query1->orderby = array_merge($query1->orderby, $query2->orderby);
// Tables
foreach ($query2->tables as $table => $num) {
$query1->tables[$table] = (isset($query1->tables[$table]) ? max($query1->tables[$table], $num) : $num);
}
}
?>
Would like to hear your thoughts on this.
#3
Also, in views_fusion_views_merge there's a missing line:
<?php$view1->filter = array_merge($view1->filter, $view2->filter);
?>
which caused my exposed filters to assume values of other (non-exposed) filters. Simply because the filter array was missing a bunch of values.
#4
Good work Folkert,
That solved the "not unique" error
I still had problems with "unknown column" SQL error message when I had both parent-child and child-parent fusions which I tracked down to incorrect table and column names.
I have fixed this in the following patch. The patch also includes Folkarts code.
i must confess the code of this module is a bit complex for me to understand fully so I may be missing something simple but my code fixes the problem.
Regards
Geoff