Re: How do I query a joined table in cayenne?

From: Frank (farocc..otmail.com)
Date: Tue Feb 27 2007 - 11:42:58 EST

  • Next message: Mike Kienenberger: "Re: How do I query a joined table in cayenne?"

    ok,

    Thanks Mike...

    Frank
    ----- Original Message -----
    From: "Mike Kienenberger" <mkienen..mail.com>
    To: <use..ayenne.apache.org>
    Sent: Tuesday, February 27, 2007 11:39 AM
    Subject: Re: How do I query a joined table in cayenne?

    > Sorry, you'll have to ask on the Click mailing list for click-specific
    > issues.
    >
    > The code I sent you will work in a generic Cayenne environment. Try
    > it out and see.
    > It's only a couple more lines of code to perform query once you have
    > the expression built.
    >
    > On 2/27/07, Frank <farocc..otmail.com> wrote:
    >> In Click, this should map to the department name.
    >> departments.pcldds
    >>
    >> I would load the table column like this
    >> column = new Column("departments.pcldds")
    >> I think Click would translate this to
    >> getDepartments().getPcldds()
    >>
    >> I tried various combinations and nothing seems to work.
    >> My query select does not show a join
    >> here is the output:
    >>
    >> INFO QueryLogger: SELECT t0.EMEMP#, t0.EMWPH#, t0.EMYFNM, t0.EMYLNM,
    >> t0.EMHSP#, t0.EMDEP4 FROM BSYDTAA.BSYPEMP t0 WHERE (t0.EMHSP# IN (?, ?))
    >> AND
    >> (UPPER(RTRIM(t0.EMYLNM)) LIKE UPPER(CAST (? AS VARCHAR(25)))) AND
    >> (UPPER(RTRIM(t0.EMYFNM)) LIKE UPPER(CAST (? AS VARCHAR(15)))) [bind: 1,
    >> 5,
    >> '%', '%'] - prepared in 31 ms.
    >>
    >> Frank
    >>
    >> ----- Original Message -----
    >> From: "Mike Kienenberger" <mkienen..mail.com>
    >> To: <use..ayenne.apache.org>
    >> Sent: Tuesday, February 27, 2007 10:58 AM
    >> Subject: Re: How do I query a joined table in cayenne?
    >>
    >>
    >> >I don't understand what
    >> >
    >> > column = new Column("toDepartments().getPcldds()")
    >> >
    >> > is supposed to do.
    >> >
    >> > Also, my examples may not exactly match your model. It looks like
    >> > your "toDepartments()" relationship is actually "departments()".
    >> > Adjust accordingly.
    >> >
    >> > public static final String DEPARTMENTS_PROPERTY = "departments";
    >> >
    >> >
    >> > On 2/27/07, Frank <farocc..otmail.com> wrote:
    >> >> I still get an error
    >> >> Is this right?
    >> >>
    >> >> No matching getter method found for property toDepartments() on class
    >> >> stemc.cayenne.as400.Bsypemp
    >> >> column = new Column("toDepartments().getPcldds()");
    >> >>
    >> >> Frank
    >> >> ----- Original Message -----
    >> >> From: "Mike Kienenberger" <mkienen..mail.com>
    >> >> To: <use..ayenne.apache.org>
    >> >> Sent: Tuesday, February 27, 2007 10:44 AM
    >> >> Subject: Re: How do I query a joined table in cayenne?
    >> >>
    >> >>
    >> >> > You'd show in the table:
    >> >> >
    >> >> > getFirstName()
    >> >> > getLastName()
    >> >> > toDepartment().getDeptName()
    >> >> >
    >> >> > You would search on:
    >> >> >
    >> >> > andExp() of the following:
    >> >> >
    >> >> > ExpressionFactory.matchExp(Bsypemp.FIRST_NAME_PROPERTY, firstName)
    >> >> >
    >> >> > ExpressionFactory.matchExp(Bsypemp.LAST_NAME_PROPERTY, lastName)
    >> >> >
    >> >> > ExpressionFactory.matchExp(Bsypemp.TO_DEPARTMENT_PROPERTY + "." +
    >> >> > bprppcp.DEPT_NAME_PROPERTY, deptName)
    >> >> >
    >> >> >
    >> >> > On 2/27/07, Frank <farocc..otmail.com> wrote:
    >> >> >> If I have two files joined:
    >> >> >> bsypemp
    >> >> >> firstName
    >> >> >> LastName
    >> >> >> deptno
    >> >> >> bprppcp
    >> >> >> deptno
    >> >> >> deptName
    >> >> >>
    >> >> >> I want to list in my table:
    >> >> >> firstName
    >> >> >> LastName
    >> >> >> deptName
    >> >> >>
    >> >> >> I want to search on
    >> >> >> firstName
    >> >> >> LastName
    >> >> >> deptName
    >> >> >>
    >> >> >> as long as I do not reference the deptName, my table loads fine.
    >> >> >> What am I doing wrong?
    >> >> >>
    >> >> >> Regards,
    >> >> >> Frank
    >> >> >> ----- Original Message -----
    >> >> >> From: "Mike Kienenberger" <mkienen..mail.com>
    >> >> >> To: <use..ayenne.apache.org>
    >> >> >> Sent: Tuesday, February 27, 2007 10:29 AM
    >> >> >> Subject: Re: How do I query a joined table in cayenne?
    >> >> >>
    >> >> >>
    >> >> >> > Are you just trying to find an object by the property on a
    >> >> >> > related
    >> >> >> > object?
    >> >> >> >
    >> >> >> > ExpressionFactory.matchExp(Contact.PRIMARY_CONTACT_NAME_PROPERTY
    >> >> >> > +
    >> >> >> > "."
    >> >> >> > + ContactName.FIRST_NAME_PROPERTY, firstName)
    >> >> >> >
    >> >> >> > For example, if you're trying to find an employee by department
    >> >> >> > name,
    >> >> >> >
    >> >> >> > ExpressionFactory.matchExp(Employee.DEPARTMENT_PROPERTY + "." +
    >> >> >> > Department.NAME_PROPERTY, targetDepartmentName)
    >> >> >> >
    >> >> >> > You can chain together as many relationships as you like, but be
    >> >> >> > aware
    >> >> >> > of potential outer join issues if there's any chance that one of
    >> >> >> > the
    >> >> >> > relationships may be null.
    >> >> >> >
    >> >> >> > For example, here's a fun one that operates on two different
    >> >> >> > kinds
    >> >> >> > of
    >> >> >> > entities, with parts of the expression path being built
    >> >> >> > conditionally:
    >> >> >> >
    >> >> >> > ===========================================================
    >> >> >> > String pathToFeePrefix;
    >> >> >> > String pathToFeePaymentHistoryPrefix;
    >> >> >> > Class searchEntityClass;
    >> >> >> > if (searchByFeeInsteadOfFeePaymentReceipt)
    >> >> >> > {
    >> >> >> > pathToFeePrefix = "";
    >> >> >> > pathToFeePaymentHistoryPrefix =
    >> >> >> > Fee.FEE_PAYMENT_RECEIPT_LIST_PROPERTY + ".";
    >> >> >> > searchEntityClass =
    >> >> >> > com.gvea.eng_work_mgmt.entity.cayenne.Fee.class;
    >> >> >> > }
    >> >> >> > else
    >> >> >> > {
    >> >> >> > pathToFeePrefix = FeePaymentReceipt.FEE_PROPERTY + ".";
    >> >> >> > pathToFeePaymentHistoryPrefix = "";
    >> >> >> > searchEntityClass =
    >> >> >> > com.gvea.eng_work_mgmt.entity.cayenne.FeePaymentReceipt.class;
    >> >> >> > }
    >> >> >> >
    >> >> >> >
    >> >> >> > [... one example usage...]
    >> >> >> >
    >> >> >> >
    >> >> >> > oredExpressionList.add(ExpressionFactory.matchExp(pathToFeePrefix
    >> >> >> > +
    >> >> >> > Fee.FEE_CYCLE_PROPERTY + "."
    >> >> >> > + FeeCycle.INITIAL_AUTHORIZATION_DOCUMENT_PROPERTY
    >> >> >> > +
    >> >> >> > "."
    >> >> >> > +
    >> >> >> > AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY + "."
    >> >> >> > + PermitDocument.AGENCY_PROPERTY, agency));
    >> >> >> > ===========================================================
    >> >> >> >
    >> >> >> >
    >> >> >> > On 2/26/07, Frank <farocc..otmail.com> wrote:
    >> >> >> >> Hi Mike,
    >> >> >> >>
    >> >> >> >> Here is my orginal jdbc version of select
    >> >> >> >>
    >> >> >> >> String sql = "SELECT BSYDTAA.BSYPEMP.EMHSP# AS HSP,
    >> >> >> >> BSYDTAA.BSYPEMP.EMYLNM,
    >> >> >> >> BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4,
    >> >> >> >> BSYDTAC.BPRPPCP.PCLDDS,
    >> >> >> >> BSYDTAA.BSYPEMP.EMEMP# AS EMEMP, BSYDTAA.BSYPEMP.EMWPH# AS
    >> >> >> >> WPHONE,
    >> >> >> >> BSYDTAA.BSYPEMP.EMSCH, BSYDTAA.BSYPEMP.EMOTSH FROM
    >> >> >> >> BSYDTAA.BSYPEMP,
    >> >> >> >> BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP.EMHSP# =
    >> >> >> >> BSYDTAC.BPRPPCP.PCHSP#
    >> >> >> >> AND
    >> >> >> >> BSYDTAA.BSYPEMP.EMDEP4 = BSYDTAC.BPRPPCP.PCDEP4 AND
    >> >> >> >> (BSYDTAA.BSYPEMP.EMHSP#
    >> >> >> >> IN (1,5)) AND (BSYDTAA.BSYPEMP.EMDOT=0)";
    >> >> >> >> sql += " AND EMYLNM like ?";
    >> >> >> >> sql += " AND EMYFNM like ?";
    >> >> >> >> sql += " AND PCLDDS like ?";
    >> >> >> >> sql += " AND PCDEP4 like ?";
    >> >> >> >> sql += " ORDER BY EMYLNM";
    >> >> >> >>
    >> >> >> >> I can just do this:
    >> >> >> >> rs = pstmt.executeQuery();
    >> >> >> >> rs.getString("PCLDDS");
    >> >> >> >> to get the department name.
    >> >> >> >>
    >> >> >> >> In Cayenne I do not understand how to limit records by
    >> >> >> >> department
    >> >> >> >> name
    >> >> >> >> Integer[] hspArray = {new Integer(1), new Integer(5)};
    >> >> >> >> Expression e =
    >> >> >> >> ExpressionFactory.inDbExp(Bsypemp.EMHSPPOUND_PK_COLUMN,
    >> >> >> >> hspArray);
    >> >> >> >> e =
    >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYLNM_PROPERTY,
    >> >> >> >> lname
    >> >> >> >> +"%"));
    >> >> >> >> e =
    >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYFNM_PROPERTY,
    >> >> >> >> fname
    >> >> >> >> +"%"));
    >> >> >> >> // I'm lost here e =
    >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.class.DEPARTMENTS_PROPERTY,
    >> >> >> >> pcldds
    >> >> >> >> // +"%"));
    >> >> >> >>
    >> >> >> >> SelectQuery query = new SelectQuery(Bsypemp.class,e);
    >> >> >> >> List records = context.performQuery(query);
    >> >> >> >>
    >> >> >> >> Thanks
    >> >> >> >> Frank
    >> >> >> >> ----- Original Message -----
    >> >> >> >> From: "Mike Kienenberger" <mkienen..mail.com>
    >> >> >> >> To: <use..ayenne.apache.org>
    >> >> >> >> Sent: Friday, February 23, 2007 10:02 PM
    >> >> >> >> Subject: Re: How do I query a joined table in cayenne?
    >> >> >> >>
    >> >> >> >>
    >> >> >> >> >I don't follow what you're trying to do.
    >> >> >> >> > Can you give an example of what you expect the sql to look
    >> >> >> >> > like
    >> >> >> >> > when
    >> >> >> >> > you're done, and give some example data so I can understand
    >> >> >> >> > what
    >> >> >> >> > you're trying to do?
    >> >> >> >> >
    >> >> >> >> > On 2/23/07, Frank <farocc..otmail.com> wrote:
    >> >> >> >> >> Hello,
    >> >> >> >> >>
    >> >> >> >> >> I cannot figure out how to query the pcldds, pcdep4 fields
    >> >> >> >> >> What am I doing wrong?
    >> >> >> >> >>
    >> >> >> >> >> Thanks
    >> >> >> >> >>
    >> >> >> >> >> Frank
    >> >> >> >> >>
    >> >> >> >> >> ** Java Code Start
    >> >> >> >> >> DataContext context = DataContext.getThreadDataContext();
    >> >> >> >> >> Integer[] hspArray = {new Integer(1), new Integer(5)};
    >> >> >> >> >> Expression e =
    >> >> >> >> >> ExpressionFactory.inDbExp(Bsypemp.EMHSPPOUND_PK_COLUMN,
    >> >> >> >> >> hspArray);
    >> >> >> >> >> e =
    >> >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYLNM_PROPERTY,
    >> >> >> >> >> lname
    >> >> >> >> >> +"%"));
    >> >> >> >> >> e =
    >> >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.EMYFNM_PROPERTY,
    >> >> >> >> >> fname
    >> >> >> >> >> +"%"));
    >> >> >> >> >> // I'm lost here e =
    >> >> >> >> >> e.andExp(ExpressionFactory.likeIgnoreCaseExp(Bsypemp.class.DEPARTMENTS_PROPERTY,
    >> >> >> >> >> pcldds
    >> >> >> >> >> // +"%"));
    >> >> >> >> >>
    >> >> >> >> >> SelectQuery query = new SelectQuery(Bsypemp.class,e);
    >> >> >> >> >> List records = context.performQuery(query);
    >> >> >> >> >> ** Java Code End
    >> >> >> >> >>
    >> >> >> >> >> Here is my first class
    >> >> >> >> >> package stemc.cayenne.as400.auto;
    >> >> >> >> >>
    >> >> >> >> >> /** Class _Bsypemp was generated by Cayenne.
    >> >> >> >> >> * It is probably a good idea to avoid changing this class
    >> >> >> >> >> manually,
    >> >> >> >> >> * since it may be overwritten next time code is
    >> >> >> >> >> regenerated.
    >> >> >> >> >> * If you need to make any customizations, please use
    >> >> >> >> >> subclass.
    >> >> >> >> >> */
    >> >> >> >> >> public class _Bsypemp extends
    >> >> >> >> >> org.objectstyle.cayenne.CayenneDataObject {
    >> >> >> >> >>
    >> >> >> >> >> public static final String E_MEMPPOUND_PROPERTY =
    >> >> >> >> >> "eMEMPpound";
    >> >> >> >> >> public static final String E_MWPHPOUND_PROPERTY =
    >> >> >> >> >> "eMWPHpound";
    >> >> >> >> >> public static final String EMOTSH_PROPERTY = "emotsh";
    >> >> >> >> >> public static final String EMSCH_PROPERTY = "emsch";
    >> >> >> >> >> public static final String EMYFNM_PROPERTY = "emyfnm";
    >> >> >> >> >> public static final String EMYLNM_PROPERTY = "emylnm";
    >> >> >> >> >> public static final String DEPARTMENTS_PROPERTY =
    >> >> >> >> >> "departments";
    >> >> >> >> >>
    >> >> >> >> >> public static final String EMEMPPOUND_PK_COLUMN =
    >> >> >> >> >> "EMEMP#";
    >> >> >> >> >> public static final String EMHSPPOUND_PK_COLUMN =
    >> >> >> >> >> "EMHSP#";
    >> >> >> >> >>
    >> >> >> >> >> public void setEMEMPpound(java.math.BigDecimal
    >> >> >> >> >> eMEMPpound) {
    >> >> >> >> >> writeProperty("eMEMPpound", eMEMPpound);
    >> >> >> >> >> }
    >> >> >> >> >> public java.math.BigDecimal getEMEMPpound() {
    >> >> >> >> >> return
    >> >> >> >> >> (java.math.BigDecimal)readProperty("eMEMPpound");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setEMWPHpound(java.math.BigDecimal
    >> >> >> >> >> eMWPHpound) {
    >> >> >> >> >> writeProperty("eMWPHpound", eMWPHpound);
    >> >> >> >> >> }
    >> >> >> >> >> public java.math.BigDecimal getEMWPHpound() {
    >> >> >> >> >> return
    >> >> >> >> >> (java.math.BigDecimal)readProperty("eMWPHpound");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setEmotsh(String emotsh) {
    >> >> >> >> >> writeProperty("emotsh", emotsh);
    >> >> >> >> >> }
    >> >> >> >> >> public String getEmotsh() {
    >> >> >> >> >> return (String)readProperty("emotsh");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setEmsch(String emsch) {
    >> >> >> >> >> writeProperty("emsch", emsch);
    >> >> >> >> >> }
    >> >> >> >> >> public String getEmsch() {
    >> >> >> >> >> return (String)readProperty("emsch");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setEmyfnm(String emyfnm) {
    >> >> >> >> >> writeProperty("emyfnm", emyfnm);
    >> >> >> >> >> }
    >> >> >> >> >> public String getEmyfnm() {
    >> >> >> >> >> return (String)readProperty("emyfnm");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setEmylnm(String emylnm) {
    >> >> >> >> >> writeProperty("emylnm", emylnm);
    >> >> >> >> >> }
    >> >> >> >> >> public String getEmylnm() {
    >> >> >> >> >> return (String)readProperty("emylnm");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setDepartments(stemc.cayenne.as400.Bprppcp
    >> >> >> >> >> departments) {
    >> >> >> >> >> setToOneTarget("departments", departments, true);
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >> public stemc.cayenne.as400.Bprppcp getDepartments() {
    >> >> >> >> >> return
    >> >> >> >> >> (stemc.cayenne.as400.Bprppcp)readProperty("departments");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> Here is my second class
    >> >> >> >> >> package stemc.cayenne.as400.auto;
    >> >> >> >> >>
    >> >> >> >> >> /** Class _Bprppcp was generated by Cayenne.
    >> >> >> >> >> * It is probably a good idea to avoid changing this class
    >> >> >> >> >> manually,
    >> >> >> >> >> * since it may be overwritten next time code is
    >> >> >> >> >> regenerated.
    >> >> >> >> >> * If you need to make any customizations, please use
    >> >> >> >> >> subclass.
    >> >> >> >> >> */
    >> >> >> >> >> public class _Bprppcp extends
    >> >> >> >> >> org.objectstyle.cayenne.CayenneDataObject {
    >> >> >> >> >>
    >> >> >> >> >> public static final String PCDEP4_PROPERTY = "pcdep4";
    >> >> >> >> >> public static final String PCINA_PROPERTY = "pcina";
    >> >> >> >> >> public static final String PCLDDS_PROPERTY = "pcldds";
    >> >> >> >> >> public static final String PCSDDS_PROPERTY = "pcsdds";
    >> >> >> >> >>
    >> >> >> >> >> public static final String PCDEP4_PK_COLUMN = "PCDEP4";
    >> >> >> >> >> public static final String PCHSPPOUND_PK_COLUMN =
    >> >> >> >> >> "PCHSP#";
    >> >> >> >> >>
    >> >> >> >> >> public void setPcdep4(java.math.BigDecimal pcdep4) {
    >> >> >> >> >> writeProperty("pcdep4", pcdep4);
    >> >> >> >> >> }
    >> >> >> >> >> public java.math.BigDecimal getPcdep4() {
    >> >> >> >> >> return (java.math.BigDecimal)readProperty("pcdep4");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setPcina(String pcina) {
    >> >> >> >> >> writeProperty("pcina", pcina);
    >> >> >> >> >> }
    >> >> >> >> >> public String getPcina() {
    >> >> >> >> >> return (String)readProperty("pcina");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setPcldds(String pcldds) {
    >> >> >> >> >> writeProperty("pcldds", pcldds);
    >> >> >> >> >> }
    >> >> >> >> >> public String getPcldds() {
    >> >> >> >> >> return (String)readProperty("pcldds");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> public void setPcsdds(String pcsdds) {
    >> >> >> >> >> writeProperty("pcsdds", pcsdds);
    >> >> >> >> >> }
    >> >> >> >> >> public String getPcsdds() {
    >> >> >> >> >> return (String)readProperty("pcsdds");
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >> }
    >> >> >> >> >>
    >> >> >> >> >>
    >> >> >> >> >
    >> >> >> >>
    >> >> >> >>
    >> >> >> >
    >> >> >>
    >> >> >>
    >> >> >
    >> >>
    >> >>
    >> >
    >>
    >>
    >



    This archive was generated by hypermail 2.0.0 : Tue Feb 27 2007 - 11:43:36 EST