[Dbix-class] how to avoid "select =>... as => ..." syntax with simple but long queries e.g. M fields from N tables with decodes and sql-date operations

Jess Robinson castaway at desert-island.demon.co.uk
Sat Dec 30 02:47:27 GMT 2006



On Wed, 27 Dec 2006, Igor Longagnani wrote:

> Hi,
> a newbie is here again :)
> I come from a DBIx::Class RTFM session and I am a little sad because it
> doesnt seem helpful with simple stuff like
>
> select
>    T1.field1,T1.field2,T2,field3,...,TN.fieldM             ---  (M
> fields from N tables)
> from
>    T1 table1,...TN tableN
> where
>    joins....and conditions...
>
> I can make nice joins and conditions through the  "search" syntax...
> BUT
> n cases like the one cited above it seems like you have to use the
> "select" syntax to pick fields you need:
>
>        select => qw/ fields...../
>        as => qw/alias of fields/
>
> When you have "VERY LONG" number of fields and tables, this last syntax
> becomes quite "error-prone".
>
> I am using a "well-formed" and consistent set of DBIx::Classes built
> from an Oracle schema, with relations
> and has_xxx methods and so on ... Catalyst 5.7006 framework environment.
>
> Any suggestion for something like the following?  I am somehow stuck,
> hoping not to have to re-type in "Perl-DBix-Class-mode" this sql which i
> didnt built the first time myself :)
> Especially for things like (TO_CHAR(SYSDATE, 'J') - TO_CHAR(RF.FIRMADT,
> 'J')) or some other DECODE?
> I even thought to work on the "Controller side" methods of Catalyst to
> make some operations, but still i would like to avoid to "prefetch" a
> huge amount of data when not strictly necessary and still I'd like to
> avoid the "select...as ..." syntax which would force me to re-type every
> single field twice ( the as section as well ).
>
> Thanks in advance for your support and ...below follows some SQL to
> translate into DBIx::Syntax...
> ... maybe not all fields are necesasry but most of them are.
> Igor
>
>        SELECT DISTINCT
> EE.CENTER, R.ALTNO, R.CODPAT , EE.USERID, EE.RAGIONE_SOCIALE,
> R.COD_PROGETTO, TO_CHAR(R.FIRMADT,'DD/MM/YYYY'), R.FIRMA,  IQ.DATA,
> IQ.NF_PROTOCOLLO_ITA_FILE,     IQ.NF_PROTOCOLLO_ITA, IQ.FILE_NAME,
> R.MVALUTAZIONE , IQ.PROPOSTA_RIF, IQ.PROGR, IQ.ID_INFO,
> I.SOTTOTIPO_INT, R.STATO_EVC, R.NOTE_EVC, 'SERVIZIO',  R.FIRMADT,
> (TO_CHAR(SYSDATE, 'J') - TO_CHAR(R.FIRMADT, 'J')), R.CLONATO,
> TO_NUMBER(DECODE(RF.PROGRESSIVO_RICHIESTA,NULL,
> '-1',RF.PROGRESSIVO_RICHIESTA,RF.PROGRESSIVO_RICHIESTA)),
> DECODE(TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'),NULL,TO_CHAR(R.FIRMADT,'DD/MM/YYYY'),
> TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'), TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'))
> ,(TO_CHAR(SYSDATE, 'J') - TO_CHAR(RF.FIRMADT, 'J')),
> RF.CONT_INT,RF.STATO_EVC,RF.NOTE_EVC,
> TO_DATE(DECODE(TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'),NULL,TO_CHAR(R.FIRMADT,'DD/MM/YYYY'),
> TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'),TO_CHAR(RF.FIRMADT,'DD/MM/YYYY')),'DD/MM/YYYY')
> ,
> TO_NUMBER(DECODE(RF.STATO_EVC,NULL,R.STATO_EVC,RF.STATO_EVC,RF.STATO_EVC)),
> EE.ID_ESERCENTE
>        FROM
> ANA_ESERCENTI_EE EE , RICHIESTE R, INF_QUANTITATIVE IQ,
> RICHIESTE_COORDINATE C, INTERVENTO I, RICHIESTE_FIRMA RF
>       WHERE
> R.CENTER = IQ.CENTER
>    AND R.ALTNO = IQ.ALTNO
>    AND R.CODPAT = IQ.CODPAT
>    AND R.CENTER = I.CENTER
>    AND R.ALTNO = I.ALTNO
>    AND R.CODPAT = I.CODPAT
>    AND TO_CHAR(R.CENTER) = EE.CENTER
>    AND R.CENTER=C.CENTER
>    AND R.ALTNO=C.ALTNO
>    AND R.CODPAT=C.CODPAT
>    AND RF.CENTER(+)=IQ.CENTER
>    AND RF.ALTNO(+)=IQ.ALTNO
>    AND RF.CODPAT(+)=IQ.CODPAT
>    AND RF.FIRMA(+)=IQ.ID_INFO
>    AND RF.FIRMA_PROGR(+)=IQ.PROGR
>    AND I.ID_INT=IQ.PROGR
>    AND C.VISITNUM=0 AND C.ESAM=0
>     AND R.FIRMA IS NOT NULL
>    AND I.DELETED IS NULL
>
>


If you insist on not using prefetch, which is really the general way to do 
this, then you can either try proxy (which adds fields across rels as if 
they were in the current resultsource.. which is generally for one-to-ones 
(might_have, and has_one).

You can also avoid retyping the plain lists of fields by doing:

select => [ $rs->result_source->columns ] .. and similar for the 
relationships, using related_resultset->resul_source->columns etc.

Jess




More information about the Dbix-class mailing list