Use of disticnt

Question

Use of disticnt

le51 Dec '13

Created Dec '13	Last Reply None	Replies 0	Views 560	Votes 0

le51 43.9k

Dec '13

Hello, I can't get "distinct" to work as expected. Here's the code:

$this->view->setVar("cdb", Vols::find(array("columns"=>array("cdt_de_bord_id","cdt_de_bord"), "order"=>"cdt_de_bord ASC","distinct"=>"cdt_de_bord_id")));

Where Vols is a model created from a Postgresql view:

SELECT v.id_vol, v.saison, v.date_vol, at.id_aeronef, at.libelle AS libelle_aeronef, at.immatriculation, 
        CASE
            WHEN tv.id_tarif_type_vol = 3 OR tv.id_tarif_type_vol = 10 OR tv.id_tarif_type_vol = 16 THEN pvi.id_annu
            ELSE pvc.id_annu
        END AS cdt_de_bord_id, 
        CASE
            WHEN tv.id_tarif_type_vol = 3 OR tv.id_tarif_type_vol = 10 OR tv.id_tarif_type_vol = 16 THEN pil.trigram::text
            ELSE 
            CASE
                WHEN prc.prenom IS NULL THEN prc.nom::text
                ELSE btrim((prc.nom::text || ' '::text) || prc.prenom::text)
            END
        END AS cdt_de_bord, 
        CASE
            WHEN tv.id_tarif_type_vol = 3 OR tv.id_tarif_type_vol = 10 OR tv.id_tarif_type_vol = 16 THEN pve.id_annu
            ELSE pvo.id_annu
        END AS co_pi_id, 
        CASE
            WHEN tv.id_tarif_type_vol = 3 OR tv.id_tarif_type_vol = 10 OR tv.id_tarif_type_vol = 16 THEN 
            CASE
                WHEN pre.prenom IS NULL THEN pre.nom::text
                ELSE btrim((pre.nom::text || ' '::text) || pre.prenom::text)
            END
            WHEN tv.id_tarif_type_vol = 4 OR tv.id_tarif_type_vol = 5 OR tv.id_tarif_type_vol = 7 OR tv.id_tarif_type_vol = 11 OR tv.id_tarif_type_vol = 12 OR tv.id_tarif_type_vol = 12 OR tv.id_tarif_type_vol = 13 OR tv.id_tarif_type_vol = 17 THEN v.passager::text
            ELSE 
            CASE
                WHEN pro.prenom IS NULL THEN pro.nom::text
                ELSE btrim((pro.nom::text || ' '::text) || pro.prenom::text)
            END
        END AS co_pi, v.decollage, v.atterrissage, v.atterrissage - v.decollage AS temps_vol, rm.id_aeronef AS id_remorqueur, rm.libelle AS libelle_remorqueur, rm.immatriculation AS immatriculation_remorqueur, pvr.id_annu AS pilote_remorqueur_id, btrim((prr.nom::text || ' '::text) || prr.prenom::text) AS pilote_remorqueur, round((date_part('hour'::text, v.tps_remorque) * 100::double precision + date_part('minute'::text, v.tps_remorque) * 100::double precision / 60::double precision + date_part('second'::text, v.tps_remorque) * 100::double precision / 3600::double precision)::numeric, 0) AS temps_remorque_cent
   FROM vol v
   JOIN v_aeronef_type at ON v.id_aeronef = at.id_aeronef
   JOIN tarif_type_vol tv ON v.id_tarif_type_vol = tv.id_tarif_type_vol
   LEFT JOIN pilote_vol pvi ON v.id_vol = pvi.id_vol AND pvi.fonction = 3
   LEFT JOIN vfr_equipage pri ON pvi.id_annu = pri.personne_id
   LEFT JOIN pilote pil ON pvi.id_annu = pil.id_annu
   LEFT JOIN pilote_vol pve ON v.id_vol = pve.id_vol AND pve.fonction = 4
   LEFT JOIN vfr_equipage pre ON pve.id_annu = pre.personne_id
   LEFT JOIN pilote_vol pvc ON v.id_vol = pvc.id_vol AND pvc.fonction = 1
   LEFT JOIN vfr_equipage prc ON pvc.id_annu = prc.personne_id
   LEFT JOIN pilote_vol pvo ON v.id_vol = pvo.id_vol AND pvo.fonction = 2
   LEFT JOIN vfr_equipage pro ON pvo.id_annu = pro.personne_id
   LEFT JOIN pilote_vol pvt ON v.id_vol = pvt.id_vol AND pvt.fonction = 5
   LEFT JOIN personne prt ON pvt.id_annu = prt.personne_id
   LEFT JOIN pilote_vol pvr ON v.id_vol = pvr.id_vol AND pvr.fonction = 6
   LEFT JOIN personne prr ON pvr.id_annu = prr.personne_id
   LEFT JOIN pilote_vol pvpv ON v.id_vol = pvpv.id_vol AND pvpv.fonction = 7
   LEFT JOIN personne prpv ON pvpv.id_annu = prpv.personne_id
   LEFT JOIN compte_interne cipv ON pvpv.id_compte_interne = cipv.id_compte_interne
   LEFT JOIN pilote_vol pvpt ON v.id_vol = pvpt.id_vol AND pvpt.fonction = 8
   LEFT JOIN personne prpt ON pvpt.id_annu = prpt.personne_id
   LEFT JOIN compte_interne cipt ON pvpt.id_compte_interne = cipt.id_compte_interne
   LEFT JOIN pilote_vol pvpr ON v.id_vol = pvpr.id_vol AND pvpr.fonction = 9
   LEFT JOIN personne prpr ON pvpr.id_annu = prpr.personne_id
   LEFT JOIN compte_interne cipr ON pvpr.id_compte_interne = cipr.id_compte_interne
   LEFT JOIN pilote_vol pvpm ON v.id_vol = pvpm.id_vol AND pvpm.fonction = 10
   LEFT JOIN personne prpm ON pvpm.id_annu = prpm.personne_id
   LEFT JOIN compte_interne cipm ON pvpm.id_compte_interne = cipm.id_compte_interne
   LEFT JOIN treuil tr ON v.treuil = tr.id_treuil
   LEFT JOIN v_aeronef_type rm ON v.remorqueur = rm.id_aeronef
   LEFT JOIN remorque ro ON v.id_remorque = ro.id_remorque;

