[development] Crossing information between content types

[Yuval Hager]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
Url : http://lists.drupal.org/pipermail/development/attachments/20071025/2915f123/attachment.pgp