Thanks, Ray, this is what I was wondering. I didn't know if the Fine Arts schema had a Work Items procedure as well as a Works authority. Here at Berkeley we are using the Works authority so we can just use the refname, and in fact we just extract out the display name rather than join to the authority table. Thanks, Chris Jun 22, 2016, at 8:54 PM, Ray Lee <rhlee@berkeley.edu> wrote: Watch out, works are authority records (not procedural records), so they're not related to collection objects in the way that procedural records are related. There's no join through relations_common. Instead a collection object will contain a field whose value is the refname of a work. You can join to works_common or collectionspace_core through the refname column, then join to other tables using the id column. The trick is that when you compare refnames, you should compare only the part that does not include the display name. I'm sure someone on the list has some sample SQL for this. Ray On Wed, Jun 22, 2016 at 8:30 PM, Jesse Martinez <mjesse@gmail.com> wrote: > I've used the following SQL pattern in a few reports: (replace > groups_common and collectionobjects_common with their respective related > procedures) > > FROM groups_common gc > > JOIN hierarchy h1 ON (gc.id=h1.id) > JOIN relations_common rc1 ON (h1.name=rc1.subjectcsid) > > JOIN hierarchy h2 ON (rc1.objectcsid=h2.name) > JOIN collectionobjects_common co ON (h2.id=co.id) > > ... > > INNER JOIN misc ON (misc.id = co.id AND misc.lifecyclestate <> 'deleted') > INNER JOIN collectionspace_core core ON (core.id = co.id) > INNER JOIN collectionobjects_TENANT ctenant ON (co.id = ctenant.id) > > > Jesse > > On Wed, Jun 22, 2016 at 11:12 PM, Yousuf Nejati < > yousuf.nejati@granitehorizon.com> wrote: > >> Hey, >> >> Thanks for being so responsive, we really appreciate it. >> >> I've discovered how difficult this is through speaking with Lowe, and >> first hand. We are using the core schema. >> >> Here is the broken SQL fragment, where coc is collectionobjects_common. >> >> LEFT OUTER JOIN hierarchy h24 ON (h24.id=coc.id) >> LEFT OUTER JOIN relations_common r2 ON (r2.subjectcsid=h24.name AND >> r2.subjectdocumenttype='CollectionObject') >> LEFT OUTER JOIN hierarchy h25 ON (h25.name=r2.objectcsid AND >> r2.objectdocumenttype='Workitem') >> LEFT OUTER JOIN works_common wc ON (h25.name=wc.id); >> >> Any ideas? >> >> -Y >> >> On Wed, Jun 22, 2016 at 7:52 PM, Christopher R. HOFFMAN < >> chris_h@berkeley.edu> wrote: >> >>> Hi Yousuf, >>> >>> These queries are notoriously challenging given the underlying structure >>> of Nuxeo. Are you using the core scheme or the Fine Arts extension, and >>> have you customized how related works are represented? >>> >>> I don't think we're using works in the standard way here at Berkeley so >>> we probably can't give you the exact SQL fragment, but hopefully we can >>> help with the overall pattern. I'll look at this a bit tomorrow if we don't >>> see more traffic on the list. >>> >>> Regards, >>> Chris >>> >>> >>> On Jun 22, 2016, at 6:02 PM, Yousuf Nejati < >>> yousuf.nejati@granitehorizon.com> wrote: >>> >>> Hi, >>> >>> Thanks for your response. >>> >>> I believe what we are really trying to understand is how the >>> CollectionSpace platform ui derives from the Postgres database to provide >>> things such as collection object and work relations, etc. >>> >>> Does that make sense? We've deployed the UCB webapp and are currently >>> extending it to meet our needs. Implementing a custom query to index Solr >>> has been quite frustrating, to say the least. >>> >>> For example, we currently wish to obtain all of the Workitems related to >>> a CollectionObject. We are so very close. >>> >>> -Yousuf >>> On Jun 22, 2016 5:54 PM, "Richard Millet" <richard.millet@lyrasis.org> >>> wrote: >>> >>>> Ray, >>>> >>>> Well, I wish they were obsolete but we're not quite there yet. There >>>> are still places in the code (Services layer) that rely on those classes. >>>> The "common" schemas/classes don't change (haven't changed?) so we've not >>>> encountered any problems. >>>> >>>> -Richard >>>> >>>> ________________________________________ >>>> From: Ray Lee <rhlee@berkeley.edu> >>>> Sent: Wednesday, June 22, 2016 5:41 PM >>>> To: Richard Millet >>>> Cc: Peter Tucker; talk@lists.collectionspace.org; Yousuf Nejati >>>> Subject: Re: [Talk] Searching for complete source code >>>> @org.collectionspace.services.movement.MovementsCommon >>>> >>>> Richard, >>>> I think those JAXB classes are obsolete now, aren't they? The .xsd >>>> files for records are now generated from app layer config, but the JAXB >>>> classes are not generated from those generated .xsd files. I think they're >>>> still using old static .xsd files, which may not be up to date with what's >>>> defined in the app layer now. >>>> >>>> Ray >>>> >>>> >>>> On Wed, Jun 22, 2016 at 5:28 PM, Richard Millet < >>>> richard.millet@lyrasis.org<mailto:richard.millet@lyrasis.org>> wrote: >>>> Peter, >>>> >>>> MovementsCommon.java is a JAX-B class generated from the XML Schema >>>> file movements-common.xsd. It is essentially just a way for converting XML >>>> to Java class instances and vice versa -see >>>> https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding for >>>> more details. >>>> >>>> -Richard >>>> >>>> ________________________________________ >>>> From: Talk <talk-bounces@lists.collectionspace.org<mailto: >>>> talk-bounces@lists.collectionspace.org>> on behalf of Peter Tucker < >>>> peter.tucker@granitehorizon.com<mailto:peter.tucker@granitehorizon.com >>>> >> >>>> Sent: Wednesday, June 22, 2016 5:03 PM >>>> To: talk@lists.collectionspace.org<mailto: >>>> talk@lists.collectionspace.org> >>>> Cc: Yousuf Nejati >>>> Subject: [Talk] Searching for complete source code >>>> @org.collectionspace.services.movement.MovementsCommon >>>> >>>> Hi everyone, >>>> >>>> We were curious about the code that is in MovementsCommon.java class , >>>> since we're doing some database queries that's related to movements >>>> records and wanted to understand the process more. However currently in >>>> the CS github, we can't find the source code of the specific class. >>>> >>>> Thank you, >>>> Peter >>>> >>>> _______________________________________________ >>>> Talk mailing list >>>> Talk@lists.collectionspace.org<mailto:Talk@lists.collectionspace.org> >>>> >>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>>> >>>> _______________________________________________ >>>> Talk mailing list >>>> Talk@lists.collectionspace.org<mailto:Talk@lists.collectionspace.org> >>>> >>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>>> >>>> _______________________________________________ >>> Talk mailing list >>> Talk@lists.collectionspace.org >>> >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>> >>> >> >> >> -- >> Yousuf Nejati >> * Assistant Developer* >> Granite Horizon >> Web Design and Development | Enterprise Content Management | IT Consulting >> >> yousuf.nejati@granitehorizon.com <greg@granitehorizon.com> >> granitehorizon.com >> (888) 354-6626 x786 <%28888%29%20354-6626%20x730> | (916) 647-6350 x786 >> <%28916%29%20647-6350%20x730> | (866) 867-7126 (fax) >> 8153 Elk Grove Boulevard Suite 20 >> Elk Grove CA 95758-5965 >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> >> > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org > > _______________________________________________ Talk mailing list Talk@lists.collectionspace.org http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

Ray and Chris, I'm still having a hard time understanding how, or better yet where, exactly a collection object references a work authority? It doesn't seem to be in relations_common, or is it? "Instead a collection object will contain a field whose value is the refname of a work.'-Ray Ray, are you suggesting to join the relations_common ref column with the collectionspace_core or works_common ref column, leaving me with a list of works? Chris, I've been doing this in the SELECT statement to extract out the display name from the refname: split_part(cscore.refname,'''',2) AS works_s, What could I provide you to make this easier for everyone? Thanks, -Yousuf On Thu, Jun 23, 2016 at 7:47 AM, Christopher R. HOFFMAN < chris_h@berkeley.edu> wrote: > Thanks, Ray, this is what I was wondering. I didn't know if the Fine Arts > schema had a Work Items procedure as well as a Works authority. > > Here at Berkeley we are using the Works authority so we can just use the > refname, and in fact we just extract out the display name rather than join > to the authority table. > > Thanks, > Chris > > Jun 22, 2016, at 8:54 PM, Ray Lee <rhlee@berkeley.edu> wrote: > > Watch out, works are authority records (not procedural records), so > they're not related to collection objects in the way that procedural > records are related. There's no join through relations_common. Instead a > collection object will contain a field whose value is the refname of a > work. You can join to works_common or collectionspace_core through the > refname column, then join to other tables using the id column. The trick is > that when you compare refnames, you should compare only the part that does > not include the display name. I'm sure someone on the list has some sample > SQL for this. > > Ray > > > On Wed, Jun 22, 2016 at 8:30 PM, Jesse Martinez <mjesse@gmail.com> wrote: > >> I've used the following SQL pattern in a few reports: (replace >> groups_common and collectionobjects_common with their respective related >> procedures) >> >> FROM groups_common gc >> >> JOIN hierarchy h1 ON (gc.id=h1.id) >> JOIN relations_common rc1 ON (h1.name=rc1.subjectcsid) >> >> JOIN hierarchy h2 ON (rc1.objectcsid=h2.name) >> JOIN collectionobjects_common co ON (h2.id=co.id) >> >> ... >> >> INNER JOIN misc ON (misc.id = co.id AND misc.lifecyclestate <> 'deleted') >> INNER JOIN collectionspace_core core ON (core.id = co.id) >> INNER JOIN collectionobjects_TENANT ctenant ON (co.id = ctenant.id) >> >> >> Jesse >> >> On Wed, Jun 22, 2016 at 11:12 PM, Yousuf Nejati < >> yousuf.nejati@granitehorizon.com> wrote: >> >>> Hey, >>> >>> Thanks for being so responsive, we really appreciate it. >>> >>> I've discovered how difficult this is through speaking with Lowe, and >>> first hand. We are using the core schema. >>> >>> Here is the broken SQL fragment, where coc is collectionobjects_common. >>> >>> LEFT OUTER JOIN hierarchy h24 ON (h24.id=coc.id) >>> LEFT OUTER JOIN relations_common r2 ON (r2.subjectcsid=h24.name AND >>> r2.subjectdocumenttype='CollectionObject') >>> LEFT OUTER JOIN hierarchy h25 ON (h25.name=r2.objectcsid AND >>> r2.objectdocumenttype='Workitem') >>> LEFT OUTER JOIN works_common wc ON (h25.name=wc.id); >>> >>> Any ideas? >>> >>> -Y >>> >>> On Wed, Jun 22, 2016 at 7:52 PM, Christopher R. HOFFMAN < >>> chris_h@berkeley.edu> wrote: >>> >>>> Hi Yousuf, >>>> >>>> These queries are notoriously challenging given the underlying >>>> structure of Nuxeo. Are you using the core scheme or the Fine Arts >>>> extension, and have you customized how related works are represented? >>>> >>>> I don't think we're using works in the standard way here at Berkeley so >>>> we probably can't give you the exact SQL fragment, but hopefully we can >>>> help with the overall pattern. I'll look at this a bit tomorrow if we don't >>>> see more traffic on the list. >>>> >>>> Regards, >>>> Chris >>>> >>>> >>>> On Jun 22, 2016, at 6:02 PM, Yousuf Nejati < >>>> yousuf.nejati@granitehorizon.com> wrote: >>>> >>>> Hi, >>>> >>>> Thanks for your response. >>>> >>>> I believe what we are really trying to understand is how the >>>> CollectionSpace platform ui derives from the Postgres database to provide >>>> things such as collection object and work relations, etc. >>>> >>>> Does that make sense? We've deployed the UCB webapp and are currently >>>> extending it to meet our needs. Implementing a custom query to index Solr >>>> has been quite frustrating, to say the least. >>>> >>>> For example, we currently wish to obtain all of the Workitems related >>>> to a CollectionObject. We are so very close. >>>> >>>> -Yousuf >>>> On Jun 22, 2016 5:54 PM, "Richard Millet" <richard.millet@lyrasis.org> >>>> wrote: >>>> >>>>> Ray, >>>>> >>>>> Well, I wish they were obsolete but we're not quite there yet. There >>>>> are still places in the code (Services layer) that rely on those classes. >>>>> The "common" schemas/classes don't change (haven't changed?) so we've not >>>>> encountered any problems. >>>>> >>>>> -Richard >>>>> >>>>> ________________________________________ >>>>> From: Ray Lee <rhlee@berkeley.edu> >>>>> Sent: Wednesday, June 22, 2016 5:41 PM >>>>> To: Richard Millet >>>>> Cc: Peter Tucker; talk@lists.collectionspace.org; Yousuf Nejati >>>>> Subject: Re: [Talk] Searching for complete source code >>>>> @org.collectionspace.services.movement.MovementsCommon >>>>> >>>>> Richard, >>>>> I think those JAXB classes are obsolete now, aren't they? The .xsd >>>>> files for records are now generated from app layer config, but the JAXB >>>>> classes are not generated from those generated .xsd files. I think they're >>>>> still using old static .xsd files, which may not be up to date with what's >>>>> defined in the app layer now. >>>>> >>>>> Ray >>>>> >>>>> >>>>> On Wed, Jun 22, 2016 at 5:28 PM, Richard Millet < >>>>> richard.millet@lyrasis.org<mailto:richard.millet@lyrasis.org>> wrote: >>>>> Peter, >>>>> >>>>> MovementsCommon.java is a JAX-B class generated from the XML Schema >>>>> file movements-common.xsd. It is essentially just a way for converting XML >>>>> to Java class instances and vice versa -see >>>>> https://en.wikipedia.org/wiki/Java_Architecture_for_XML_Binding for >>>>> more details. >>>>> >>>>> -Richard >>>>> >>>>> ________________________________________ >>>>> From: Talk <talk-bounces@lists.collectionspace.org<mailto: >>>>> talk-bounces@lists.collectionspace.org>> on behalf of Peter Tucker < >>>>> peter.tucker@granitehorizon.com<mailto:peter.tucker@granitehorizon.com >>>>> >> >>>>> Sent: Wednesday, June 22, 2016 5:03 PM >>>>> To: talk@lists.collectionspace.org<mailto: >>>>> talk@lists.collectionspace.org> >>>>> Cc: Yousuf Nejati >>>>> Subject: [Talk] Searching for complete source code >>>>> @org.collectionspace.services.movement.MovementsCommon >>>>> >>>>> Hi everyone, >>>>> >>>>> We were curious about the code that is in MovementsCommon.java class , >>>>> since we're doing some database queries that's related to movements >>>>> records and wanted to understand the process more. However currently in >>>>> the CS github, we can't find the source code of the specific class. >>>>> >>>>> Thank you, >>>>> Peter >>>>> >>>>> _______________________________________________ >>>>> Talk mailing list >>>>> Talk@lists.collectionspace.org<mailto:Talk@lists.collectionspace.org> >>>>> >>>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>>>> >>>>> _______________________________________________ >>>>> Talk mailing list >>>>> Talk@lists.collectionspace.org<mailto:Talk@lists.collectionspace.org> >>>>> >>>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>>>> >>>>> _______________________________________________ >>>> Talk mailing list >>>> Talk@lists.collectionspace.org >>>> >>>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>>> >>>> >>> >>> >>> -- >>> Yousuf Nejati >>> * Assistant Developer* >>> Granite Horizon >>> Web Design and Development | Enterprise Content Management | IT >>> Consulting >>> >>> yousuf.nejati@granitehorizon.com <greg@granitehorizon.com> >>> granitehorizon.com >>> (888) 354-6626 x786 <%28888%29%20354-6626%20x730> | (916) 647-6350 x786 >>> <%28916%29%20647-6350%20x730> | (866) 867-7126 (fax) >>> 8153 Elk Grove Boulevard Suite 20 >>> Elk Grove CA 95758-5965 >>> >>> _______________________________________________ >>> Talk mailing list >>> Talk@lists.collectionspace.org >>> >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>> >>> >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> >> > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org > > -- Yousuf Nejati * Assistant Developer* Granite Horizon Web Design and Development | Enterprise Content Management | IT Consulting yousuf.nejati@granitehorizon.com <greg@granitehorizon.com> granitehorizon.com (888) 354-6626 x786 <%28888%29%20354-6626%20x730> | (916) 647-6350 x786 <%28916%29%20647-6350%20x730> | (866) 867-7126 (fax) 8153 Elk Grove Boulevard Suite 20 Elk Grove CA 95758-5965