Here's what I would do:
1) Tables set up as you describe. CUSTOMER would have a BILL_ADDRESS_ID and
a SHIP_ADDRESS_ID, both of which are FK references to the ADDRESS table.
2) Set up DB joins from Customer to Address, and vice-versa.
3) Set up ObjEntities for Customer and Address, and create ObjRelationships
that traverse those. Once done, my Customer class would have to-one
relationships called "billingAddress" and "shippingAddress".
4) You can then search for Customers given Address qualifications:
.matchExp("billingAddress.state", "CO")
.likeExp("shippingAddress.address1", "123 Main")
or even across the relationships
// Obtain the entered state value from some mythical request form
String inputState = request.get("state");
Expression exp = ExpressionFactory.matchExp("billingAddress.state",
inputState);
exp = exp.orExp(ExpressionFactory.matchExp("shippingAddress.state",
inputState);
SelectQuery q = new SelectQuery(Customer.class, exp);
Dave
On Sat, Mar 14, 2009 at 4:28 PM, Joe Baldwin <jfbaldwi..arthlink.net>wrote:
> Robert,
>
> Here is the working SQL
>
> select customer.billAddressOID, custAddress.oid, customerID,
> lastName from customer, custAddress where customer.billAddressOID =
> custAddress.oid;
>
> This SQL returns the result set that I am needing. My brain is stuck in
> SQL, and I am trying to train myself to think from the Cayenne perspective.
> I would like to end up with a List of Customer entities.
>
> Customer has two addresses (billing & shipping both are to-one
> relationships). I would like to search for a list of Customers based on
> individual fields in each of the address entities.
>
> Thanks,
> Joe
>
>
>
> On Mar 14, 2009, at 3:53 PM, Robert Zeigler wrote:
>
> Hi Joe,
>>
>> How about:
>>
>> SelectQuery query = new SelectQuery(Entity1.class);
>> query.setQualifier(ExpressionFactory.matchExp(Entity1.EN|TITY2_PROPERTY,entity2));//where
>> ENTITY2_PROPERTY is the name of object property in entity1 that points to
>> entity2
>> List<Entity1> e1 = objectContext.performQuery(query);//note: perform query
>> is NOT generified, so you'll get a warning here.
>>
>> Robert
>>
>> On Mar 14, 2009, at 3/141:29 PM , Joe Baldwin wrote:
>>
>> I am attempting to create the simplest Cayenne-expedient method of doing
>>> the following query. (I can easily do this in SQL but am a tad confused with
>>> the Cayenne Expression method.)
>>>
>>> I have an Entity (E1) with a one to one relationship with a second entity
>>> (E2). I would like to perform a SELECT Query with a filter on one of the
>>> fields of the relationship-entity (E2.F1) and return a list of the first
>>> entity (E1List).
>>>
>>> What is the most efficient Cayenne way to do this? (I am still a bit
>>> confused concerning how to construct efficient queries in the object domain
>>> vs the relational domain.)
>>>
>>> Thanks,
>>> Joe
>>>
>>>
>>>
>>>
>>
>
This archive was generated by hypermail 2.0.0 : Sat Mar 14 2009 - 17:50:21 EDT