le51 · Answer 1 · 2013-12-02T13:31:27-07:00

Also with phql:

$query = $this->modelsManager->createQuery("SELECT DISTINCT cdt_de_bord_id, cdt_de_bord FROM Vols ORDER BY cdt_de_bord");
        $cdb = $query->execute();
        $this->view->setVar("cdb", $cdb);

Object $cdb is not well formatted (many cdt_de_bord_id and many cdt_de_bord) .... while raw SQL command "SELECT DISTINCT cdt_de_bord_id, cdt_de_bord FROM Vols ORDER BY cdt_de_bord" gives the right object.. I'm using Phalcon 1.3.0

le51 · Answer 2 · 2013-12-03T11:48:57-07:00

Vols::find(array("columns"=>array("cdt_de_bord_id","cdt_de_bord"), "order"=>"cdt_de_bord ASC","distinct"=>"cdt_de_bord_id"))

and

$query = $this->modelsManager->createQuery("SELECT DISTINCT cdt_de_bord_id, cdt_de_bord FROM Vols ORDER BY cdt_de_bord");
    $cdb = $query->execute();

result this Postgresql log:

exécute pdo_stmt_0000000a: SELECT "vols"."cdt_de_bord_id" AS "cdt_de_bord_id", "vols"."cdt_de_bord" AS "cdt_de_bord" FROM "public"."vols" ORDER BY "vols"."cdt_de_bord" ASC

is there something wrong