Re: ERXExistsQualifier and compound keys in t0
Paul Hoadley <[email protected]> Thu, 5 Mar 2015 12:59:38 +1030
| Newsgroups | gmane.comp.web.webobjects.wonder-disc |
|---|---|
| Message-ID | <[email protected]> |
--===============4271850344715072860== Content-Type: multipart/alternative; boundary="Apple-Mail=_357CFB32-8B1B-448D-9847-3FB245AF444F" --Apple-Mail=_357CFB32-8B1B-448D-9847-3FB245AF444F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Hi Larry, On 5 Mar 2015, at 12:34 pm, Larry Mills-Gahl <[email protected]> wrote: > I=92m having a bit of difficulty with an ERXExistsQualifier and I=92m = wondering if it isn=92t related to a compound key in the first table. = The goal is to find unique rows in patientrole that have related rows in = the encounter table. That=92s a job for the Exists qualifier=85 (right = Aaron?) >=20 > The problem is that the last portion of the exists clause (where the = docs say: ...=93 and t1.FIRST_TABLE_ID =3D t0.ID)=94 is selecting the = wrong fields to make that join. >=20 > The two tables in question are: > patientrole (centerkey,patientkey,mrn) So the compound key here is (centerkey, patientkey)? (Does the = patientkey column correspond to the personKey property below?) > encounter(centerkey, patientkey, etc=85) >=20 > The qualifier code is: >=20 > EOQualifier centerQ =3D = DialysisTreatment.CENTERKEY.is(c.centerkey()); > EOQualifier rangeQ =3D new = ERXAndQualifier(DialysisTreatment.START_TIME.after(start), > DialysisTreatment.START_TIME.before(end)); >=20 > EOQualifier roleQ =3D new ERXExistsQualifier(new = ERXAndQualifier(centerQ, > rangeQ), PatientRole.DIALYSIS_TREATMENTS_KEY); >=20 > The SQL output from this is: >=20 > SELECT t0.centerKey, RTRIM(t0.mrn), t0.personKey=20 > FROM patientrole t0=20 > WHERE EXISTS (=20 > SELECT exists0.encounterKey=20 > FROM encounter exists0=20 > WHERE (exists0.centerKey =3D ?=20 > AND (exists0.startTime > ? AND exists0.startTime < ?) > )=20 > AND exists0.centerKey =3D t0.personKey # THIS IS THE PROBLEM = LINE > )=20 >=20 > The SQL I=92m looking for ends with : > AND exists0.centerKey =3D t0.centerKey >=20 > Am I on the right track or should I accomplish this through other = means? I don't think a compound key will prevent ERXExistsQualifier from = generating the appropriate SQL (unless you ask it to use an IN clause = instead of EXISTS, which can't be done). I've certainly done it myself = recently on just such a join entity with a compound key. It's = definitely annotated in the model as a compound key? --=20 Paul Hoadley http://logicsquad.net/ --Apple-Mail=_357CFB32-8B1B-448D-9847-3FB245AF444F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252 <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html = charset=3Dwindows-1252"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">Hi = Larry,<div><br><div><div>On 5 Mar 2015, at 12:34 pm, Larry Mills-Gahl = <<a href=3D"mailto:[email protected]">[email protected]</a>> = wrote:</div><br class=3D"Apple-interchange-newline"><blockquote = type=3D"cite"><meta http-equiv=3D"Content-Type" content=3D"text/html = charset=3Dutf-8"><div style=3D"word-wrap: break-word; -webkit-nbsp-mode: = space; -webkit-line-break: after-white-space;" class=3D""><div = class=3D"">I=92m having a bit of difficulty with an ERXExistsQualifier = and I=92m wondering if it isn=92t related to a compound key in the first = table. The goal is to find unique rows in patientrole that have related = rows in the encounter table. That=92s a job for the Exists qualifier=85 = (right Aaron?)</div><div class=3D""><br class=3D""></div><div = style=3D"widows: 1;" class=3D"">The problem is that the last portion of = the exists clause (where the docs say: ...=93 <span = style=3D"widows: 1; background-color: rgb(255, 255, 255);" = class=3D""><font face=3D"monospace" size=3D"3" class=3D"">and = t1.FIRST_TABLE_ID =3D t0.ID)=94 is selecting the wrong fields to make = that join.</font></span></div><div style=3D"widows: 1;" class=3D""><span = style=3D"widows: 1; background-color: rgb(255, 255, 255);" = class=3D""><font face=3D"monospace" size=3D"3" class=3D""><br = class=3D""></font></span></div><div style=3D"widows: 1;" class=3D""><font = face=3D"monospace" size=3D"3" class=3D""><span style=3D"background-color: = rgb(255, 255, 255);" class=3D"">The two tables in question = are:</span></font></div><div style=3D"widows: 1;" class=3D""><font = face=3D"monospace" size=3D"3" class=3D""><span style=3D"background-color: = rgb(255, 255, 255);" class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span>patientrole = (centerkey,patientkey,mrn)</span></font></div></div></blockquote><div><br>= </div><div>So the compound key here is (centerkey, patientkey)? = (Does the patientkey column correspond to the personKey property = below?)</div><br><blockquote type=3D"cite"><div style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; -webkit-line-break: = after-white-space;" class=3D""><div style=3D"widows: 1;" class=3D""><font = face=3D"monospace" size=3D"3" class=3D""><span style=3D"background-color: = rgb(255, 255, 255);" class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> = </span>encounter(</span>centerkey, patientkey, = etc=85)</font></div><div style=3D"widows: 1;" class=3D""><font = face=3D"monospace" size=3D"3" class=3D""><br class=3D""></font></div><div = style=3D"widows: 1;" class=3D""><font face=3D"monospace" size=3D"3" = class=3D"">The qualifier code is:</font></div><div style=3D"widows: = 1;" class=3D""><font face=3D"monospace" size=3D"3" class=3D""><br = class=3D""></font></div><div style=3D"widows: 1;" class=3D""><div = style=3D"margin: 0px; font-size: 11px; font-family: Monaco; color: = rgb(120, 141, 147);" class=3D""> = EOQualifier <span style=3D"color: #e5493d" = class=3D"">centerQ</span> =3D DialysisTreatment.CENTERKEY.<span = style=3D"color: #2c9edb" class=3D"">is</span>(<span style=3D"color: = #7f87cf" class=3D"">c</span>.centerkey());</div><div style=3D"margin: = 0px; font-size: 11px; font-family: Monaco; color: rgb(120, 141, 147);" = class=3D""> EOQualifier <span style=3D"color: = #e5493d" class=3D"">rangeQ</span> =3D <span style=3D"color: #788e95" = class=3D"">new</span> <span style=3D"color: #2c9edb" = class=3D"">ERXAndQualifier</span>(DialysisTreatment.START_TIME.<span = style=3D"color: #2c9edb" class=3D"">after</span>(<span style=3D"color: = #7f87cf" class=3D"">start</span>),</div><div style=3D"margin: 0px; = font-size: 11px; font-family: Monaco; color: rgb(120, 141, 147);" = class=3D""> = DialysisTreatment.START_TIME.<span style=3D"color: #2c9edb" = class=3D"">before</span>(<span style=3D"color: #7f87cf" = class=3D"">end</span>));</div><div style=3D"margin: 0px; font-size: = 11px; font-family: Monaco; min-height: 15px;" class=3D""><br = class=3D""></div><div style=3D"margin: 0px; font-size: 11px; = font-family: Monaco; color: rgb(44, 158, 219);" class=3D""><span = style=3D"color: #788d93" class=3D""> = EOQualifier </span><span style=3D"color: #e5493d" = class=3D"">roleQ</span><span style=3D"color: #788d93" class=3D""> =3D = </span><span style=3D"color: #788e95" class=3D"">new</span><span = style=3D"color: #788d93" class=3D""> </span>ERXExistsQualifier<span = style=3D"color: #788d93" class=3D"">(</span><span style=3D"color: = #788e95" class=3D"">new</span><span style=3D"color: #788d93" class=3D""> = </span>ERXAndQualifier<span style=3D"color: #788d93" = class=3D"">(</span><span style=3D"color: #e5493d" = class=3D"">centerQ</span><span style=3D"color: #788d93" = class=3D"">,</span></div><div style=3D"margin: 0px; font-size: 11px; = font-family: Monaco; color: rgb(120, 141, 147);" class=3D""> = <span style=3D"color: = #e5493d" class=3D"">rangeQ</span>), = PatientRole.DIALYSIS_TREATMENTS_KEY);</div></div><div style=3D"widows: = 1;" class=3D""><span style=3D"widows: 1; background-color: rgb(255, 255, = 255);" class=3D""><font face=3D"monospace" size=3D"3" class=3D""><br = class=3D""></font></span></div><div class=3D"">The SQL output from this = is:</div><div class=3D""><br class=3D""></div><div class=3D""><div = class=3D""><font face=3D"Monaco" class=3D"">SELECT t0.centerKey, = RTRIM(t0.mrn), t0.personKey </font></div><div class=3D""><font = face=3D"Monaco" class=3D"">FROM patientrole t0 </font></div><div = class=3D""><font face=3D"Monaco" class=3D"">WHERE EXISTS = ( </font></div><div class=3D""><font face=3D"Monaco" class=3D""><span= class=3D"Apple-tab-span" style=3D"white-space:pre"> </span>SELECT = exists0.encounterKey </font></div><div class=3D""><font = face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span>FROM encounter = exists0 </font></div><div class=3D""><font face=3D"Monaco" = class=3D""><span class=3D"Apple-tab-span" style=3D"white-space:pre"> = </span>WHERE (exists0.centerKey =3D ? </font></div><div = class=3D""><font face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span"= style=3D"white-space:pre"> </span>AND (exists0.startTime = > ? AND exists0.startTime < ?)</font></div><div class=3D""><font = face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span>) </font></div><div = class=3D""><font face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span"= style=3D"white-space:pre"> </span>AND exists0.centerKey =3D = t0.personKey # THIS IS THE PROBLEM LINE</font></div><div class=3D""><font = face=3D"Monaco" class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span>) </font></div></div><div = class=3D""><br class=3D""></div><div class=3D"">The SQL I=92m looking = for ends with :</div><div class=3D""><span style=3D"font-family: = Monaco;" class=3D""><span class=3D"Apple-tab-span" = style=3D"white-space:pre"> </span>AND exists0.centerKey =3D = t0.centerKey</span></div><br class=3D""><div class=3D"">Am I on the = right track or should I accomplish this through other = means?</div></div></blockquote><div><br></div><div>I don't think a = compound key will prevent ERXExistsQualifier from generating the = appropriate SQL (unless you ask it to use an IN clause instead of = EXISTS, which can't be done). I've certainly done it myself = recently on just such a join entity with a compound key. It's = definitely annotated in the model as a compound = key?</div><div><br></div><div><br></div></div><div = apple-content-edited=3D"true"> <span class=3D"Apple-style-span" style=3D"border-collapse: separate; = border-spacing: 0px;"><span class=3D"Apple-style-span" = style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: = 'Lucida Grande'; font-style: normal; font-variant: normal; font-weight: = normal; letter-spacing: normal; line-height: normal; orphans: 2; = text-indent: 0px; text-transform: none; white-space: normal; widows: 2; = word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; = -webkit-border-vertical-spacing: 0px; = -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: = auto; -webkit-text-stroke-width: 0px; "><div style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; -webkit-line-break: = after-white-space; "><span class=3D"Apple-style-span" = style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: = 'Lucida Grande'; font-style: normal; font-variant: normal; font-weight: = normal; letter-spacing: normal; line-height: normal; orphans: 2; = text-indent: 0px; text-transform: none; white-space: normal; widows: 2; = word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; = -webkit-border-vertical-spacing: 0px; = -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: = auto; -webkit-text-stroke-width: 0px; "><div style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; -webkit-line-break: = after-white-space; ">-- <br>Paul Hoadley<br><a = href=3D"http://logicsquad.net/">http://logicsquad.net/</a><br><br></div></= span></div></span></span> </div> <br></div></body></html>= --Apple-Mail=_357CFB32-8B1B-448D-9847-3FB245AF444F-- --===============4271850344715072860== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline ------------------------------------------------------------------------------ Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ --===============4271850344715072860== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Wonder-disc mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/wonder-disc --===============4271850344715072860==--