I need for my query (An explanation why follows) to join with the primary node itself, this is prevented, for some reason.
so i made/propose a small code change.
in the beginning of the function add_table
i remove the block:

    if ($table == $this->primary_table) {
	    return;
    }

and second in the function get_table_name
i add the check and incrementation:

    if ($table == $this->primary_table) {
    	$table_num++;
    }

This prevents name collision.

As to why i need this query?
i need this query to implement views arg on something like backlink.
i need to get all the nodes referenced by a certain field in a different node.
so this is actually the query i got to:
(notice that i run here into another limitation of the view query which is only one condition for the join - so i move the vid comparison to the where)

SELECT *
	FROM node node
	inner join content_field_style_instructions
	on node.nid = field_style_instructions_nid
	inner join node node1
	on node1.nid = content_field_style_instructions.nid
where 
	content_field_style_instructions.nid = 68
	and node1.vid = content_field_style_instructions.vid
CommentFileSizeAuthor
#4 ReferencedByFilter.zip_.txt1.63 KBamitaibu
#1 views_query_new.patch736 bytesamitaibu

Comments

amitaibu’s picture

amitaibu
Primary language Hebrew
Location Israel
commented
Status: Active » Needs review
StatusFileSize
new views_query_new.patch736 bytes

Hi,
Not sure If I've created the patch correctly - first time...

Amitai

amitaibu’s picture

amitaibu
Primary language Hebrew
Location Israel
commented
Version: 6.x-2.x-dev » 5.x-1.6-beta5
merlinofchaos’s picture

merlinofchaos commented
Status: Needs review » Closed (won't fix)

I'm really afraid of the ripple effect that this patch will have. Taking out this protection will almost certainly cause a slow trickle of malformed queries because the system has always expected that trying to add 'node' itself would be banned.

The workaround you're going to have to do is to create an new table and make it 'node' and alias it. That's what most of the systems that need to join the node table back in do.

amitaibu’s picture

amitaibu
Primary language Hebrew
Location Israel
commented
StatusFileSize
new ReferencedByFilter.zip_.txt1.63 KB

Hi,
Just as an FYI, with the attached module (zip format) we get backlink for any node reference field.

1. Enable module
2. Create a node reference CCK field, Autocomplete Text, Multiple values.
3. Enter some node references in the field - note down the order you've put them.
3. Create a view. A new argument is available "Reference by any field"
4. In the Option select the node reference field.
5. Invoke you view - the nodes appear ordered.

By idea, not necessarily by the way it will be implemented views 2.0, we did the

'node' not the only base table

the greenman’s picture

the greenman commented

I thought I would add a little extra info here, cause merlin's answer on how to achieve this was a little terse and it took me a little while to work out.

To join back to node, you need to add a table via hook_views_tables. In the table definitions, there is no limit on the primary table joins.
Once you have the alias added that way, you can ensure_table($alias), and then write your join using the alias.

here is a quick example, it assumes that you have node profiles of type "profile", and allows queries to be based on the profile of the author, rather than the node itself: 

function myprofiles_views_tables(){  
  $tables['profilenode']=array(
    'name' => 'node',
    'join' => array(
      'left' => array( 'table' => 'node', 'field' => "uid"),
      'right' => array( 'field' => "uid  and profilenode.type = 'profile'" ), // link to user's node profile
    ),
    'fields' => array(
      'profile_nid' => array(
        'name' => 'profile nid',
        'field' => 'nid',
        'help' =>'',
      ),
    ), 
  );
  return $tables;
}


///and then within the argument handler.....
....
   $joininfo = array( 'right' => array( 'table' => $table, 'field' => 'nid' ),
                           'left' => array( 'table' => 'profilenode','field' => 'nid') 
                         );                  
   $query->ensure_table('profilenode');
   $query->add_table($table, false, 1, $joininfo );
....