I can't figure this one out. Any help would be *greatly* appreciated on the following matter:

1) I created an Event CCK that has events fields (start and end times via event module) enabled on it + a EVENT_NOTES field
2) I created a Venue CCK that has location fields (state, city, street address, zip via location module) enabled on it + a VENUE_NAME field + a _SITE_URL field
3) the Event CCK has a node reference field set to the Venue CCK VENUE_NAME field

No less than 4 content tables are related via a single nid and vid to represent a single Event CCK.

Now i create some content:

I create/add my VENUE CCKs separately (type in the venue name, website, location (city, state, address) information)
Then I create/add EVENT CCKs. This includes adding event start/end times, event notes and selecting the VENUE_NAME (which is a node reference field to the VENUE CCK) from a select menu.

In admin> views, i create a page view called 'event' that filters on my Event CCK Type and adds fields for display from all the 4 tables described above.

When i load the view into my browser, ONLY those fields that ARE DIRECLTY selected or filled in on the Add Event CCK form show up in view. All fields that are created on the Add Venue CCK (city, state, website url) DO NOT show in the view, even though i've selected city, state, website url from the views 'fields' section. And eventhough, the Venue CCK is related to the Event CCK via field of type "node reference".

The only thing i can think of is that the view isn't performing a join on the Venue Table and it's Associated location table.

It would seem like relations between CCK nodes and other nodes like events and locations would be pretty common to build robust drupal applications, so i know there has to be a workaround for this. i'm either using views wrong, there's a bug, or this is a task that needs to be handled manually with custom code.

Qs:

1) can anyone clarify for me if views can handle queries on nodes with relations across multiple tables like my example shows?
2) or if it needs to be handled manually (with custom code), any snippets and where to put them?
3) THIS IS A BIGGIE FOR ME.. is there any way to trace the view, it's vars, db calls, db queries, etc WHILE i load the view in the browser so i can debug what it's doing when it's loaded?

I'm kind of stuck in the water until i can get this one working.. Any thoughts are greatly appreciated...
thanks in advance

Comments

merlinofchaos’s picture

I don't think what you are talking about is possible the way you are thinking it is. Having a nodereference doesn't really make it part of the node. I don't have any good suggestions for making what you want to do work.

-- Merlin

[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]

-- Merlin

[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]

eaton’s picture

node reference allows views to provide a link to the 'referenced' node as if it were another field or column. It *doesn't* allow two nodes to be treated as one. It looks, though, like someone is working on something that provides those capabilities. The 'Node family' and 'Views fusion' modules sound like they attempt that, though I haven't used them. Perhaps you can check them out?

http://drupal.org/project/nodefamily
http://drupal.org/project/views_fusion

--
Lullabot! | Eaton's blog | VotingAPI discussion

newdru’s picture

a pretty standard request for a CMS:

You want a venue table with associated location, url, description fields and then attach that to (many to one relationships) an event table via a foreign key.

What about the thousand other related combinations: artist - album | author - book | .... the list goes on...?

Just so i fully understand all of this can someone confirm the following for me:

1) I thought CCK node reference fields (note they are primarily CCK constructs that are made available to views not a native view construct) were about tying fields from different tables together much as foreign keys. If i look at all the tables involved in my "complex" type described above there is a chain that is created via a series of node ids between them all that creates one master relationship. That said, you are saying that views doesn't join the tables on these node reference fields in any way - right?

2) If #1 is yes, then aside from your suggestion to use nodefusion & views_fusion, how does one create relationships like artist-album, venue-event? Many cms drupal apps are going to need a way to create those relationships between content types. I can't believe there is no way to do this. Are you sure about this or does it mean that most complex apps have to create a module to somehow enforce the relationships between related content types?

3) Could i somehow inject some php code in the view to do the join on the 4 tables myself so i wouldn't have to write an entire module to do this kind of thing?

btw thanks for the input.

merlinofchaos’s picture

Well here's the real problem.

Creating those relationships can be done in Views, using the API.

But you're kind of expecting a nodereference to give Views a lot more relationship information than it currently does.

Let me try to explain a little bit. Views wants to relate all of its data back to the node table. This looks something like this (naively):

[node] <-- [cck_data_field]

However, the relationshpi you're trying to draw looks like this.

[node] <-- [node_reference] <-- [related_node] <-- [cck_data_field]

When you add your event field, it wants to do the simpler relationship noted above. However, you need the relationship noted below. What this means is that all of your Event fields have to be entered *twice*. But they are not. Node reference isn't smart enough to do that, and Views relationship data is currently fairly static.

