Query problem!

From: Michael Schuldt (m..scensys.de)
Date: Sun Feb 16 2003 - 05:29:47 EST

  • Next message: Holger Hoffstätte: "Re: high-volume offline processing using cayenne?"

    Hi,

    maybe this problem is a little bit complicate to explain but I will try:

    It would take very long to explain my database schema etc. so I try it in
    plain text.

    For this case I have to concentrate on three main tables of my db.

    They're FIRM, USERGROUP and OFFER.

    FIRM is related to USERGROUP by an n:m relationship (association table not
    mentioned here!)
    USERGROUP is related to OFFER by an n:m relationship (association table not
    mentioned here!)

    Now I'm trying the following steps to get the correct OFFERS:
    ...........
    Expression qual = ExpressionFactory.binaryDbPathExp(Expression.EQUAL_TO,
                                                        "GLOBAL",
                                                        "J");

    qual = qual.orExp (ExpressionFactory.binaryPathExp(Expression.EQUAL_TO,
                                                       "isInUsergroup.hasFirm",
                                                       loggedUser.getIsInFirm
    ())); // where loggedUser is a formerly read user-Object and "getIsInFirm
    ()" returns a Firm-Object

    qual = qual.andExp (ExpressionFactory.binaryPathExp (Expression.EQUAL_TO,
                                                         "status",
                                                         "on"));

    SelectQuery queryOffers = new SelectQuery(TbOffers.class, qual);

    queryOffers.setLoggingLevel(Level.WARN);
    queryOffers.setPageSize (nPageSize);

    List offers = ctxt.performQuery(queryOffers);
    .............

    The resulting sql-statement looks like this:
    SELECT DISTINCT t0.* FROM tb_offers t0,
                              tb_offer_usergroup_assoc t1,
                              tb_usergroup t2,
                              tb_usergroup_firm_assoc t3,
                              tb_firm t4
    WHERE t0.ID = t1.OFFER_ID AND
          t1.USERGROUP_ID = t2.ID AND
          t2.ID = t3.USERGROUP_ID AND
          t3.FIRM_ID = t4.ID AND
          (((t0.GLOBAL = 'J') OR (t3.FIRM_ID = 820)) AND (t0.STATUS = 'on'))

    (rem: I shortened the listing of all t0-attributes to t0.*)

    But this query doesn't do the right thing for me. I need the following
    statement:
    SELECT DISTINCT t0.* FROM tb_offers t0,
                              tb_offer_usergroup_assoc t1,
                              tb_usergroup t2,
                              tb_usergroup_firm_assoc t3,
                              tb_firm t4
    WHERE (((t0.GLOBAL = 'J') OR
             (t0.ID = t1.OFFER_ID AND t1.USERGROUP_ID = t2.ID AND
            t2.ID = t3.USERGROUP_ID AND
            t3.FIRM_ID = t4.ID AND t3.FIRM_ID = 820)) AND
           (t0.STATUS = 'on'))

    I tried this in a mysql-frontend and this one gives me the right results.

    How can I change the Cayenne-Statements to act like the right query?

    Thanks a lot for any help!

    Michael

    This archive was generated by hypermail 2.0.0 : Sun Feb 16 2003 - 05:29:19 EST