I thought this would be a very common subject, but I can't seem to find a good discussion on this one.
Assume I have these CCK node types,
Order ([nid], [title], [cck-uid-ref], [cck-bid-ref], ...[price])
Book ([nid], [title], [cck-pid-ref], [cck-aid-ref], ...)I have several Order nodes
nid, cck-uid, cck-bid, price
x, 1, 111, ..., $100- user 1 has ordered book 111
y, 1, 222, ... $200 -user 1 has ordered book 222
z, 2 111,... $100 -user 2 has ordered book 111
...I have 2 Book nodes
nid, title cck-pid cck-aid
111, AAA, p1, a1
222, BBB, p2, a2I want to create a node view as below
uid, book, publisher, author, price
1, AAA, p1, a1, $100
1, BBB, p2, a2, $200
2, AAA, p1, a2, $100Basically, expand the book fields like a simple join of Order and Book nodes (like two db tables).
I looked into this for a long time; my conclusion is that Views cannot work this way.
Question 1: Am I missing anything? or is there a way views can do this?
I believe to display this table requires custom coding. I am in the midst of doing that (I hope I'm not wasting my time). I find myself struggling with excessive db reads trying to expand these fields.
While linking in the expanded reference nodes, I need to take into consideration that the referenced book nodes may have already been deleted (accidentally or intentionally). So I CANNOT use join in my SQL statement (otherwise, some orders will be missing).
So, I first query all the Orders, then I loop through the records; I look for referenced Book records, if I can't find the book, I still need to show the order (noting that the book doesn't exist or leave book data blank).
<?php
$result = db_query("select * from {nodes} node inner join {content_type_order} order on node.nid=order.nid;");
while ($row = db_fetch_object($result)) {
// 1. get cck-uid-ref, retrieve user (customer) fields (may even have cck fields...)
// 2. get book-id-ref, retrieve book cck fields
// 3. if Book has cck-reference fields, then more db read here...
// generate one output row (user/customer, book-title, publisher, author)
}
...
?>Question 2: is this the right way to do it? Instead of one "join" query, now I'm performing queries inside the loop. If the book further reference other CCK nodes (publisher, author), then I have to do more queries inside the loop. It looks like an atrocious algorithm to me. I don't see how I can avoid it.
Comments
relationships?
did you try adding relationships to follow the user and node references?