[development] Crossing information between content types

Barry Jaspan barry at jaspan.org
Wed Oct 24 21:38:13 UTC 2007

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.


More information about the development mailing list