Others will say similarly, but as someone who once used views, etc, you'll want to avoid these and stored procedures and build out all of this logic in EOF/Modeler and let WebObjects do it's thing. If you are executing SQL for results lists, you are likely trying too hard to outthink WO
On Apr 12, 2011, at 11:46 PM, naneon.raym...@neuf.fr wrote: > Hi Chuck, > > thanks for your reply. So I thought about stored procedures? But How I > include in my code and can I cast resultset in EOEntities Objects? > I want to use data in my WOPopUpButton. > > Thanks > > Ray > > > ======================================== > > Message du : 12/04/2011 > De : "Chuck Hill " <ch...@global-village.net> > A : naneon.raym...@neuf.fr > Copie à : webobjects-dev@lists.apple.com > Sujet : Re: fetching on joined table > > > Hi Ray, > > WebObjects is not going to generate SQL like that for you (maybe it is > possible, but it would be very difficult). I think that you have two > choices: > > 1. Use views > 2. Think and fetch in terms of Objects not tables and rows and joins. This > is going to be VERY different than how you are approaching the data now. > > It is possible that WebObjects is just not a good thing to use with your > schema. > > > Chuck > > > > On Apr 12, 2011, at 12:49 AM, <naneon.raym...@neuf.fr> > <naneon.raym...@neuf.fr> wrote: > >> Hi chuck, >> >> I go to give you more informations about data. I work in IT Universtity in >> France and we have huge database with lot of table. Here is an example of >> PDM (5 tables) >> >> person >> ------------>students--------->degree---------->diploma---------->academic >> year------>period_param----------------------------->admin_resgistration >> (fk_person) (fk_student) >> (fk_academic_year) (pk an_univ prd_univ) (fields an_univ_prd_univ) >> (fk_diploma, fk_person, fk_student, fk_academic_year) >> >> >> and I have lof view (view_student_course on a period ...), >> Here is code of view vue_parcours_profil using to have student curse : >> >> CREATE OR REPLACE FORCE VIEW "GEST_UV"."VUE_PROFIL_PARCOURS" ("ETU_ID", >> "DIPLOME_$C", "REORIENT_$F", "PRD_DEBUT", "PRD_FIN") >> AS >> SELECT DISTINCT t3.e, >> t3.d, >> t3.reorient_$f, >> MIN(p_deb) over (partition BY t3.e, t3.d, t3.p_fin) debut, >> t3.p_fin fin >> FROM >> (SELECT DISTINCT t1.e, >> t1.d, >> t1.no, >> t1.p p_deb, >> NVL(MIN(t2.p) over (partition BY t1.e, t1.d, t1.no), MAX (t1.p) over >> (partition BY t1.e, t1.d)) p_fin, >> CASE >> WHEN t1.no = 1 >> AND t2.r IS NULL >> THEN t1.r >> ELSE DECODE(t2.r,NULL,'N',t2.r) >> END reorient_$f >> --DECODE(t2.r,null,'N',t2.r) reorient_$f >> FROM >> (SELECT DISTINCT etu_id e, >> diplome_$c d, >> an_univ >> prd_univ p, >> ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ >> prd_univ NULLS LAST) no, >> DECODE(res_pedag_$c,'RE','O','DR','O','N') r >> FROM gerry.etu_inscr_adm eia1 >> ) t1, >> (SELECT DISTINCT etu_id e, >> diplome_$c d, >> an_univ >> prd_univ p, >> ROW_NUMBER() OVER (partition BY etu_id,diplome_$c order by an_univ >> prd_univ NULLS LAST) no, >> DECODE(res_pedag_$c,'RE','O','DR','O','N') r >> FROM gerry.etu_inscr_adm eia2 >> ) t2 >> WHERE t1.e = t2.e(+) >> AND t1.d = t2.d(+) >> AND t1.no < t2.no(+) >> AND t1.r <> t2.r(+) >> --and t1.r = 'N' >> AND ((t1.no = 1 >> AND NVL(t1.r,'N') IN ('O','N')) >> OR (t1.no > 1 >> AND NVL(t1.r,'N') = 'N')) >> ) t3; >> >> So when I want all students curse in sql developer this my request : >> >> SELECT vtp.etu_id as STUDENT_ID, >> vtp.diplome_$c as DIPLOMA_CODE, >> DECODE (vtp.diplome_$c,'ING',d.diplome >> ' ancien règlement',d.diplome) as DIPLOMA_NAME, >> vtp.prd_debut as PERIOD_BEGIN, >> vtp.prd_fin as PERIOD_END, >> gerry.fonc_trad_prd_lib(vtp.prd_debut,'anprd') as ACADEMIC_YEAR_BEGIN, >> gerry.fonc_trad_prd_lib(vtp.prd_fin,'anprd') ACADEMIC_YEAR_END, >> vtp.reorient_$f as ORIENTATION, >> (SELECT DIPL_SPEC_ABR >> ' ' >> DIPL_OPT_ABR >> ' ' >> NIVEAU_ADM >> FROM gerry.vue_eia_abr veia >> WHERE veia.etu_id = vtp.etu_id >> AND veia.diplome_$c=vtp.diplome_$c >> AND veia.an_univ >> veia.prd_univ=vtp.prd_fin >> ) as DIPLOMA_SPECIFICITY, >> (SELECT diplome_num >> FROM gest_uv.etu_diplome >> WHERE etu_id = vtp.etu_id >> AND diplome_$c = vtp.diplome_$c >> AND an_univ >> prd_univ BETWEEN vtp.prd_debut AND vtp.prd_fin >> ) as DIPLOMA_NUMBER, >> (SELECT SITUATION >> FROM gerry.etu_inscr_adm eia, >> gerry.ref_situation rs >> WHERE rs.situation_$c = eia.situation_$c >> AND eia.etu_id = vtp.etu_id >> AND eia.diplome_$c =vtp.diplome_$c >> AND eia.an_univ >> eia.prd_univ=vtp.prd_fin >> ) as STUDENT_SITUATION, >> NVL( >> (SELECT DECODE(situation_$c,'DEMI','O','N') >> FROM gerry.etu_inscr_adm >> WHERE etu_id = vtp.etu_id >> AND diplome_$c =vtp.diplome_$c >> AND situation_$c='DEMI' >> AND an_univ >> prd_univ=vtp.prd_fin >> ),'N') as IS_STUDENT_RESIGNED, >> NVL( >> (SELECT DECODE(situation_$c,'UTX','O','N') >> FROM gerry.etu_inscr_adm >> WHERE etu_id = vtp.etu_id >> AND diplome_$c =vtp.diplome_$c >> AND situation_$c='UTX' >> AND an_univ >> prd_univ=vtp.prd_fin >> ),'N') as IS_STUDENT_TRANSFERED >> FROM gest_uv.VUE_PROFIL_PARCOURS vtp, >> gerry.diplome d >> WHERE vtp.diplome_$c = d.diplome_$c >> order by prd_debut, prd_fin; >> >> So I must fetch on view vue_profil_parcours, diploma to display all students >> curse in a WOO table using batch. Here a little example and I have lof of >> fecthing such as this to do in my apps to display information about, >> sutdent, teacher so on... >> >> I want a example code How to translate this sql request or what the best >> practice in WebObjects in this situation? To fast forward in my apps, I >> create view every time when I want to display this kind of information. This >> allows me to browse my data in an accurate in WebObjects. >> >> Thanks for your help >> >> Ray, >> >> >> Message du : 11/04/2011 >> De : "Chuck Hill " <ch...@global-village.net> >> A : naneon.raym...@neuf.fr >> Copie à : webobjects-dev@lists.apple.com >> Sujet : Re: fetching on joined table >> >> >> >> On Apr 11, 2011, at 3:12 AM, <naneon.raym...@neuf.fr> wrote: >> >>> Hi Chuck, >>> >>> Tables A, B, C, D haven't relationship but same fileds name. >> >> If they don't have modeled relationships, then you can't use qualifiers. >> >> >>> When i'am using raw rows to translate my sql in webobjects and get my data, >>> I don't have EOF entities but dictionnaries. >>> >>> So I would like to know what the best practice on my case? create view with >>> my sql request on database and fetching on view in my model? >> >> I think the best practice would be to change the model (and the schema too, >> if needed) so that there are relationships and you are fetching entities. I >> very rarely need to do a raw row fetch. If you give us more information on >> the data, maybe we can suggest a better model. >> >> >> >> Chuck >> >> >>> ======================================== >>> >>> Message du : 11/04/2011 >>> De : "Chuck Hill " <ch...@global-village.net> >>> A : naneon.raym...@neuf.fr >>> Copie à : webobjects-dev@lists.apple.com >>> Sujet : Re: fetching on joined table >>> >>> >>> Hello Ray, >>> >>> >>> On Apr 8, 2011, at 5:16 PM, naneon.raym...@neuf.fr wrote: >>> >>>> Hi, >>>> >>>> I'm newbie on WebObjects and I have big problem to fetching on joined >>>> table. >>>> Example : >>>> I have my SQL like that : >>> >>> A good rule with WebObjects is "If you are thinking about SQL, you are >>> doing it wrong." There are times (i.e. when optimizing) when you do need >>> to think about the SQL, but thinking in terms of objects is usually the >>> better way. >>> >>>> >>>> select >>>> a.id b.id as ID, >>>> c.option as option, >>>> decode(d.no, 1, 'one', 2, 'two', 'unknow') as order >>> >>> WebObjects (EOF) does not do mixed table selects like this (unless you are >>> selecting Raw Rows). It selects whole rows of tables and makes them into >>> related Java objects. >>> >>> >>>> from >>>> ta a, tb b, tc c, td d >>>> where >>>> c.option in ('1', '2','3') >>>> and c.option = b.option >>>> >>>> How to translate this SQL request in EOF using qualifier or spec qualifier? >>>> >>>> Thanks for your help. >>> >>> What are the relationships in the model between A, B, C, and D? What are >>> you trying to fetch? >>> >>> >>> Chuck >>> >>> -- >>> Chuck Hill Senior Consultant / VP Development >>> >>> Practical WebObjects - for developers who want to increase their overall >>> knowledge of WebObjects or who are trying to solve specific problems. >>> http://www.global-village.net/products/practical_webobjects >>> >>> >>> >>> >>> >>> >>> >>> >> >> -- >> Chuck Hill Senior Consultant / VP Development >> >> Practical WebObjects - for developers who want to increase their overall >> knowledge of WebObjects or who are trying to solve specific problems. >> http://www.global-village.net/products/practical_webobjects >> >> >> >> >> >> >> >> > > -- > Chuck Hill Senior Consultant / VP Development > > Practical WebObjects - for developers who want to increase their overall > knowledge of WebObjects or who are trying to solve specific problems. > http://www.global-village.net/products/practical_webobjects > > > > > > > > > _______________________________________________ > Do not post admin requests to the list. They will be ignored. > Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) > Help/Unsubscribe/Update your Subscription: > http://lists.apple.com/mailman/options/webobjects-dev/jmlittle%40gmail.com > > This email sent to jmlit...@gmail.com
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com