Views does a lot of good stuff, but it is not advanced enough to do the complex automatic relationships you want.

That said, yes, if you have a good understanding of the underlying CCK tables and Views API model, you *can* add that relationship information through the Views API. I'll warn you now: this is not an easy task. The CCK Views integration is difficult because it is very, very abstract.

-- Merlin

[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]

-- Merlin

[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]

newdru’s picture

I walked through the relations myself and came to a similar conclusion you layout above, which i sum up as "any field that is NOT DIRECTLY "attached" to a CCK will NOT appear in the VIEW result set because there is no "join abstraction layer" to to support node_reference mechanism".

That said:

1) When you export a view, $view->requires contains what i think are the db tables needed for the view. i think these table names are supplied by each module that provides a field to VIEWS in one of it's hooks. is that correct?. Then views simply does a SEPEARATE SELECT on each table for the given fields (specified in the field arrray) with no joins. Is that correct?

2) what's the best way to 'trace" a given view while it's executed in the browser? i tried turning on devel but it doesn't do anything. i'd like to be able to see what views is doing and a listing of var values (preferably on the same browser page as the view versus a log file). possibly i could put a print_r on $view in the view footer but there's other stuff going on behind the scenes as well and maybe other vars i don't know about. any thoughts on this would be greatly appreciated (as it can be applied to other aspects of drupal as well).

3) Could i not just put a custom SELECT join query on whatever tables i want and then format/wrap with hmtl tags in a PHP snippet in either a page or even in the header or footer of a view (and leave the other fields blank(like the fields fields)) to do this? this would prevent me from having to
write a module. if view->requires (as stated in #1) has access to the tables in my example, then i should be able to manually access and manipulate those tables as i see fit.

thanks for the input and for your work on views... btw angrydonuts is a great name!

NOTE:

I looked at the nodefamily and views_fusion modules today. I haven't installed them yet but after reading the README and walking through the code it looks like it may be the solution i'm looking for. from what i can tell, it allows for teh creation of lookup tables between two CCK types and enforces those relationships when you create views on those content types (via views_fusion module). If it works as planned, this would seem essential for creating complex related CCK types fairly easily and maintaining those relationships in views without having to create entirely new modules. A very handy thing for app developers! thanks to the poster that suggested these to me!

However, I have not seen a lot of post activity on those modules and the few that exist seem to highlight bugfixes. So i'm not sure how well it really works in practice. Mabye it just hasn't recieved enough exposure yet. I posted to the author to ask if it will do what i propose here but have not heard back yet.

I will explore this more over the next few days and post my results here.

merlinofchaos’s picture

1) When you export a view, $view->requires contains what i think are the db tables needed for the view. i think these table names are supplied by each module that provides a field to VIEWS in one of it's hooks. is that correct?. Then views simply does a SEPEARATE SELECT on each table for the given fields (specified in the field arrray) with no joins. Is that correct?

Not quite. What you're getting is this:

[node] <-- cck_table_for_type_a
^---- cck_table_for_type_b
^---- users [let's say you put 'Author: Name' as a field]
^---- node_comment_statisticis [Let's say you put Comment: Count as a field]

There's plenty of joins going on, they just all join directly to the node table.

2) what's the best way to 'trace" a given view while it's executed in the browser? i tried turning on devel but it doesn't do anything. i'd like to be able to see what views is doing and a listing of var values (preferably on the same browser page as the view versus a log file). possibly i could put a print_r on $view in the view footer but there's other stuff going on behind the scenes as well and maybe other vars i don't know about. any thoughts on this would be greatly appreciated (as it can be applied to other aspects of drupal as well).

I don't have a good answer for this. I use devel.module and turn on query logging -- that lets me figure out the actual query that gets generated. The function 'views_build_view()' is where you want to look to see a view get built, but since you're dealing with abstract data it isn't as completely straightforward as you might like. It's not too bad though; I consider it a medium-low level programming exercise to follow. But I think understanding that everything is going to attach directly to the actual node that's being pulled up, not some *other* node being pulled up.

3) Could i not just put a custom SELECT join query on whatever tables i want and then format/wrap with hmtl tags in a PHP snippet in either a page or even in the header or footer of a view (and leave the other fields blank(like the fields fields)) to do this? this would prevent me from having to
write a module. if view->requires (as stated in #1) has access to the tables in my example, then i should be able to manually access and manipulate those tables as i see fit.

Well yes but then you wouldn't be using Views. And, er, would basically be writing a module. If you are saying you want to write the select and have Views handle the output, I don't think there's any way to do that.

However, I have not seen a lot of post activity on those modules and the few that exist seem to highlight bugfixes. So i'm not sure how well it really works in practice. Mabye it just hasn't recieved enough exposure yet. I posted to the author to ask if it will do what i propose here but have not heard back yet.

The author is still active and working on it. Much of the speed of activity there is my fault; getting views_fusion working required a fairly large patch to Views so that he could get his data. I have not yet actually committed that patch, though fago did just reroll that patch so it will work against Views 1.1 and from what I can tell there are a few people using it. From the sound of your posting I think you probably have the ability to puzzle through it and figure out what's going on, but you are right...it's not-mature bleeding-edge software in Drupal-land. And fago would appreciate intelligent users providing feedback, I think.

-- Merlin

[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]

-- Merlin

[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]

newdru’s picture

1) Since you are familiar with views_fusion, would you be able to confirm that nodefamily with views_fusion will solve the problem defined in this thread? and or whether or not writing a custom module is better than using that approach. i am willing to dig in there at some point but i was hoping it wouldn't have to be so soon.

