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