[PHP] Contare quante volte uno stesso id si ripete nella tabella

[Phelps]

Ciao
avendo questo codice

$sql = "SELECT publishers.*, publishers.id, publishers.nome, addresses.publisher_id, addresses.numero FROM publishers
INNER JOIN addresses
ON publishers.id = addresses.publisher_id
ORDER BY publishers.nome";

-----------------------------------------------------------
--------------------------------------------------------------

@foreach($publishers as $publisher)
<tr>
//qui la logica che conta..e poi stampare in class il bg-danger
<th class="" scope="row">{{$publisher->nome}}</th>
<td>{{$publisher->numero}}</td>
</tr>
@endforeach

Come scritto nella join ad un publisher possono essere legati piu di un address tramite la foreign publisher_id

Come posso contare se uno stesso id si ripete più di due volte?
Se si ripete maggiore o uguale a due vorrei memorizzare in una variablie $class='bg-danger'.



OPPURE

avere una display di tutti i nomi della tabella publishers(dato che i campi sono solo id e nome) e al suo fianco avere il Count di quante volte un publisher è presente come publisher_id nella tabella addresses.
Esempio:

Phelps 2
Travis 4
John 3
Doe 2

Grazie

 

[marino51]

vedi se ti va bene, ottieni un elenco ordinato x occorrenze, nome, id

SELECT count(a.publisher_id) as counter, p.nome, p.id
FROM publishers p
INNER JOIN addresses a
ON p.id = a.publisher_id
group by p.nome, p.id
order by 1 desc, 2, 3

 

[Phelps]

Grazie marino.. Funziona.
E se io volessi accedere anche alla colonna numero di address come si può fare?

Esempio ora ho ottenuto il risultato che volevo tipo:

Tizio: 3
caio: 4
Semprionio 2

Ma volendo collegare un modal al numeretto dato dal counter(dovrebbe mostrare gli indirizzi legati), come si comporrebbe la query?
Grazie ancora

 

[marino51]

aggiungendo una colonna alla select, anch'essa va elencata nella "group by"
essendo un indirizzo, quindi si presume contenente valori diversi, si vanificherebbe la query
ma possiamo "raggirare" il problema usando una seconda volta la tabella "addresses",
dimmi se così può andare ed é quello che ti aspetti,

select  x.counter, x.nome, x.id, a2.indirizzo FROM (
SELECT count(a1.publisher_id) as counter, p.nome, p.id
FROM publishers p
INNER JOIN addresses a1
ON p.id = a1.publisher_id
group by p.nome, p.id
) x
LEFT JOIN addresses a2
ON x.id = a2.publisher_id
where x.counter = 2
order by 1 desc, 2, 3, 4

ho lasciato anche la clausola where così sai dove posizionarla
eventualmente, se ti servissero, sulla select annidata,, puoi aggiungerne altre alla "inner join … on"

 

[Phelps]

no con il modal aprirei solo una finestra di dialogo javascript quindi non romperei la query...
Il parametro verrebbe caricato con gli altri e rimarrebbe semplicemente nascosto fin quando non lo mostro con il modal..
Oppure se il modal mi resta complicato aggiungo un altro <th> e <td> e lo mostro affianco al contatore.
In questo casa penso che la query si semplifica molto.
Con l'ultima query complessa che mi hai postato il display non và.. Ottengo un solo record duplicato.

poi ho provato anche cosi:

"SELECT count(a.publisher_id) as counter, p.nome, p.id, a.numero
FROM publishers p
INNER JOIN addresses a
ON p.id = a.publisher_id
group by p.nome, p.id, a.numero
order by 1 desc, 2, 3, 4";

ho provato pure cosi, ma invece di ottenere il risultato corretto come lo ottenevo con la tua precedente query in questo modo:
Tizo: 3
Caio: 2
Semprionio: 2
Jhon: 1

ottengo
Tizio: 2
Jhon: 1
Tizio: 1
Sempronio 1
Sempronio 1

stranetto......

 

[marino51]

la seconda query che ti ho postato, l'ho provata e a me funziona regolarmente,
ovviamente ho cambiato i nomi per adattarla a due mie tabelle ma senza cambiare la struttura della stessa
questo il risultato

l'ultima colonna non contiene valori univoci, vedi righe 11/12 e 13/14

la considerazione può essere fatta se é bene/male presentare le righe 15 e 16 che hanno lo stesso valore
​

nella seconda query, prova a sostituire "indirizzo" con "numero" e vedi se funziona
poi come lo presenti dipende dalla query e dal codice che scrivi

se puoi postare un esempio con i valori (dati nelle tabelle) che stai usando per le prove, possiamo ragionare sulla stessa base e capire meglio

 

[Phelps]