2) not sure if you can answer this one but i'll throw it out there. the event.module start/end date fields that are presented in views do not allow/enable the ability to enter a custom date string like you can do on normal node date fields. actually, they allow you to select the 'custom date' format in the drop down but no option box is available to apply a custom format to it. How can i access that date in the view (or otherwise) so that i can reformat it the way i want?

thanks as usual

fago’s picture

yes, nodefamily and views_fusion are built for solving exactly your problem.
However nodefamily does currently build its relations only based on node-author basis automatically. If this doesn't suit you can use its API to manually set node relations
or you can build an views_fusion support for your own node relations module.

I'm planning to extend nodefamily anyway when I've got the time for that: more

Unfortunately there aren't a lot of views_fusion docs and it's not the easiest thing to use it. Feel free to create an issue if you have any problem and I'll try to help you.

newdru’s picture

sorry for getting back on this.. i'll post to issues as they come up..

until then.. any thoughts on what might be a good module to base a "node_relations" module off of?

what i mean is that writing a basic module seems like it's not that difficult in the sense that there is an example.module and enough documents lying around to wade through which make it fairly straightforward (i think ?). but making what i want to do work as a module with respect to honoring the drupal api/s and making sure i'm doing the things i'm supposed seems a little more arduous without anything a little meatier to go on (doco or a more complex example).

So if you have an thoughts on what might be a good "model" module or modules to dive in and look at the code and base what i want to do off of would be a good step.. something that is tying two content types together as one functional unit.

Any other links, etc are more than welcome..

thanks much

fago’s picture

Which interface have you in mind? How should the system determine which nodes belong together?

newdru’s picture

from the problem i've described in this thread, i should be able to achieve what i want to do successfully without having to write a module by:

==============
PROBLEM AT HAND
===============

1) First i install the latest 1.2 views (i move the original older 'views' folder out of the modules directory. after extracting views 1.2 download, the new views ilives in a folder called 'views_1", so i rename it to 'views' and copy the folder into the modules directory. i make sure i go to admin/modules and uncheck views and check it again and then resubmit the the modules at the bottom of the page just to make sure it's picking up my 1.2 views - i hope/think???)

2) I create 2 CCK types (MyEvent & Venues)

3) Then relate them manually with nodefamily by admin/settings/nodefamily (i think that's what this is there for right? i don't have to use the API, this will probably create a lookup table on the backend - yes - unless i don't understand?)

4a) I go into admin/views and create 1 View for MyEvent called 'myevents". I enable a number of fields on it (some native to MyEvent CCK) and one is a nodereference field to my CCK Venue

4b) I go into admin/views and create 1 View for Venues called 'myvenues". I enable a number of fields on it native to the Venue CCK.

4c) I test 'myvenues' view. It works fine. I do the same for 'myevents'. It works fine but of course it doe NOT RELATE THE VENUES CORRECTLY BECAUSE THE 2 VIEWS HAVEN'T BEEN FUSED YET.

5) I go into admin/views/fusion. I make the 'myevents' parent and 'myvenues' child "Using --> 'node family relation'". I presume this is the nodereference field in MyEvent CCK linking to Venues CCK

6) I go back to admin/views and test 'myevents' view. It still works the old way. It does NOT pickup any of the Venues fields.

7) I've tried recreating the view and the fusion and the nodefamily a number of times to no avail.

Can you confirm i'm doing this right?

If not, let's fix it..

OK.

======================
COMPLEX MODULE OPTION
======================

