-
1. Re: how to perform SELF JOIN using CMP
didi1976 May 12, 2003 12:47 AM (in response to taiwuco)Hi,
if I got you right, you are trying to get all parentAccounts of a childAccount so the query should look like this one:
SELECT DISTINCT OBJECT(ap) FROM Account ac, IN (ac.parentAccounts) ap WHERE ac.accountID = ?1
You did not write what you intend to do so I hope the above query does help.
Dietmar -
2. Re: how to perform SELF JOIN using CMP
dema May 13, 2003 11:40 PM (in response to taiwuco)If using only relations will be enough then here are my relations in TreeBean:
CREATE TREE (
ID NUMBER,
PARENT_ID NUMBER
)
Actually this was generated by Middlegen for XDoclet.
/**
* This is a bi-directional one-to-many relationship CMR method
*
* * @ejb.interface-method view-type="local"
*
* @ejb.relation
* name="TREE-cmp20-TREE-cmp20"
* role-name="TREE-cmp20-1-has-TREE-cmp20-2"
*
* @jboss.relation-mapping style="foreign-key"
* @ejb.value-object
* aggregate="kz.alb.pcat.domain.interfaces.TreeValue"
* aggregate-name="ChildNode"
* members="kz.alb.pcat.domain.interfaces.TreeLocal"
* members-name="ChildNode"
* type="Collection"
* relation="external"
*/
public abstract java.util.Collection getChildTrees();
----------------------------------------
/**
* This is a bi-directional one-to-many relationship CMR method
*
* @return the related kz.alb.pcat.domain.interfaces.TreeLocal.
*
* @ejb.interface-method view-type="local"
*
* @ejb.relation
* name="TREE-cmp20-TREE-cmp20"
* role-name="TREE-cmp20-2-has-TREE-cmp20-1"
* cascade-delete="yes"
*
* @jboss.relation-mapping style="foreign-key"
*
* @jboss.relation
* fk-constraint="true"
* fk-column="PARENT_ID"
* related-pk-field="id"
*
*/
public abstract kz.alb.pcat.domain.interfaces.TreeLocal getParentTree(); -
3. Re: how to perform SELF JOIN using CMP
taiwuco May 16, 2003 1:12 AM (in response to taiwuco)Dietmar, thank you for your comments. However, my question is not just to get the direct parents of a child node. I want to get
1. the parents, grand parents, grand grand parents.
and
2. the children, grand children, grand grand children.
That is, to get tree structures in both directions.
Brian -
4. Re: how to perform SELF JOIN using CMP
taiwuco May 16, 2003 1:22 AM (in response to taiwuco)dema,
Thanks. I can do bi-directional table-based 1:M relationship betwen a table and itself. From your message, I found Middlegen and I will try to see if I can use the tool to save me more time. Thanks.
The problem is not the relationship but the query:
How to do self join query.
If there are 1,000,000 members forming a tree-like organization such as in a multi-level marketing membership, or in a bill of material relationship. I want to get all levels of the tree below the node. Howver, the other problem may come as the self join is very expensive on join 1 million members. Is there an alternatve way?
Brian -
5. Re: how to perform SELF JOIN using CMP
didi1976 May 22, 2003 3:02 AM (in response to taiwuco)Let us asume you have an entity bean which has a CMR between parent and children which is bidirectional. Now you have two methods, one to get the parent (e.g. getParentAccount()), and one to get the collection containing the children (getChildAccounts()).
Now just write a simple recursive method which generates the desired collection:
public java.util.Collection getAllChildAccounts(Account startAccount)
{
// Generate a collection to put the found accounts into
java.util.Collection col = new java.util.LinkedList();
// add startAccount
col.add(startAccount);
addChildAccounts(col,startAccount);
return col;
}
public void addChildAccounts(java.util.Collection col, Account startAccount)
{
java.util.Iterator it = startAccount.getChildAccounts().iterator();
Account tmpAccount = null;
while( it.hasNext() )
{
tmpAccount = (Account)it.next();
col.add(tmpAccount);
addChildAccounts(col,tmpAccount);
}
}
You could then do the same for the parent accounts. Hope I could help,
Dietmar -
6. Re: how to perform SELF JOIN using CMP
taiwuco May 23, 2003 2:09 AM (in response to taiwuco)(Re: Dietmar)
Thanks. I think your codes will work. However, let's assume I have 300,000 member accounts. In many cases, it will generate a lot of SQL queries if I want to find the account tree from the top levels of the membership hierarchy. Will the recursive queries be expensive? That is, I have to buy more equipment to provide more computing power. -
7. Re: how to perform SELF JOIN using CMP
didi1976 May 26, 2003 3:07 AM (in response to taiwuco)You are right that the above code will produce many SQL Statements but it is the most portable way.
I have only seen one better chance but that is depending on your DB. In a former project we used recursive SQL Statements to do such a SELECT but then you are tied to a feature of a DB which may not be portable. I have never tried to use that with EJBs and CMP.
An example of recursive SQL on DB2 can be found here:
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0203venigalla/0203venigalla.html -
8. Re: how to perform SELF JOIN using CMP
taiwuco May 31, 2003 1:48 AM (in response to taiwuco)Thanks. At least I know I can't avoid using recursive quries to solve the problem unless I use a specific DBMS such as DB2. Unfortunately, I don't have DB2.
Best Regards -
9. Re: how to perform SELF JOIN using CMP
santoshp Aug 15, 2003 1:26 PM (in response to taiwuco)I just visited this forum. If this is not a closed issue, I here is my thought.
I would like to do it using a SQL query:
SELECT parent_id , child_id , level
FROM account
START WITH parent_id = 1111
CONNECT BY PRIOR child_id = parent_id
-- this query returns all the children...grand children...grand grand chidren..... until the last one in hierarchy for parent=1111.
Similariliy to get all the parents, this is the query:
SELECT parent_id , child_id , level
FROM account
START WITH child_id = 9999
CONNECT BY PRIOR parent_id = child_id
----------
To have a nice view of the hiearchy use these queries respectively:
SELECT LPAD(' ',2*(LEVEL-1)) || parent_id , child_id
FROM account
START WITH parent_id = 1111
CONNECT BY PRIOR child_id = parent_id
and
SELECT LPAD(' ',2*(LEVEL-1)) || parent_id , child_id
FROM account
START WITH child_id = 9999
CONNECT BY PRIOR parent_id = child_id -
10. Re: how to perform SELF JOIN using CMP
samart Aug 21, 2003 6:57 PM (in response to taiwuco)just load all 1 M beans, ordered, into a list, then use a recurisive function to build the node tree, starting at any node. create a stack and push and pop to walk and build the node tree. i've done this to create an xml based catalog that had to be arbitrarily deep.