[development] Crossing information between content types

[Barry Jaspan]

Yuval Hager <yuval at 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