However,

If i am doing this right and it doesn't work.... My next thought was to actually just write a SEPARATE module to do what i want to do. So when i asked what i asked above, it was in generally seeking 'how would you approach this if you were going to do create a custom module'. And not necessarily using nodefamily API etc. I'm open to that as well. But i'm not so sure it wouldn't be better to just write a new module that handles everything it needs to.

How I see What I Want To Do:
-----------------------------

What i want to do probably requires 2 separate modules, one for MyEvent and one for Venues. But i'm not sure if that is the right approach? Then these have to integrate probably using hooks. And that's probably the biggest part i'm not sure about. Because MyEvents could leverage off of the event module event table and Venues could levearge off of location module location table, there's more hooks and intercommunication going on. So there's potential communication between upto 4 contrib modules.

Also, and though this may defeat the purpose of a custom module, my 2 CCK types actually function quite well individually. It's tying them together in a meaningful way that is my problem. So in theory if there was a way for my MyEvent custom module to simply provide the functionality of tying the two CCKs together that is an option as well (as stated above, it means you either have to 1) manually create the 2 CCK types it wants ahead of time which kind of uncouples it from the module which is counterintuitive or 2) provide the module with the functionality to create the 2 CCK types itself on install - not sure if CCK provides an API to do that or not)

The Problem
------------

I haven't found any good module.examples or handbook links that might be good as a basis for creating what i want to do. There are very simple examples, but they tend to ignore the intermodule communication aspects (unless i'm looking in the wrong place). I think it's the intercommunication between all of the modules my custom module will interact with and doing it in the right places at the right times using the right drupal hooks that i would like a leg up on.

FWIW
------

And i want this to be understood, i'm not asking anybody to write my code. i'm proficient enough in php/mysql to do this. my goal is to provide a solution that is architected well so that it not only works within the drupal core system, but is optimised and most important of all plays by the rules. it's the dovetailing of what i want to do within the drupal architecture where i'm looking for any guidance. And again i think i'm most concerned about my intermodule communication between siblings (MyEvent, Venue, event, location) here.

fwiw2, i have been digging into the API manuals on my own. I have been searching the forums. I have been looking at module code. So i'm not coming in blind asking for help. i have put my own effort into this up front before asking for help, hopefully so i'm asking the right / more pointed questions.

My thought is that if one understands this intercommunication framework then the process becomes much easier and one can get going with the project. Later in the module development process, one can find most of what they need in the API manual independently by deduction. On those occasions where guidance is warranted, one can then ask significantly less but more pointed questions in the forum when stuck.

================
WRAP IT UP
==============

Back to my question(s):

With what you know about my problem described above, (not limited to but as a starting point the following):

- how might you approach this - as 1 or 2 or x modules
- what might you be thinking about in terms of the intermodule communication (timing, hooks, etc)
- what module would you suggest i take a look at (it incorporates some of the things you're trying to do and you can see the hooks it's using)
- is there anything that you feel i should be on the lookout for when solving this kind of problem (this area can be tricky, etc)
- here's a link[s] that might help

fwiw, i also know that folks are busy out there working on their own stuff. so if you don't/can't reply that's cool.

fago. i know that was a rather long post. but i think it redefines the problem and sets up the thread for a much more defined exchange. Partly because

1) i would REALLY like to know why nodefamily viewsfusion isn't working (tell me i'm doing something wrong)
2) i would like to write my own COMPLEX modules at some point (whether to solve this or another problem)

thanks much for your time and your module contributions

fago’s picture

hm, what I still haven't got. what is your desired interface for choosing the nodes?
how should the system know which nodes shall be connected? choose venues with cck nodereference or through creating them through the same pageroute or how?

newdru’s picture

If i'm going to use cck, nodefamily, views_fusion, then views would be the interface yes?

(btw i have no idea what pageroute is?)

nodefamily + viewsfusion provides what i think YOU are referring to as the "interface". setting the nodefamily parent child on the two types (events & venues) creates the link to make the views act like an inner join between myevents and venues ccks. Doesn't it???? That allows for the creation of a sql result set where every one of myevents would be JOINED to it's related venue (by the venue node reference field in the event cck??? yes???) and assoicated fields. IF that isn't correct than i'm completely missing the purpose of nodefamily, views_fusion and CCK node_reference. And then i can't do what i want to as stated in this thread.

Assuming the above is correct, I would then use the views filters as what I THINK as the interface. If i set no filters, it pulls the entire join set. I probably will filter on all dates greater than current time - 2 hours or something to give me all event from today into the future (that seems to be the most useful). i could filter by venue if i wanted to pull all events for a given venue.. etc. If i assigned categories, i could filter on an "event type" like speeches, concerts, etc.