Allora provo a risponderti ...
Tabella publishers = id | nome;
Tabella addresses = id | town_id | publisher_id | numero | ....................ecc non rilevanti

uso laravel ma non ho idea di come farlo con ELOQUENT, quindi uso query grezza
nel PublisherController metto la tua ultima query cioè:

$sql = "SELECT x.counter, x.nome, x.id, a2.numero FROM (
SELECT count(a1.publisher_id) as counter, p.nome, p.id
FROM publishers p
INNER JOIN addresses a1
ON p.id = a1.publisher_id
group by p.nome, p.id
) x
LEFT JOIN addresses a2
ON x.id = a2.publisher_id
where x.counter = 2
order by 1 desc, 2, 3, 4";

passo la collection ad una view e nel foreach della view faccio cosi:

@foreach($publishers as $publisher)
<tr>
//Questo codice semplicemente per evidenziare chi ha due indirizzi e chi ne ha piu di 3 o uguale a 3 in rosso. chi 1 niente
<?php if($publisher->counter == 2) {
$class="bg-warning";
} elseif ($publisher->counter >= 3) {
$class="bg-danger";
} else {
$class="";
}
?>
//Questo è quello che ciclo
<th class="<?=$class?>" scope="row">{{$publisher->nome}}</th>
<td>{{$publisher->counter}}</td>
<td>{{$publisher->numero}}</td>
</tr>
@endforeach

il risultato è che mi viene fuori solo il publisher con id 3 ed invece di utilizzare una sola riga ne usa due giustamente perchè ha due indirizzi: cosi

Publisher | Totale Indirizzi | Numero piantina indirizzo
Tizio | 2 | 1
Tizio | 2 | 6

E gli altri ?
Non si può avere il tutto su un unica riga tipo
Tizio | 2 | 1, 6 ***

Grazie. Spero è chiaro


Modifica che aggiungo:
Ho levato la where x.counter = 2 ed il risultato comincia a venire fuori chiaro ora.. Solo che invece di avere un nome solo con il suo count e la sua lista di indirzzi allegati, ho i risultati ripetuti in base a quanti indirizzi hanno..
Si può avere una sola riga come citato qui? ***

 

[macus_adi]

Utilizzo di una RAW query con laravel:

