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,
>
> could you be a little bit more specific on what is wrong and on what
result
> you do expect.
> 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.
> >> >
> >> >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
> >> and
> >> >Main. I can see that it tries to apply the first filter, but then
fails
> > to
> >> >properly assemble the data.
> >> >
> >> >
> >> >
> >> >On a side note,
> >> >
> >> >How would I add the prefetched relationship between Fleet & CarType
> >> from
> >> >here?
> >> >
> >> >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
> >> 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 ? )|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
> > _
> >> >FK,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
> > car_type_id
> >> >= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
> > WHERE
> >> >car_type_id = '1'
> >> >
> >> >1044029875020|310|0|statement|SELECT
> >> >make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
> > car_type_id
> >> >= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
> > WHERE
> >> >car_type_id = '2'
> >> >
> >> >1044029875621|310|0|statement|SELECT
> >> >make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
> > car_type_id
> >> >= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
> > WHERE
> >> >car_type_id = '3'
> >> >
> >> >1044029876222|310|0|statement|SELECT
> >> >make,model,catagory,year,weight,car_type_id FROM CAR_TYPE WHERE
> > car_type_id
> >> >= ? |SELECT make,model,catagory,year,weight,car_type_id FROM CAR_TYPE
> > WHERE
> >> >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
> >>
> >>
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'
> >> >
> >> >
> >> >
> >> >---------------------------------------------------------------------
> >> >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]