Then i assign an url to the view and i have my page.

I guess i'm missing something but that interface (regardless how you define it) seems pretty clear to me. This should work. But it's not right now.

PART 2:

I imagine if this was a module i'm not sure what the interface would be.. that's partly what i'm asking. what would be the best way to achieve this?

FWIW.. since i last posted to this thread, i dove more into the API and i think i'm starting to see how i might do this. I was thinking about 2 modules. one for events and one for venues. events requires that venues be installed. each has it's own forms interface to update it's associated tables. (i guess if i wanted to, i could actually just make one module - i think?). the events module would then be responsible for joining the venues table (easy to do i think because a FK exists by design in the tables as defined by the module. e.g. the relationship is predetermined) and filtering the result set on certain conditions (e.g. dates greater than today) and then creating pages/blocks for each of those predefined "views", etc. So the interface would all have to be predefined in the events module.

In fact, what i'm starting to see is that every module that spits out something to the screen is basically doing what views already does. Or alternatively views provides an interface for the same functionality that every module that is going to create a page or a block provides. If i look at the underlying code, i'm probably going to see lots of similarity there. In that sense VIEWS rocks! It's abstracting functionality common in all modules and making it available to the avg drupal user. For any data that shares the same node id, writing a module is probably overkill as views + CCK allows you to do what a simple module would do. This is mostly for SIMPLE data types only though.

Unless of course, nodefamily or some other mechanism plus views_fusion becomes ROCK SOLID enough to SEAMLESSLY JOIN multiple tables underneath the view via a separate lookup table that relates any tables you determine you want related in some meaningful way... Once that happens, module writing probably becomes moot. But i imagine this is easier said than done.

That said. Once i get the module writing process down, it will probably be pretty easy (i'm hoping) to crank out new modules based upon ones i've already written or just extending current ones to provide added functionality. Modules have one VERY BIG advantage over any of the views fusion type stuff based on CCKS. That is that modules are SELF CONTAINED. I can pick it up and drop it anywhere and turn it on to add the functionality i want. Creating it takes a little longer but once it's done it's very reusable. It's also probably easier to update and merge table data based on a custom module versus that created by CCK..

harriska2’s picture

Probably not an option as it requires drupal 5 and making a module but tapir allows more complex situations.

newdru’s picture

until you mentioned it here..

it looks like it's only 5.0 and i'm currently using 4.7.

Can you say more about HOW i MIGHT use it to solve my problem?

i looked at it real quickly and am not sure how it would help immediately.

thanks for posting the tip

Henrik Opel’s picture

Hi ne wdru,

concerning your Question 3, you might want to take a closer look on the devel.module. It helps a lot, especially the output of db queries on every page and the possibility to break execution temporarily on redirection. But it won't solve your initial problems, it just helps debugging - or simply understanding what a certain module is doing.

As for your initial problem - I can't help due to lack of knowledge, but I feel your pain. I'm constantly surprised to find that I have to write php kludges and hacks to do completely obvious (to me) standard CMS things, while much more complicated and non obvious things (again: to me) are already cared for. I guess the key is with the 'obvious to me' part ;)

Arto’s picture

3) THIS IS A BIGGIE FOR ME.. is there any way to trace the view, it's vars, db calls, db queries, etc WHILE i load the view in the browser so i can debug what it's doing when it's loaded?

Have a look at the Trace module that I'm developing. With all options enabled you can get more trace output than you care for (megabytes per page request) ;-)

--
Arto Bendiken -- author of drush, Trace, Boost and Timeline.

rickvug’s picture

just subscribing for now... I'll likely be trying Views Fusion soon so maybe I'll pipe in afterwards.

-------------------------------------------------------------------
Rick Vugteveen |rickvug.com @rickvug on Twitter

newdru’s picture

And let me know if it works..

I've spent a fair amount of time on this but need to get some other things done so i've dropped trying to do this as of right now.

So if/when you try it, post here to let us know how it goes..

thanks

laken’s picture

tracking...

bcn’s picture

tracking...

ezra-g’s picture

tracking...

Ezra Barnett Gildesgame

ezra-g’s picture

I'm looking into patching Nodefamily to set relationships by Nodereference in parent nodes.

Ezra Barnett Gildesgame

newdru’s picture

more specific about when and how that that will help solve the current problem?

did you find a bug in it's implementation or are you just adding new feature/functionality?

thanks