Hi Michal,
So you have a join table in one DB that connects two tables in another
DB. Cayenne should've ideally detected this condition instead of
creating an invalid join query... but anyway. As Cayenne can't really
provide a virtual join in-memory to bridge the two databases (after all
we are not trying to do a DB job), lets look for other possible
solutions. I will show one such solution below. It is done from the
Project perspective, the User side should work similar...
The idea is to override "getProjectEngineer()" in the Project class,
supplying your own array of users. To get the correct list we will have
to do two queries, as the DB-level join is not possible. So here it
goes....
public List getProjectEngineer() {
ToManyList list = (ToManyList) super.getProjectEngineer();
// if list is not faulted yet, this is a good time to do it here
if(list.needsFetch()) {
// we need to fetch data from the join table
// that will provide us ObjectIds of the User table
Object pk = DataObjectUtils.pkForObject(this);
Expression qualifier = ExpressionFactory.matchDbExp("ProjectId",
pk);
DbEntity joinEntity = getDataContext()
.getEntityResolver()
.getDataMap("Forecast DataMap")
.getDbEntity("ProjectEngineer");
SelectQuery query = new SelectQuery(joinEntity, qualifier);
query.setFetchingDataRows(true);
query.setDistinct(true);
query.addCustomDbAttribute("ProjectEngineerId");
List joinIds = getDataContext().performQuery(query);
DbRelationship r = (DbRelationship)
joinEntity.getRelationship("ProjectEngineer");
DbRelationship reverse = r.getReverseRelationship();
// now convert the result to User ObjectIds
List ids = new ArrayList(joinIds.size());
Iterator it = joinIds.iterator();
while(it.hasMoreElements()) {
Map map = (Map) it.next();
ObjectId userOID = new ObjectId(User.class,
reverse.srcPkSnapshotWithTargetSnapshot(map));
ids.addObject(userOID);
}
// finally fault all users and initialize object list
list.setObjectList(getDataContext().performQuery(QueryUtils.selectQueryF
orIds(ids));
}
return list;
}
Note that I haven't tried to compile and run this, just wanted to show
the general direction. Also updates, inserts and deletes should work
fine out of the box, as they write to a corresponding DataNode without
trying to make a join.
Have fun ;-) Hope this helps.
Andrus
On Oct 14, 2004, at 11:36 AM, Michal Kozlowski wrote:
> Hi,
> Awesome product, and I'm new to this so I might be doing something
> wrong. I have 2 databases that I'm trying to access using cayenne and
> I'm having some difficulty. One database has all the information
> (proprietary to a product we use), and our own database that has some
> more information as well as additional relationships between the data,
> simple example below and the source of my problem.
>
> DB A DB B
>
> _ProjectInfo ProjectEngineer
> _UserInfo
>
> Inside DB B ProjectEngineer is a table with 2 fields ProjectId and
> ProjectEngineerId(UserId), as the name states it defines a
> ProjectEngineer for a Project. Now in Cayenne I have a DBEntity
> _ProjectInfo, _UserInfo, as well as Project, User DBObjects in one
> DataMap. In another DataMap I have DBEntity ProjectEngineer, no
> DBObjects for this relationship. I want to be able to go
> Project.getProjectEngineer() returning me the user or vice versa
> User.getProjects() returning me the Projects.
>
> When I do this cayenne creates a wrong select statement assuming the
> ProjectEngineer table is in DB A which it isn't.
>
> SELECT DISTINCT t0.Disabled, t0.FirstName, t0.LastName, t0.LoginName,
> t0.UserId FROM dbo._UserInfo t0, dbo.ProjectEngineer t1 WHERE
> t0.UserId = t1.ProjectEngineerId AND (t1.ProjectId = ?) [bind: 211]
>
> Attached is my cayenne.xml files as well as driver and map files.
>
> I hope I'm understanding cayenne properly and I'm not doing something
> completely wrong, additional info is this is running as a tapestry
> app.
> Thanks for the awesome app.
>
> Cheers
> Michal Kozlowski
>
> Developer/Database Administrator
> IMV Projects Inc.
> (403) 770-7552
>
> It is a painful thing
> To look at your own trouble and know
> That you yourself and no one else has made it
> - Sophocles, Ajax
>
> <Actual DataMap.map.xml><Actual
> DataNode.driver.xml><cayenne.xml><Forecast DataMap.map.xml><Forecast
> DataNode.driver.xml>
This archive was generated by hypermail 2.0.0 : Thu Oct 14 2004 - 19:51:48 EDT