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
