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
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
