You will notice that Joe works at a rather well known university too. :-) I'd listen to his advice or decide to not use WO.
Chuck
On Apr 13, 2011, at 8:00 AM, Joe Little wrote:
> 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
--
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
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ 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
