Re: One to many relationship

From: Todd O'Bryan (
Date: Thu Nov 25 2004 - 08:12:01 EST

  • Next message: Mike Kienenberger: "Re: mostly shared model"

    You're trying to do too much work.

    First, Cayenne will handle PK's for you, but you have to use INTEGERs.
    I just make it a habit to put an
    id attribute in every table. If you want, you can add a UNIQUE
    descriptor to your SQL before you create
    your database to insure categories don't show up more than once.

    Try this:

    attributes: id (INTEGER), name (VARCHAR)
            you may want to make name UNIQUE

    attributes: id (INTEGER), categoryID (INTEGER), name (VARCHAR)
            you may want to make (categoryID, name) UNIQUE

    attributes: id (INTEGER), subcategoryID (INTEGER), etc.

    Now, the interesting part. You have to set up relations from each thing
    to what it should be related to.
    In subcategory, set up a relationship called "category". Under target
    choose category, then click on
    Database Mapping. For the reverse relationship, category will map back
    to all its subcategories, so I'd
    put "subcategories" as the reverse relationship. Click "Add" then
    choose categoryID as the source and
    id as the target. The relationship will exist when those two are the

    Similarly, create a relationship from item to subcategory. My suggested
    reverse relationship name is

    Finally, create Item, Category, and Subcategory object entities and map
    them to the corresponding tables.

    You can create a Category or Subcategory in the normal way. If dc is a

    Category cat = (Category) dc.createAndRegisterNewObject(Category.class);
    Subcategory subcat = (Subcategory)

    Item item = (Item) dc.createAndRegisterNewObject(Item.class);

    Then, item.getSubcategory().getName() will return "Botany".
    item.getSubcategory().getCategory().getName() will return "Biology".

    The only hitch with this stuff is that you have to query to get Objects
    of the Category and Subcategory type when you're creating new items.

    For example, if you've previously created a Subcategory called
    "Biochemistry" and you want to make your current item that subcategory,
    you have to do something like

    Subcategory s = (Subcategory) dc.performQuery(Subcategory.class,
    ExpressionFactory.matchExp("name", "Biochemistry")).get(0);

    This code will die if "Biochemistry" isn't in there. For classes with
    unique names that I want to grab by name from the database, I write a
    static method in the class that will do that, does the type checking,
    returns null if there's no such object, etc.


    On Nov 25, 2004, at 5:05 AM, Alex Gunawan Wibowo wrote:

    > Hello..
    > Apology in advance if this is rather a very simple
    > question for many of you..
    > First, let me explain the database schema..
    > there are 3 tables..
    > subcategory, category, and item.
    > category has PK "category name",
    > subcategory has composite PK of : "category name" &
    > "subcategory name". (as it is connected to category
    > table)
    > item has it's own PK, item ID.. but in addition, it
    > stores a foreign key of "category name" & "subcategory
    > name" (as it is connected to "subcategory" table).
    > Hope that's clear enough.
    > Now, the problem is that i have an entry from "item"
    > table.
    > when i call
    > anItem.getToSubCategory().getSubCategory()
    > -> getToSubCategory() supposed to return a SubCategory
    > object.
    > -> getSubCategory() supposed to return a String, i.e.
    > one of the PK.
    > both method are generated by CayenneModeller.
    > it says "too many objects.." (CayenneRuntimeException
    > is thrown). Can't exactly remember what the error
    > says.. but it's along the line of saying that there
    > are too many object in the subcategory table that has
    > "OTHER" as the "subcategory" field. This is because
    > "anItem" has as it's entry "OTHER" as the
    > "subcategory" and "bla" as the "category".
    > I have a little difficulty in understanding the
    > problem here. I know that there are a lot of of entry
    > in the
    > table "subcategory" that has "OTHER" as the field
    > entry..(since it's a composite PK anyway).
    > So can anyone tell me how i can retrieve the foreign
    > key "subcategory" for "anItem" object above??
    > The way I mentioned above works in Hibernate.. and I
    > don't know why it does not work in Cayenne..
    > What amuses me more is that the call above seems to
    > make a connection to the database.
    > (In Hibernate, "anItem" object above would have
    > "SubCategory" object as one of it's field.. so the
    > method above works perfectly ok).
    > Basically, what I want is the "subcategory" field (of
    > type "String") from the "anItem" object. I don't want
    > the "SubCategory" object that I can get easily using
    > anItem.getToSubCategory()
    > Thanks a lot for the help!
    > Best regards,
    > Alex.
    > __________________________________
    > Do you Yahoo!?
    > The all-new My Yahoo! - Get yours free!

    This archive was generated by hypermail 2.0.0 : Thu Nov 25 2004 - 08:12:05 EST