Crossing information between content types
Hi all, Let's say I have two content types - a person and a bank account. A person can have several bank accounts, and a person also has an address. Now I need to present a listing if all the people who live in New Jersey and have more than $100k in their bank account. In any database application it would be dead easy - just use a SELECT.. JOIN on these two tables and you're done. However, on Drupal, I am not sure what is the best way to accomplish that. First thing I did was to define these two content types in CCK, install the relativity.module and declare parent-child relationships between the person and her bank account. Then I hit the wall.. I can't combine information in a view from these two content types, and I can't use views fusion module, as it doesn't know about relativity (only nodefamily). My dream solution would be to expose relativity.module into the views admin screens, and just add/filter the fields of the related content types. But since relativity is generic and views uses a flat view of all fields, I really can't think of a way to accomplish that cleanly. Of course I can write the query myself, but since these are CCK nodes, I can't use a direct SELECT, and have to iterate through the nodes manually. It is also not maintainable through the UI being strictly in code (and can't use views plugins etc.). Another option could be to use views hooks (pre_view, query_alter..) to load the parent node, check the condition and manipulate the view accordingly. This doesn't feel right neither. I ended up going back to my dream solution and face it with reality. Since I know my CCK structure and the queries I need, I can go specific. I created a module and added the 'location' table, but not connected to the 'node' table, rather to 'relativity' table, through the 'parent_nid'. This way I can add the parent-city as a field, filter and an argument to the view. As a bonus, I even reused the original location.module city argument handler. The downside is that it's quite a lot of code (although once written can be managed through the views administration). Does this make any sense? Isn't there a simpler solution? Cheers, -- Yuval Hager
Yuval Hager <yuval@avramzon.net> writes:
Let's say I have two content types - a person and a bank account. A person can have several bank accounts, and a person also has an address.
Now I need to present a listing if all the people who live in New Jersey and have more than $100k in their bank account. In any database application it would be dead easy - just use a SELECT.. JOIN on these two tables and you're done.
However, on Drupal, I am not sure what is the best way to accomplish that.
CCK and Views are great and a lot can be accomplished via the UI, but sometimes you just write your own SELECT statement.
Of course I can write the query myself, but since these are CCK nodes, I can't use a direct SELECT...
Yes, you can. Node type person with a "state" field. Node type bankacct with an "owner" field that is a nodereference to the person that owns it and a "balance" field. -- select each person/acct pair SELECT person.nid, acct.nid FROM {node} person -- connect to the CCK person table INNER JOIN content_type_person ct_person ON ct_person.nid = person.nid -- connect to all CCK bankaccts that are owned by this person INNER JOIN content_field_bankacct acct ON acct.field_owner_nid = person.nid -- impose the conditions WHERE person.type = 'person' AND ct_person.state = 'NJ' AND acct.field_balance > 100000 The WHERE person.type = 'person' clause isn't really necessary b/c of the joint to content_type_person. This gets all person/account pairs over $100k. If you just want the person nids, SELECT DISTINCT person.nid instead. IMPORTANT note: Create an index on acct.field_owner_nid. CCK does not do this for you (I think it should). And run your query through EXPLAIN when you are done to see what other indices you might need. Barry
On Wednesday 24 October 2007, Barry Jaspan wrote:
Yes, you can. Node type person with a "state" field. Node type bankacct with an "owner" field that is a nodereference to the person that owns it and a "balance" field.
-- select each person/acct pair SELECT person.nid, acct.nid FROM {node} person -- connect to the CCK person table INNER JOIN content_type_person ct_person ON ct_person.nid = person.nid -- connect to all CCK bankaccts that are owned by this person INNER JOIN content_field_bankacct acct ON acct.field_owner_nid = person.nid -- impose the conditions WHERE person.type = 'person' AND ct_person.state = 'NJ' AND acct.field_balance > 100000
The WHERE person.type = 'person' clause isn't really necessary b/c of the joint to content_type_person. This gets all person/account pairs over $100k. If you just want the person nids, SELECT DISTINCT person.nid instead.
IMPORTANT note: Create an index on acct.field_owner_nid. CCK does not do this for you (I think it should). And run your query through EXPLAIN when you are done to see what other indices you might need.
Barry
Thanks! these are very useful tips. Usually, I prefer to enjoy the benefits of information hiding, and not open the hood if possible. Accessing tables that are managed by CCK seems like a hack to me.. I guess it would change if some fields become multiple, or when CCK decides to manage the tables differently in a future version... -- Yuval Hager
The CCK developera re always one step ahead. They have some helpful functions for building joins that will work even if a field changes its multiple status or you do an upgrade. Here is an example "safe" join: $field_bw = content_fields('field_blog_weight'); $db_info_bw = content_database_info($field_bw); $sql = "SELECT n.nid, n.title FROM {node} n JOIN {". $db_info_bw['table'] . '} bw ON n.vid=bw.vid';
On Thursday 25 October 2007, Moshe Weitzman wrote:
The CCK developera re always one step ahead. They have some helpful functions for building joins that will work even if a field changes its multiple status or you do an upgrade. Here is an example "safe" join:
$field_bw = content_fields('field_blog_weight'); $db_info_bw = content_database_info($field_bw);
$sql = "SELECT n.nid, n.title FROM {node} n JOIN {". $db_info_bw['table'] . '} bw ON n.vid=bw.vid';
I *knew* this form of abstraction exists, I just never got around to it yet. Thanks for the pointer. I am still thinking how can this sort of queries can be exposed to the UI. I believe the natural place is views, but am not sure exactly how. The straight forward way would be to make all parent's fields/filters/args available in the views admin screens. I see two problems with this approach: (o) The list of fields will quickly be cluttered by tons of 'Relativity: Parent of <something>: <something else' fields (o) I am also not sure this is at all possible. It requires adding all the relevant tables through relativity.parent_nid instead of node.nid, and reuse all the filter and arguments handlers.. Any other thoughts? OTOH, maybe the use case is just weak, so this is really a non-issue.. -- Yuval Hager
It is a strong use case IMO ... We shall see this solved when Views2 and node_reference are playing nicely together. I would think that the node ref form will add a section where you select the fields from the referenced node that you want exposed in Views. Then noderef and content.module will work with Views2 to expose just those fields. This resolves the UI clutter issue. We need Views2, so this won't be solved immediately. I'm sure Earl would love some testers and documenters to help on that Views2. Start by reading http://www.angrydonuts.com/views_2_high_level_design and http://groups.drupal.org/views-developers On 10/25/07, Yuval Hager <yuval@avramzon.net> wrote:
On Thursday 25 October 2007, Moshe Weitzman wrote:
The CCK developera re always one step ahead. They have some helpful functions for building joins that will work even if a field changes its multiple status or you do an upgrade. Here is an example "safe" join:
$field_bw = content_fields('field_blog_weight'); $db_info_bw = content_database_info($field_bw);
$sql = "SELECT n.nid, n.title FROM {node} n JOIN {". $db_info_bw['table'] . '} bw ON n.vid=bw.vid';
I *knew* this form of abstraction exists, I just never got around to it yet. Thanks for the pointer.
I am still thinking how can this sort of queries can be exposed to the UI. I believe the natural place is views, but am not sure exactly how. The straight forward way would be to make all parent's fields/filters/args available in the views admin screens. I see two problems with this approach: (o) The list of fields will quickly be cluttered by tons of 'Relativity: Parent of <something>: <something else' fields (o) I am also not sure this is at all possible. It requires adding all the relevant tables through relativity.parent_nid instead of node.nid, and reuse all the filter and arguments handlers..
Any other thoughts?
OTOH, maybe the use case is just weak, so this is really a non-issue..
-- Yuval Hager
Yuval Hager wrote:
Hi all,
Let's say I have two content types - a person and a bank account. A person can have several bank accounts, and a person also has an address.
Now I need to present a listing if all the people who live in New Jersey and have more than $100k in their bank account. In any database application it would be dead easy - just use a SELECT.. JOIN on these two tables and you're done.
However, on Drupal, I am not sure what is the best way to accomplish that.
First thing I did was to define these two content types in CCK, install the relativity.module and declare parent-child relationships between the person and her bank account.
For the sake of simplicity and performance, don't use CCK if you need to do complex joins.
On Thursday 25 October 2007, David Strauss wrote:
Yuval Hager wrote:
First thing I did was to define these two content types in CCK, install the relativity.module and declare parent-child relationships between the person and her bank account.
For the sake of simplicity and performance, don't use CCK if you need to do complex joins.
This is not a complex query for business applications, it is actually very common IMHO. As Drupal also becomes the basis of applications, I would love to see this easier done in Drupal. I am not sure how though... -- Yuval Hager
participants (4)
-
Barry Jaspan -
David Strauss -
Moshe Weitzman -
Yuval Hager