RE: How to create JOIN query

From: McDaniel, Joe R. (mcdanij..igr.org)
Date: Wed Jun 16 2004 - 12:42:14 EDT

  • Next message: McDaniel, Joe R.: "RE: How to create JOIN query"

    ... and do a filter on the List resulting from the toXXX.... as in this
    example (a "plate" of a particular type and containing a given prefix
    code has (say) 384 wells that we want to get sorted; there is a foreign
    key to PlateSlidePrefixes but not to Primers ...):
     
    (This does not really filter, but sorting is the same conceptually...)
            SelectQuery q = new SelectQuery(ZPlates.class);
            q.setQualifier( Expression.fromString( "plateTypCd = 'GPR'") );
            q.andQualifier( Expression.fromString(
    "toZPlateSlidePrefixes.prefix = 'UBA'") );
            
            SelectQuery primerQuery = new SelectQuery(ZPrimers.class);
            Expression primerExp = Expression.fromString("primerId =
    $primerId");
            Map primerParams = new HashMap();
     
            List plates = context.performQuery(q);
            if(plates.size() > 0)
            {
                for(int i = 0; i < plates.size(); i++)
                {
                    ZPlates plate = (ZPlates)plates.get(i);
                    String plateTypCd = plate.getPlateTypCd();
                    if(plateTypCd != null) {
                        System.out.println(plateTypCd);
                        List wells = plate.getZWellsArray();
                        List wellOrder = new ArrayList();
                        wellOrder.add(new Ordering("xCoord", true) );
                        wellOrder.add(new Ordering("yCoord", true) );
                        Ordering.orderList(wells, wellOrder);
                    }
                }
            }
     

            -----Original Message-----
            From: McDaniel, Joe R. [mailto:mcdanij..igr.org]
            Sent: Wednesday, June 16, 2004 11:37 AM
            To: cayenne-use..bjectstyle.org
            Cc: donald.mackinno..houghtweb.com
            Subject: RE: How to create JOIN query
            
            
            The idea is to use object relationships to do the "join" not a
    join, per se to get the data. (I have trouble with this idea, too.) You
    can add a condition in the query to limit the results on the initial
    table, but to get the data use the toXXX (assuming you have a foreign
    key relationship, of course).
             
            If you have a complex relationship that is messy to do in
    objects, try creating a view. I think (but cannot figure out) there is
    a way to create a dynamic view in Cayenne.
             
            I, too, would like something like Public Tree (or some
    structure) Context.query(List tables, Expression expression) (probably
    not OO, but at least one could get all the results in a single query).
             
            Best,
             
            Joe

                    -----Original Message-----
                    From: Donald Mackinnon
    [mailto:donald.mackinno..houghtweb.com]
                    Sent: Wednesday, June 16, 2004 9:47 AM
                    To: 'cayenne-use..bjectstyle.org'
                    Subject: How to create JOIN query
                    
                    
                     
                    Hi
                     
                    I am new to Cayenene and am not sure about the best way
    to query 2 tables.
                     
                    Is it best to create a Flattened Relationship using the
    Cayenne modeler?
                     
                     
                    Thanks
                     
                    Donald

                     

    This archive was generated by hypermail 2.0.0 : Wed Jun 16 2004 - 12:42:48 EDT