Re: Tr: Re: Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
Re: Tr: Re: Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
- Subject: Re: Tr: Re: Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
- From: Henrique Gomes <email@hidden>
- Date: Thu, 12 May 2011 16:43:01 +0100
Are you trying to restrict the results by filtering on a related table? Or just want to fetch data from more than one table?
Fetches in WO are always from ONE entity (table). EOF uses joins to FILTER, not to add data to the result.
If your entity A has a toMany relation to entity B, A will have a method A.b() that returns an NSArray<B>.
On initial fetch, the data for the B relation is not fetched, and EOF will fetch it from the db when the method is used, then caches the result.
To avoid lots of one row accesses to the db, you can instruct a fetchSpecification to prefetch all the 'B's when fetching from A.
Henrique Gomes
On May 12, 2011, at 4:07 PM, email@hidden wrote:
> Hi all,
>
> I could fix my problem of *Distinct* with this line:
>
> ds.fetchSpecification (). setUsesDistinct (true);
>
> Then I added to my ERXBatchingDisplayGroup
> fetchEtudiantViaUvDg.setDataSource (ds);
> the fetch is done so with disctint.
>
> Now I try to do a qualifier which is a join between *individu* and *etu_insc_adm*.
>
> I.e : SELECT * from individu t0, etu_insc_adm t1 WHERE t0.id = t1.etuId;
>
> How to set this qualifier : t0.id = t2.etuId ??
>
> Thanks
>
> ========================================
>
> Message du : 12/05/2011
> De : email@hidden
> A : "Chuck Hill" <email@hidden>, "WebObjects webobjects-dev" <email@hidden>
> Copie à :
> Sujet : Re: Re: Tr: Re: Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
>
>
> Hi Chuck,
>
> I change my mind and go to try ERXBatchingDisplayGroup with a qualifier I built (see below)
>
> I translate my fetch spec qualifier:
>
> nom like $nom
> or prenom like $prenom
> or indivId = $numero
> or etuInscrAdms.diplomec like $etuInscrAdms.diplomec
> or (etuInscrAdms.anUniv like $etuInscrAdms.anUniv and etuInscrAdms.prdUniv like $etuInscrAdms.prdUniv)
> or etuInscrAdms.niveauAdm = $etuInscrAdms.niveauAdm
> or (etuInscrAdms.eoVueInsuvs.uvc = $etuInscrAdms.eoVueInsuvs.uvc
> and etuInscrAdms.eoVueInsuvs.anUniv = $etuInscrAdms.eoVueInsuvs.anUniv
> and etuInscrAdms.eoVueInsuvs.prdUniv = $etuInscrAdms.eoVueInsuvs.prdUniv)
>
> In this qualifier to use with my ERXBatchingDisplayGroup
>
> NSMutableArray qualFinal = new NSMutableArray();
> NSMutableArray args = new NSMutableArray();
> NSMutableArray argsUv = new NSMutableArray();
> NSMutableArray argsEtu = new NSMutableArray();
>
> args.addObject(anUniv);
> args.addObject(prdUniv);
> argsUvaddObject(uv);
> argsUv.addObject(anUniv);
> argsUv.addObject(prdUniv);
> argsEtu.add("03");
> argsEtu.add("UTT");
>
> EOQualifier qualNom = EOQualifier.qualifierWithQualifierFormat("nom like %@", new NSArray(nom+"*"));
> EOQualifier qualPrenom = EOQualifier.qualifierWithQualifierFormat("prenom like %@", new NSArray(prenom+"*"));
> EOQualifier qualNumero = EOQualifier.qualifierWithQualifierFormat("individ=%@", new NSArray(nombre));
>
> EOQualifier qualPeriode = EOQualifier.qualifierWithQualifierFormat("etuInscrAdms.anUniv=%@ AND etuInscrAdms.prdUniv=%@", args);
> EOQualifier qualDiplome = EOQualifier.qualifierWithQualifierFormat("etuInscrAdms.diplomec=%@",new NSArray(diplome));
> EOQualifier qualNiveau = EOQualifier.qualifierWithQualifierFormat("etuInscrAdms.niveauAdm=%@",new NSArray(niveau));
> EOQualifier qualNiveau = EOQualifier.qualifierWithQualifierFormat("etuInscrAdms.niveauAdm=%@",new NSArray(niveau));
> EOQualifier qualUv = EOQualifier.qualifierWithQualifierFormat("etuInscrAdms.eoVueInsuvs.anUniv=%@ AND etuInscrAdms.eoVueInsuvs.prdUniv=%@ AND etuInscrAdms.eoVueInsuvs.uvc=%@",argsUv);
>
> EOQualifier qualEtudiant = EOQualifier.qualifierWithQualifierFormat("etuInscrAdms.diplSpecc<>%@ AND indEmails.emailTypec=%@",argsEtu);
>
> qualFinal.addObject(qualNom);
> qualFinal.addObject(qualPrenom);
> qualFinal.addObject(qualNumero);
> qualFinal.addObject(qualPeriode);
> qualFinal.addObject(qualDiplome);
> qualFinal.addObject(qualNiveau);
> qualFinal.addObject(qualUv);
>
> **EOSort
> NSArray dgSort = new NSArray(new Object[] {EOSortOrdering.sortOrderingWithKey(EOIndividu.NOM_KEY, EOSortOrdering.CompareAscending),
> EOSortOrdering.sortOrderingWithKey(EOIndividu.PRENOM_KEY,EOSortOrdering.CompareAscending) });
>
> fetchEtudiantViaUvDg.setQualifier(new EOOrQualifier(qualFinal));
> fetchEtudiantViaUvDg.setSortOrderings(dgSort);
> fetchEtudiantViaUvDg.fetch();
>
> Here is result :
>
> 45478 [WorkerThread0] DEBUG NSLog - evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "select count(*) FROM GERRY.INDIVIDU t0, GEST_UV.VUE_INSUV T2, GERRY.IND_EMAIL T3, GERRY.ETU_INSCR_ADM T1 WHERE ((T1.AN_UNIV = ? AND T1.PRD_UNIV = ?) OR T1.DIPLOME_$C = ? OR T1.NIVEAU_ADM = ? OR (T2.AN_UNIV = ? AND T2.PRD_UNIV = ? AND T2.UV_$C = ?) OR (T1.DIPL_SPEC_$C <> ? AND T3.EMAIL_TYPE_$C = ?)) AND T1.AN_UNIV = T2.AN_UNIV AND T1.DIPLOME_$C = T2.DIPLOME_$C AND T1.ETU_ID = T2.ETU_ID AND T1.PRD_UNIV = T2.PRD_UNIV AND t0.INDIV_ID = T3.INDIV_ID AND t0.INDIV_ID = T1.CONSEILLER_ID" withBindings: 1:"2010"(anUniv), 2:"1A"(prdUniv), 3:"ING2"(diplomec), 4:"4"(niveauAdm), 5:"2010"(anUniv), 6:"1A"(prdUniv), 7:"11420"(uvc), 8:"03"(diplSpecc), 9:"UTT"(emailTypec)>
> 47102 [WorkerThread0] DEBUG NSLog - 1 row(s) processed //All students
>
> 47132 [WorkerThread0] DEBUG NSLog - evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "select * from (select INDIV_ID, rownum eo_rownum from (SELECT t0.INDIV_ID FROM GERRY.INDIVIDU t0, GEST_UV.VUE_INSUV T2, GERRY.IND_EMAIL T3, GERRY.ETU_INSCR_ADM T1 WHERE ((T1.AN_UNIV = ? AND T1.PRD_UNIV = ?) OR T1.DIPLOME_$C = ? OR T1.NIVEAU_ADM = ? OR (T2.AN_UNIV = ? AND T2.PRD_UNIV = ? AND T2.UV_$C = ?) OR (T1.DIPL_SPEC_$C <> ? AND T3.EMAIL_TYPE_$C = ?)) AND T1.AN_UNIV = T2.AN_UNIV AND T1.DIPLOME_$C = T2.DIPLOME_$C AND T1.ETU_ID = T2.ETU_ID AND T1.PRD_UNIV = T2.PRD_UNIV AND t0.INDIV_ID = T3.INDIV_ID AND t0.INDIV_ID = T1.CONSEILLER_ID ORDER BY UPPER(t0.NOM) ASC, UPPER(t0.PRENOM) ASC)) where eo_rownum between 1 and 10" withBindings: 1:"2010"(anUniv), 2:"1A"(prdUniv), 3:"ING2"(diplomec), 4:"4"(niveauAdm), 5:"2010"(anUniv), 6:"1A"(prdUniv), 7:"11420"(uvc), 8:"03"(diplSpecc), 9:"UTT"(emailTypec)>
> 50212 [WorkerThread0] DEBUG NSLog - 10 row(s) processed //10 students per batch(don't use distinct)
>
> My problem is this fetch don't use *disctinct* and I have several time the same student. The another problem is how to set qualifier to do natural join like :
> A.id = B.id, when I don't have parameters for my qualifier?
>
> i.e : SELECT * FROM GERRY.INDIVIDU t0, GEST_UV.REF_CIVILITE T2, GERRY.IND_EMAIL T3, GERRY.ETU_INSCR_ADM T1
> WHERE t0.INDIV_ID = T3.INDIV_ID AND t0.INDIV_ID = T1.ETU_ID AND t0.CIVILITE_$C = T2.CIVILITE_$C ORDER BY UPPER(t0.NOM) ASC, UPPER(t0.PRENOM) ASC)
>
> Thanks for all
> ========================================
>
> Message du : 11/05/2011
> De : "Chuck Hill " <email@hidden>
> A : email@hidden
> Copie à : email@hidden
> Sujet : Re: Tr: Re: Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
>
>
> Hi Raymond,
>
>
> On May 11, 2011, at 5:29 AM, email@hidden wrote:
>
>> Hi,
>> Thanks for your reply but I use ERXBatchingDisplayGroup (see attached) and in my search class :
>>
>> individuList = EOUvOuvertes.fetchStudentViaUVz(ed, anUniv, prdUniv, diplome, niveau, uv, nom, prenom, nombre);//get OutOfMemory here
>> fetchEtudiantViaUvDg.setObjectArray(individuList);
>> fetchEtudiantViaUvDg.setCurrentBatchIndex(individuList.count());
>
> You should not need to do that in code. That will fetch everything all at once. You should be setting the qualifier on the fetch spec that the ERXBatchingDisplayGroup is using.
>
> Data fetched from a stored procedure is just data (I think), not EOs that are fetched from an entity. That data is read only, it can't be updated. You could use a stored procedure to just fetch the primary key and then use something like ERXEOControlUtilities public static NSArray faultsForRawRowsFromEntity(EOEditingContext ec, NSArray primKeys, String entityName) to convert them into EOs.
>
>
> Chuck
>
>
>> thanks for your help
>>
>> ========================================
>>
>> Message du : 11/05/2011
>> De : "Pascal Robert " <email@hidden>
>> A : email@hidden
>> Copie à : email@hidden, email@hidden, email@hidden, email@hidden
>> Sujet : Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
>>
>>
>>
>>
>> Le 2011-05-11 à 07:58, email@hidden a écrit :
>>
>> > Hi,
>> >
>> > I follwed all your instructions and corrected my eomodel and my fetch spec. give this request.
>> >
>> > 33963 [WorkerThread0] DEBUG NSLog - evaluateExpression:
>> > "SELECT DISTINCT t0.ANC_UV_OUVERTE_ID, t0.CATEG_ID, t0.CRE_DATE, t0.DIPL_SPEC_$C, t0.DIPLOME_$C, t0.MAJ_AUTEUR, t0.MAJ_DATE, t0.NLE_UV_OUVERTE_ID, t0.UV_CREDITS_ECTS, t0.UV_OUV_ID
>> > FROM GEST_UV.UV_OUVERTES t0, GEST_UV.UV_OUV T1, GERRY.ETU_INSCR_ADM T3, GERRY.DIPLOME T2
>> > WHERE ((T1.AN_UNIV like ? ESCAPE '\' AND T1.PRD_UNIV like ? ESCAPE '\') OR t0.DIPLOME_$C like ? ESCAPE '\' OR T3.NIVEAU_ADM = ? OR T1.UV_OUV_ID = ?) AND t0.UV_OUV_ID = T1.UV_OUV_ID AND T2.DIPLOME_$C = T3.DIPLOME_$C AND t0.DIPLOME_$C = T2.DIPLOME_$C ORDER BY t0.DIPLOME_$C ASC"
>> > withBindings: 1:"2005"(anUniv), 2:"1A"(prdUniv), 3:"ING2"(diplomec), 4:"3"(niveauAdm), 5:131(uvOuvId)>
>> >
>> > The problem is the time(very very very slow) it takes to return data and get outOfMemory exception. (see below)
>> >
>> > 2520004 [WorkerThread0] DEBUG NSLog - 68758 row(s) processed
>>
>> 68758 rows, that's a lot of data! If you want to display the data in a display group, use ERXBatchingDisplayGroup from Project Wonder, this way it will fetch only the needed objects for the current batch (eg, if you show 10 objects at a time, ERXBatchingDisplayGroup will fetch only 10 objects per page). Something like this:
>>
>> public ERXBatchingDisplayGroup dgMembers;
>> static EOQualifier publicMembersQualifier = Member.SHOW_PROFILE.is(true);
>> public final static EOSortOrdering lastUpdateSort = new EOSortOrdering(Member.DATE_LAST_MODIFICATION_KEY,EOSortOrdering.CompareDescending);
>>
>> EODatabaseDataSource ds = new EODatabaseDataSource(ec, Member.ENTITY_NAME);
>> dgMembers = new ERXBatchingDisplayGroup();
>> dgMembers.setNumberOfObjectsPerBatch(10);
>> dgMembers.setDataSource(ds);
>> dgMembers.setSortOrderings(new NSArray(lastUpdateSort));
>> dgMembers.setQualifier(publicMembersQualifier);
>>
>> Even if Oracle might returns 68 758 rows fast, EOF will take a some time to convert those 68 758 rows to EOs.
>>
>> > 2520005 [WorkerThread0] DEBUG NSLog - === Commit Internal Transaction
>> > 2527790 [WorkerThread0] WARN NSLog - Throwable occurred: java.lang.OutOfMemoryError: Java heap space
>> > 2527790 [WorkerThread0] WARN NSLog - Workerthread exiting due to error, respawning with ID 10000...
>> > Exception in thread "WorkerThread0" com.webobjects.foundation.NSForwardException [java.lang.OutOfMemoryError] Java heap space:java.lang.OutOfMemoryError: Java heap space
>> >
>> > ========================================
>> >
>> > Message du : 11/05/2011
>> > De : "Susanne Schneider "
>> > A : email@hidden
>> > Copie à : email@hidden
>> > Sujet : Re: Fetching on 2 EOMODELS using different schema in DATABASE with but have relationship
>> >
>> >
>> >
>> > Hi,
>> >
>> > at least in Oracle it should be possible if you
>> >
>> > 1) qualify the table name with the schema name, e.g. "b.individu"
>> > 2) allow the user of the separate schemes to select the tables from the
>> > other schemes.
>> >
>> > If you have static (not changing) models, you can achieve the first by
>> > adding the scheme names to the table names in your EOModel. WO will then
>> > use the full qualified table names to build the fetch specification. For
>> > the second you have to extend your initial SQL-generation script.
>> >
>> > HTH,
>> > Susanne
>> >
>> > >
>> > > Message: 3
>> > > Date: Tue, 10 May 2011 10:36:49 -0700
>> > > From: Chuck Hill
>> > > Subject: Re: Fetching on 2 EOMODELS using different schema in DATABASE
>> > > with but have relationship
>> > > To: email@hidden
>> > > Cc: WebObjects webobjects-dev
>> > > Message-ID:
>> > > Content-Type: text/plain; charset="us-ascii"
>> > >
>> > > I think this is something that EOF can't do: qualify across schemas (databases in EOF's view).
>> > >
>> > > Chuck
>> > >
>> > > On May 10, 2011, at 4:49 AM, email@hidden wrote:
>> > >
>> > >> Hi all,
>> > >>
>> > >> I have 2 models using different schema of a database. but these schemas have table which have relationship. So in my model "A", I use a fetch spec. on a table which have the maximum relation with tables in model "A" and "B".
>> > >> So when I excute my fech spec, WO don't see the schema of model A and show exception ORA-00942 : table or view does not exist. This a the querry generate by WO on fetching :
>> > >>
>> > >> "SELECT t0.ANC_UV_OUVERTE_ID, t0.CATEG_ID, t0.CRE_DATE, t0.DIPL_SPEC_$C, t0.DIPLOME_$C, t0.MAJ_AUTEUR, t0.MAJ_DATE, t0.NLE_UV_OUVERTE_ID, t0.UV_CREDITS_ECTS, t0.UV_OUV_ID FROM UV_OUVERTES t0, INDIVIDU T4, UV_OUV T1, ETU_INSCR_ADM T3, DIPLOME T2 WHERE ((T1.AN_UNIV like ? ESCAPE '\' AND T1.PRD_UNIV like ? ESCAPE '\') OR t0.DIPLOME_$C like ? ESCAPE '\' OR T3.NIVEAU_ADM = ? OR T1.UV_OUV_ID = ?) AND T3.CONSEILLER_ID = T4.INDIV_ID AND t0.UV_OUV_ID = T1.UV_OUV_ID AND T2.DIPLOME_$C = T3.DIPLOME_$C AND t0.DIPLOME_$C = T2.DIPLOME_$C ORDER BY T4.NOM ASC, T4.PRENOM ASC" withBindings: 1:"2005"(anUniv), 2:"1A"(prdUniv), 3:"ING2"(diplomec), 4:"1"(niveauAdm), 5:131(uvOuvId)>"
>> > >>
>> > >> tables INDIVIDU, ETU_INSCR_ADM and DIPLOME are table of schema B but WO can't translate it like that i.e : A.INDIVIDU or A.ETU_INSCR_ADM.
>> > >>
>> > >> Thanks for your help
>> > >>
>> > >> PS : I have a storedProcedure which return data and do the same request but I don't know how to insert it in my code and binding it with WODisplayGroup for batch view (previous, next) with 10 students per batch.
>> > >> _____________________________________________
> --
> Chuck Hill Senior Consultant / VP Development
>
> Come to WOWODC this July for unparalleled WO learning opportunities and real peer to peer problem solving! Network, socialize, and enjoy a great cosmopolitan city. See you there! http://www.wocommunity.org/wowodc11/
>
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden