Jump to:
| Project: | Table Wizard |
| Version: | 6.x-1.x-dev |
| Component: | Documentation |
| Category: | task |
| Priority: | normal |
| Assigned: | mikeryan |
| Status: | active |
Issue Summary
Hello,
I am trying to create a node view that accesses data in an external table. I've added both node and my external table to table wizard and created a relationship from node.nid to an nid column in my external table, setting relationship to manual.
I've then created a node view, and added the relationship node: array. Running the Views query then indicates that it is indeed forming a left join with my external table.
Having established this relationship, I would then expect to be able to add any fields from my external table into the view within the "fields" section of the Views UI. However, none of the external table fields appear as options in the Views UI.
As a very similar issue is marked as recently fixed (http://drupal.org/node/645944), and I am using the latest TW dev release (downloaded today, 29 Jan), as well as latest official releases of Views and Schema, I am figuring that I must be doing something wrong, and marking this for now as a support request.
However, as I'm finding it hard to work out what I may be doing wrong, it's also very possible this is a bug.
Either way, very keen to solve this. Thanks for this great module & really appreciate your help.
Comments
#1
I've experimented a bit more and now feel this is likely to be a bug:
If an external table is linked to a node view through a relationship, Views shows that a join has been made with the external table. However, the fields of that external table cannot be added to the view through Views UI. Please let me know if you need any more info.
This issue arose with dev release downloaded 29th Jan. Can anyone confirm whether this issue also occurs in the latest official release?
Thanks
#2
Well, something's a little funky here, but I'm not seeing quite what you are... Using an automatic relationship works fine for me, with a manual relationship I'm seeing "Array" in certain places (obviously wrong) - but I am able to add the relationship, then add the fields from the related table.
What version of Views are you using? I'm at the latest CVS of V2...
Thanks.
#3
I've committed a fix to the aspect I was seeing ("Array") and it appears to work fine for me - can you fetch the latest CVS (or latest dev release from the project page once it gets rebuilt) and see if it helps with what you're seeing?
Thanks.
#4
Mike, thanks very much for looking into this. I've uploaded the latest dev release.
As before, I looked to add a TW relationship to a node view. The relationship was now labelled "Node: Nid" rather than Node: Array.
As before, once the relationship is added the SQL displayed in views indicates that a join has been formed with the external table, and I see duplicate rows reflecting the fact that the external table has many records for each nid.
However, I still don't seem able to add fields from that external table through the "fields" section within Views. The only groups available from which to add fields are Content, Comment, Global, Node, Node Revision, Taxonomy, User, Customfield (a Views contributed module). The TW fields do not appear in any of these groups.
To give a bit more background, and to check I am not configuring anything incorrectly:
I have added one external table into table wizard, plus the node table and a content_type CCK table.
I have set up a single, manual relationship from node.nid to myexternaltable.nid. Note that nid is not, by itself, a primary key on my external table. It combines with another field to create the primary key. I have not set up any relationships with the content_type table as yet (NB - ideally, if poss, I would like to be able to link one of the CCK fields to the external table as well).
I have not enabled default views for any of the tables added to table wizard. "Foreign Key" is checked on all the fields that I want to add to relationships. I have not checked "ignore" on any of the fields in the external or drupal tables added to TW.
Hope this gives you enough detail to reproduce/understand the issue. Thanks!
#5
What version of Views are you using?
#6
I'm using 6.x-2.8 - the latest official release.
I'm using version 6.x-1.6 of Schema.
Drupal 6.15
Thanks
#7
Sorry, I've tried playing with it some more and cannot reproduce your problem. I can't think of a scenario where the join is successful (represented by the multiple entries you get per nid) but the fields aren't available.
#8
Mike, Thanks for looking into this and sorry for my delayed reply. I've continued to check this, but the problem persists. In the TW settings I am able to add the external table, TW shows all the fields of that table under content/tw/analyze/1, and I am able to add the relationship with my node table. Views shows that an SQL join has been made, and there are multiple node records to indicate the join with the external table, yet I cannot add fields from the external table in Views.
Just so I am clear, is the expected behaviour then that on the Views UI, if I click "+" under "fields", all of the external table fields (except those set to "ignore") should be visible to add? Should they appear under "content", "node" or some other category? (Either way, they are not there in my case).
I have a standard installation, with very recent versions of the modules, as mentioned above. The only unusual things in my settings I can think of that may be causing problems are:
- I have not included a single field primary key for my external table (I have a 2 field primary key because of the nature of the data).
- I did not click the checkbox to "Provide default view" for my external table for the above reason.
If these are not the cause of the problem and you don't have any other ideas, then I guess my next step would be to uninstall/ reinstall the modules, possibly trying the last official release of TW instead of the dev version, and see if the problem remains.
As my site has since evolved to include data I don't want to damage, I just wanted to double check that uninstalling TW would not change or delete any of the external (or drupal) tables referenced in TW, and also that clicking "remove selected tables" or "delete selected relationships" in the TW user interface would not change/ delete any of the underlying tables?
Hope you can clarify and thanks again for your help.
#9
I've come back to this problem, and it seems I've identified the issue.
As mentioned above i was not using a single column primary key in my external table.
After (i) adding a single column (auto increment) primary key AND (ii) creating a default view of the external table, the fields were then completely accessible in views and, so far, it seems to be working great. Assuming this is intended, I would recommend making it explicit at admin/content/tw that these steps MUST be taken for the module to work (or, at least, this is neccessary for tables to be accessible to Views). Without taking these steps, as mentioned above, a join could still be formed with the external table in Views-but the fields were not accessible.
Also, in the process of experimenting with the module I "removed selected tables" and "Deleted selected relationships" multiple times through the UI. In case any other users are wondering, changing these settings does not in any way affect the content of the underlying tables, just the information TW retains about them (or so it seems). It may also be worth also making that explicit as the terminology was (to me at least) a bit unclear. (NB I am assuming, as well, that uninstalling the module does not affect the data of any tables added to table wizard?)
Now I've got this up and running, really looking forward to seeing what I can do with it. Thanks for what seems an extremely useful module!