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 <yuvalavramzon.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  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

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 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  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

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

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  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  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://grou
ps.drupal.org/views-developers

On 10/25/07, Yuval Hager <yuvalavramzon.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  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
>
>