paolo@bimodesign.com | +34 608 61 64 10

Framework

        

Zend 2.0 - How configure routing

I needed to create a route using an alfhabetic ID as unique parameter to pass to my controller.
For example:
http://mydomain.localhost/comparador/en
http://mydomain.localhost/comparador/it
After several tests, I found this solution

    'router' => array(
        'routes' => array(
            'comparador' => array(
                'type' => 'segment',
                'options' => array(
                    'route'    => '/comparador',
                    'defaults' => array(
                        'controller' => 'PaoloPrueba\Controller\Index',
                        'action'     => 'index',
                    ),
                ),
                'may_terminate' => true,
                'child_routes' => array(
                    'language' => array(
                        'type'    => 'Segment',
                        'options' => array(
                            'route' => '[/:lang]',
                            'constraints' => array(
                                'lang' => '[a-z]{2}',
                                //'lang' => '(en|de|fr|nl)?',
                            ),
                        ),
                    ),
                ),
            ),
        ),
    ),

You can notice the constraint use, where there's this regular expression

[a-z]{2}

but you could use also this

(en|de|fr|nl)?

if you'll check a limited list of language code.
The called pivot table is the SQL way to spinning data column in a row, and then read these data as it should be a typically table. Here (http://stackoverflow.com/a/9668036) you could find a perfect explanation of the steps to solve this issue.
In my post, I'll explain the particular case that I had to solve.
This huge post is in Italian language and I'm translating in English as soon as possible.
Meanwhile, if you need more details, you could contact me using the "contact menu"
Per capire come ho applicato la soluzione della tabella pivot, farò una breve premessa, illustrando lo scenario in questione.
L'entità in questione sono:
- Hotel
- Clienti
- Target dei clienti dell'hotel.

Ogni cliente può alloggiare varie volte nello stesso hotel (le chiameremo "stay"). I target sono di due tipo. Quelli fissi, sono l'età, il paese di provenienza, il sesso, il tour operator e se viaggia con bambini, animali, etc
Quindi ci sono i target di tipo variabili, a due livelli. Ad esempio target "cucina" con sotto livello "italiana/francese/spagnola/vegetariana/etc) oppure "sport" con "tennis, piscina, etc"
Ogni hotel configura i propri target, sia quelli fissi che quelli custom.
Non mi soffermo oltre sulle tabelle implicate nella configurazione dei target dei clienti da parte degli hotel, mentre vi illustro le due tabelle che rappresentano la parte principale dell'estrazione delle statistiche, dove vengono archiviati i dati scelti da ogni cliente mentre si trova alloggiando in un hotel.

- app_user_default_target, che presenta cologne fisse, corrispondenti ai target indicati in precedenza (i nomi sono definiti in inglese).

+------+------+------+--------+---------+--------------+--------------+
| user | stay | age  | gender | country | touroperator | travels_with |
+------+------+------+--------+---------+--------------+--------------+
|   23 |   31 |    4 |      1 |       2 |            1 |            3 |
|   24 |   28 |    4 |      1 |       2 |            1 |            3 |
+------+------+------+--------+---------+--------------+--------------+

- app_user_custom_targets, che invece presenta una riga per ogni opzione scelta. Essendo queste configurabili, per semplificare la descrizione del problema, darò per scontato che siano solo 5, i cui id sono 1/2/3/4/5. Ad esempio, una configurazione possibile è questa.

+------+------+---------------+-------+
| user | stay | custom_target | value |
+------+------+---------------+-------+
|   24 |   28 |             1 |     2 |
|   24 |   28 |             3 |     2 |
|   24 |   28 |             4 |     3 |
|   23 |   31 |             1 |     2 |
|   23 |   31 |             3 |     1 |
|   23 |   31 |             4 |     3 |
+------+------+---------------+-------+

Come vedrete la select pivot, va costruita leggendo (cursore) la tabella targets_custom_values che ad esempio prevede questa configurazione
+----+---------------+-----------+
| id | custom_target | label     |
+----+---------------+-----------+
|  1 |             1 | Asiatica  |
|  2 |             1 | italiana  |
|  3 |             1 | española  |
|  4 |             1 | frances   |
|  5 |             2 | futbol    |
+----+---------------+-----------+

Nel mio caso ho usato il PHP.

Dopo questa lunga premessa, necessaria però per capire la problematica da affrontare, passiamo alla spiegazione di come costruire la select.
Innanzitutto, saranno implicate 4 tabelle in JOIN (INNER).
- tabella utenti: app_users
- tabella hotel: app_user_establishments, legata alla tabella user tramite l'userId.
- Tabella dei target fissi: app_user_default_target, legata alla tabella user tramite l'userId.
- Tabella dei target variabili app_user_custom_targets, legata alla tabella user tramite l'userId, ma che, appunto, presenta la caratteristica che i valori sono per riga e non per colonna.
Quindi su quest'ultima applicheremo, finalmente, il cosidetto "pivot". In questo modo.
(ricordo che per in questo link ((http://stackoverflow.com/a/9668036)) troverete una spiegazione perfetta, da cui ho preso spunto per questa mia.

Innanzitutto, dobbiamo individuiamo quali solo le colonne implicate.
Nel nostro caso, custom_target ci darà gli "x-valori" mentre user sarà quella degli "y-valori".
Quindi creiamo la select pivot (la chiamerò "A")

SELECT app_user_custom_targets.*,
       CASE WHEN custom_target = "1" THEN value END AS '1',
       CASE WHEN custom_target = "2" THEN value END AS '2',
       CASE WHEN custom_target = "3" THEN value END AS '3',
       CASE WHEN custom_target = "4" THEN value END AS '4',
       CASE WHEN custom_target = "5" THEN value END AS '5'
FROM app_user_custom_targets

Notare che le colonne della tabella pivot sono dei "numeri". Inoltre, come già ricordato, saranno proprio queste righe della select a dover essere generate rispetto ai valori presenti nella tabella di configurazione targets_custom_values
La select precedente, genera il risultato seguente

+------+------+---------------+-------+------+------+------+------+------+
| user | stay | custom_target | value | 1    | 2    | 3    | 4    | 5    |
+------+------+---------------+-------+------+------+------+------+------+
|   24 |   28 |             1 |     2 |    2 | NULL | NULL | NULL | NULL |
|   24 |   28 |             3 |     2 | NULL | NULL |    2 | NULL | NULL |
|   24 |   28 |             4 |     3 | NULL | NULL | NULL |    3 | NULL |
|   23 |   31 |             1 |     2 |    2 | NULL | NULL | NULL | NULL |
|   23 |   31 |             3 |     1 | NULL | NULL |    1 | NULL | NULL |
|   23 |   31 |             4 |     3 | NULL | NULL | NULL |    3 | NULL |
+------+------+---------------+-------+------+------+------+------+------+

Notate che le colonne 2 e 5 non presentano nessun valore, perchè nessun cliente li ha selezionati.
Quindi il passaggio successivo, sarà quello di raggruppare per la colonna user e fare il SUM di quelle che presentano i valori dei targhet. Aggiungiamo semplicemente un livello alla select precedente (la select la chiamerò B).
Così.

SELECT pivot_extend.user,
       SUM(pivot_extend.1) as '1',
       SUM(pivot_extend.2) as '2',
       SUM(pivot_extend.3) as '3',
       SUM(pivot_extend.4) as '4',
       SUM(pivot_extend.5) as '5'
  FROM (
         --- SELECT A --- 
       ) AS pivot_extend
  GROUP BY user

Il risultato sarà questo

+------+------+------+------+------+------+
| user | 1    | 2    | 3    | 4    | 5    |
+------+------+------+------+------+------+
|   23 |    2 | NULL |    1 |    3 | NULL |
|   24 |    2 | NULL |    2 |    3 | NULL |
+------+------+------+------+------+------+

Ovvero la nostra tabella che utilizzeremo in JOIN, aggiungendo semplicemente.

INNER JOIN (
     --- SELECT B ---
)
AS pivot_custom_target ON pivot_custom_target.user = apu.uid

Chiaramente la statistica verrà eseguita inserendo i propri parametri di ricerca scelti, ovvero nella WHERE avremo, ad esempio


...
AND (pivot_custom_target.1 = 2 AND pivot_custom_target.3 = 1 AND pivot_custom_target.4 = 3);

Qui (mettere link al txt) trovate la select completa che, ricordo ancora, deve essere costruita partendo dalla tabella targets_custom_values

Questa la select completa

/**
 * We have to use the pivot table for the app_user_custom_targets
 *
 * So the select will be like this example
 * SELECT apu.*
 *   FROM app_users apu
 * INNER JOIN app_user_establishments aue ON aue.user = apu.uid
 * INNER JOIN app_user_default_target aud ON aud.user = apu.uid
 * INNER JOIN (
 *            SELECT pivot_extend.user,
 *                   SUM(pivot_extend.1) as '1',
 *                   SUM(pivot_extend.2) as '2',
 *                   SUM(pivot_extend.3) as '3',
 *                   SUM(pivot_extend.4) as '4'
 *              FROM (
 *                    SELECT app_user_custom_targets.*,
 *                           CASE WHEN custom_target = "1" THEN value END AS '1',
 *                           CASE WHEN custom_target = "2" THEN value END AS '2',
 *                           CASE WHEN custom_target = "3" THEN value END AS '3',
 *                           CASE WHEN custom_target = "4" THEN value END AS '4'
 *                      FROM app_user_custom_targets
 *                   ) AS pivot_extend
 *            GROUP BY user
 *            ) AS pivot_custom_target ON pivot_custom_target.user = apu.uid
 *       WHERE (aue.establishment = 1 AND aue.user_type = 3)
 *         AND (aud.gender = 1 AND aud.age = 4 AND aud.country = 2 AND aud.touroperator = 1 AND aud.travels_with = 3)
 *         AND (pivot_custom_target.1 = 2 AND pivot_custom_target.3 = 1 AND pivot_custom_target.4 = 3);
 *
 *  Note 1: Is possible that the array will not have the value search for app_user_custom_targets OR for app_user_default_target
 *  Note 2: How the custom_target values/colums are variable, we'll generate them getting all the targets_custom_values.id field



Aggiunta al post
Se volessi confrontare due tabelle che presentano entrambe un formato pivot?
Dove in questo caso "user" può avere più campi della "campaign", ovvero per tornare true (> 0 record) tutti i valori di "User" devono essere compresi in quelli di Campaign, però User può averne di più di Camping

SELECT * 
FROM (SELECT pivot_extend.user,
                SUM(pivot_extend.1) as '1',
                SUM(pivot_extend.2) as '2',
                SUM(pivot_extend.3) as '3',
                SUM(pivot_extend.4) as '4'
            FROM (
                SELECT app_user_custom_targets.*,
                    CASE WHEN custom_target = '1' THEN value END AS '1',
                    CASE WHEN custom_target = '2' THEN value END AS '2',
                    CASE WHEN custom_target = '3' THEN value END AS '3',
                    CASE WHEN custom_target = '4' THEN value END AS '4'
                    FROM app_user_custom_targets
                ) AS pivot_extend
            GROUP BY user) as auc,
     (SELECT pivot_extend.campaign,
                SUM(pivot_extend.1) as '1',
                SUM(pivot_extend.2) as '2',
                SUM(pivot_extend.3) as '3',
                SUM(pivot_extend.4) as '4'
            FROM (
                SELECT campaign_custom_targets.*,
                    CASE WHEN custom_target = '1' THEN value END AS '1',
                    CASE WHEN custom_target = '2' THEN value END AS '2',
                    CASE WHEN custom_target = '3' THEN value END AS '3',
                    CASE WHEN custom_target = '4' THEN value END AS '4'
                    FROM campaign_custom_targets
                ) AS pivot_extend
            GROUP BY campaign) as cct
WHERE auc.`user` = 19
AND cct.`campaign` =10
AND (auc.`1` = cct.`1` OR cct.`1` is NULL)
AND (auc.`2` = cct.`2` OR cct.`2` is NULL)
AND (auc.`3` = cct.`3` OR cct.`3` is NULL)
AND (auc.`4` = cct.`4` OR cct.`4` is NULL)