i assume you're querying for _garage_ with these criteria:
Criteria criteria = new Criteria();
criteria.addLike("fleet.carType.make","Dodge");
the first query will return all garages having "Dodge". in your case "Main" and "Florida".
the second query returns the whole fleet of garage 1, because of the 1:n between garage and fleet.
this relationship can not know anything about the previous query and it's loaded because of auto-retrieve.
i f you want to get all _cars_ of type "Dodge" i propose to query first for those cars and then navigate to the garage.
hth
jakob
casterx wrote:
Jakob,
I have the following relationship.
Garage 1..* Fleet
Fleet 1..1 CarType
I'm trying to get a list of all the garages and a list of dodge
(carType.make) located in the respective garages.
The DB has two garages: Florida and Main. Each garage has many Fleet records
some Dodge and some Ford.
I create the following criteria (and do use distinct which doesn't help) to
get the desired results.
criteria criteria = new Criteria();
criteria.addLike("fleet.carType.make","Dodge");
However, the output shows non-dodge records (although the first query
filters for it). Perhaps I need to add another criteria because the the
"carType.make=Dodge" is not passed onto the latter queries.
Florida
[Red Ford: Taurus, Blue Dodge: Intrepid, Teal Ford: Escort, Teal Ford:
Mustang, Purple Ford: Escort, Purple Ford: Escort],
Main
[White Ford: Mustang, White Ford: Taurus, Brown Ford: Taurus, Brown Ford:
Taurus, Lime Dodge: Intrepid]]
P6Output:
SELECT DISTINCT A0.location,A0.garage_id FROM GARAGE A0,FLEET A1,CAR_TYPE A2
WHERE A1.car_type_fk=A2.car_type_id AND A0.garage_id=A1.garage_FK AND
(A2.make LIKE 'Dodge' )
SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
FK,A0.condition
FROM FLEET A0 WHERE A0.garage_FK = '1'
SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
car_type_id = '1'
SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
car_type_id = '2'
SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
car_type_id = '3'
SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
car_type_id = '5000'
SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
FK,A0.condition
FROM FLEET A0 WHERE A0.garage_FK = '2'
----- Original Message -----
From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Sunday, February 02, 2003 2:41 AM
Subject: Re: Big 9.9.1 (.9.8) Bugs with Criteria on Collections
hi caster,result
could you be a little bit more specific on what is wrong and on what
you do expect.fails
path expressions over collections may need a 'distinct'.
jakob
On Sat, 01 Feb 2003 18:23:22 -0600, casterx <[EMAIL PROTECTED]> wrote:
Jakob,
Removing lower() doesn't fix anything. The results are the same.
Caster
----- Original Message -----
From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Saturday, February 01, 2003 2:37 AM
Subject: Re: Big 9.9.1 (.9.8) Bugs with Criteria on Collections
hi caster,
does it produce the expected results when you drop 'lower' ?
jakob
casterx wrote:
I have created a three level class layer on top of my data.and
Garage
{
int id;
Collection fleet;
}
Fleet
{
CarType carType;
}
CarType
{
String color;
String model;
}
I try to execute the following query:
criteria.addLike("lower(fleet.carType.model)","Escort".toLowerCase());
Query query = new QueryByCriteria(Garage.class,criteria);
Collection carTypes = broker.getCollectionByQuery(query);
System.out.println(carTypes);
The output is completely wrong. I have to entries in Garage: Florida
Main. I can see that it tries to apply the first filter, but then
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garageto
properly assemble the data.from
On a side note,
How would I add the prefetched relationship between Fleet & CarType
here?GARAGE
Simply adding "carType" doesn't work.
How do I insert custom functions with on chained properties?
criteria.addLike("myFunc(fleet.carType.model),"bla") throws a
StringIndexOutOfBoundsException.
Thanks a million, I can send more code/data/schema if you need it
Caster
Here's the output with P6Spy:
[
Florida
[Red Ford: Taurus, Blue Dodge: Intrepid, Teal Ford: Escort, Teal Ford:
Mustang, Purple Ford: Escort, Purple Ford: Escort],
Florida
[Red Ford: Taurus, Blue Dodge: Intrepid, Teal Ford: Escort, Teal Ford:
Mustang, Purple Ford: Escort, Purple Ford: Escort],
Florida
[Red Ford: Taurus, Blue Dodge: Intrepid, Teal Ford: Escort, Teal Ford:
Mustang, Purple Ford: Escort, Purple Ford: Escort]]
1044029873548|310|0|statement|SELECT A0.location,A0.garage_id FROM
A0,FLEET A1,CAR_TYPE A2 WHERE A1.car_type_fk=A2.car_type_id AND
A0.garage_id=A1.garage_FK AND (lower(A2.model) LIKE ? )|SELECT
A0.location,A0.garage_id FROM GARAGE A0,FLEET A1,CAR_TYPE A2 WHERE
A1.car_type_fk=A2.car_type_id AND A0.garage_id=A1.garage_FK AND
(lower(A2.model) LIKE 'escort' )
1044029873979|310|0|statement|SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
FK,A0.condition FROM FLEET A0 WHERE A0.garage_FK = ? |SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
car_type_idFK,A0.condition FROM FLEET A0 WHERE A0.garage_FK = '1'
1044029874400|301|0|statement|SELECT
make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
WHERE= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
car_type_idcar_type_id = '1'
1044029875020|310|0|statement|SELECT
make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
WHERE= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
car_type_idcar_type_id = '2'
1044029875621|310|0|statement|SELECT
make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
WHERE= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
car_type_idcar_type_id = '3'
1044029876222|310|0|statement|SELECT
make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
WHERE= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
car_type_id = '5000'
1044029877023|310|0|statement|SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
FK,A0.condition FROM FLEET A0 WHERE A0.garage_FK = ? |SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
FK,A0.condition FROM FLEET A0 WHERE A0.garage_FK = '1'
1044029877654|320|0|statement|SELECT
A0.arrival_date,A0.color,A0.milage,A0.fleet_car_pk,A0.car_type_fk,A0.garage_
FK,A0.condition FROM FLEET A0 WHERE A0.garage_FK = ? |SELECT
_--
---------------------------------------------------------------------FK,A0.condition FROM FLEET A0 WHERE A0.garage_FK = '1'---------------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Jakob Bräuchi
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