$result=\DB::select(\DB::raw('SELECT
  z_publishers.*,
  (SELECT
      COUNT(z_addresses.id) AS expr1
    FROM z_addresses
    WHERE z_addresses.publisher_id = z_publishers.id) AS num
FROM z_publishers'));
       print_r($result);



Utilizzo del QB
$res=\DB::table('z_publishers')
  ->select(['z_publishers.*',\DB::raw('(SELECT
  COUNT(z_addresses.id) AS expr1
FROM z_addresses
WHERE z_addresses.publisher_id = z_publishers.id) as num')])
  ->get();

Mantenendo la tua struttura si riesce ad arrivare ad un buon compromesso di facilità di lettura e scrittura della query
Schema DB

--
-- Create table "z_addresses"
--
CREATE TABLE z_addresses (
  id int(11) NOT NULL AUTO_INCREMENT,
  publisher_id int(11) DEFAULT NULL,
  town_id int(11) DEFAULT NULL,
  field1 varchar(255) DEFAULT NULL,
  field2 varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_z_addresses_z_publishers_id FOREIGN KEY (publisher_id)
  REFERENCES z_publishers (id) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 21
AVG_ROW_LENGTH = 819
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;
--
-- Create table "z_publishers"
--
CREATE TABLE z_publishers (
  id int(11) NOT NULL AUTO_INCREMENT,
  nome varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1001
AVG_ROW_LENGTH = 819
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;

L'output generato da Illuminate\Support\Collection è il seguente...

Dove num indica esattamente quanto da te richiesto...

Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[id] => 1
[nome] => 86C
[num] => 0
)

[1] => stdClass Object
(
[id] => 2
[nome] => 1DIREBKHR9J2O7782GY331U61I
[num] => 1
)

[2] => stdClass Object
(
[id] => 3
[nome] => 619I2461HUS51KVAP28KWJ029U4H4T5WRZ6309C2N20IX8HV9KL9YB
[num] => 0
)

[3] => stdClass Object
(
[id] => 4
[nome] => 67A2Z20
[num] => 0
)

[4] => stdClass Object
(
[id] => 5
[nome] => 626I6Y732I4
[num] => 1
)

[5] => stdClass Object
(
[id] => 6
[nome] => CDKS14LAF92GWG3M
[num] => 0
)

[6] => stdClass Object
(
[id] => 7
[nome] => R2760HOD1VR6
[num] => 3
)

[7] => stdClass Object
(
[id] => 8
[nome] => YSW9SN
[num] => 0
)

[8] => stdClass Object
(
[id] => 9
[nome] => 41H83S280837TP9F0N4MN96859VRHK0KU3059J9JD460K5N0Y0RXF
[num] => 0
)

[9] => stdClass Object
(
[id] => 10
[nome] => X0WD8849MGGGA4KGL0S66C6V9P8UCRG1YD35U110EDV1
[num] => 1
)

[10] => stdClass Object
(
[id] => 11
[nome] => CFK10V2UVEOE8J7
[num] => 0
)

)

)

I dati sono di prova, generati in automatico...


Utilizzando laravel e blade non dovresti creare delle view con delle condizioni, sarebbe meglio utilizzare gli helpers

<?php if($publisher->counter == 2) {
$class="bg-warning";
} elseif ($publisher->counter >= 3) {
$class="bg-danger";
} else {
$class="";

In questo specifico caso potresti richiamare un'helper del tipo "dammi il css della cella".....
qui la documentazione https://laravel.com/docs/5.8/helpers

<th class="{{get_class_css_name($item->num)}}"></th>

"get_class_css_name" è la funzione che ti permette di gestire le classi in modo dinamico e non sulla view, distribuendo così il codice in modo uniforme....
In questo modo sicuramente potrai riutilizzare quanto già scritto ed accelerare i tempi di scrittura delle view!

Buon Lavoro!

 

[Phelps]

Con questa query ottengo quello che ho ottenuto con la prima query di marino

$sql = "SELECT
publishers.*,
(SELECT
COUNT(addresses.id) AS expr1
FROM addresses
WHERE addresses.publisher_id = publishers.id) AS num
FROM publishers";

Facendo $pubisher->nome ottengo I nomi
Facendo $publisher->num ottengo quanti indirizzi hanno
e cosa devo fare invece ora per avere anche il numero identificativo degli indirizzi che hanno?
Sempre secondo l'esempio: Tizio | 2 | 1 e 5

 

[macus_adi]

Con questa query ottengo quello che ho ottenuto con la prima query di marino

Esatto ma non scordarti che stai utilizzando laravel e non delle lib php, quanto più possibile rendi il codice autoportante e non bloccante con query scritte a mano....

Se ottieni anche l'identificativo degli indirizzi sappi che torna in forma matriciale e non vettoriale, quindi sarebbe buona prassi spezzare il codice e creare un model che rispecchi le tue aspettative, in definitiva potresti fare così:

select `z_publishers`.`id`,
  (SELECT COUNT(id) AS expr1
    FROM z_addresses
  WHERE publisher_id = z_publishers.id) AS num,
  (SELECT GROUP_CONCAT(id, ',') AS identificativi
  FROM z_addresses za WHERE publisher_id = z_publishers.id) AS identificativo
  from `z_publishers`

In questo caso gli id vengono separati da virgola, e spetta a te convertire il tutto in vettore!

 

[Phelps]

non sapevo che in laravel non si poteva scrivere tutto.

$sql = "SELECT `publishers`.`id`,
(SELECT COUNT(id) AS expr1
FROM addresses
WHERE publisher_id = publishers.id) AS num,
(SELECT GROUP_CONCAT(id, ',') AS identificativi
FROM addresses za WHERE publisher_id = publishers.id) AS identificativo
from `publishers`";
[php]
Con questa ottengo tutto il resto che volevo, ma non i nomi, ma solo  id publisher cosi:

3 | 2 | 1,,5

Perfetto il contatore 2 e gli indirizzi allegati sono giusti! Ma il nome non c'è

 

[macus_adi]

non sapevo che in laravel non si poteva scrivere tutto.

Non è che non si può scrivere, è che la best practice sarebbe di minimizzare il codice scritto a mano cercando di non farlo diventare bloccante... Tutto qui... Laravel è un framework non è una libreria.... Fai uno sforzo, e vedi che i risultati si vedono e si sentono a livelli di prestazioni....

Non mi fa impazzire eloquent ma a volte mi lascia senza parole per quello che fa....

Guarda la query, è li che estrae i dati....

select `z_publishers`.`id`,
  `z_publishers`.`nome`,
  (SELECT COUNT(id) AS expr1
    FROM z_addresses
  WHERE publisher_id = z_publishers.id) AS num,
  (SELECT GROUP_CONCAT(id, ',') AS identificativi
  FROM z_addresses za WHERE publisher_id = z_publishers.id) AS identificativo
  from `z_publishers`

 

[Phelps]

Non sò perche quando le scrivo io le cose non esce mai!!!(avevo aggiunto anche io la proprietà alla select) Quando copio ed incollo invece si
Comunque si và! Questo è quello che volevo.
Best Practice o no comunque il risultato c'è e va bene! Il progettino è leggero e moolto funzionale..
Grazie del vostro aiuto, ragazzi