I’ve created a query that runs perfectly on phpMyAdmin:
However when I run this by using DB::select() it only displays the last 2 columns in the SELECT statement.
Expected result: The query should output the same result when run on the phpMyAdmin prompt and the Laravel DB::select() function.
Actual result: the query result is wrong when run with the DB::select() function.
example run with PHP artisan tinker:
DB::select("
SELECT tipi_coinvolgimento.nome, users.nome, stati_incarico.nome, count(*) from incarichi
inner join coinvolgimenti on coinvolgimenti.incarico_id = incarichi.id
inner join tipi_coinvolgimento on tipi_coinvolgimento.id = coinvolgimenti.tipo_coinvolgimento_id
inner join users on users.id = coinvolgimenti.user_id
inner join stati_incarico on stati_incarico.id=incarichi.stato_id
group by tipi_coinvolgimento.nome, users.nome, stati_incarico.nome
");
outputs:
{#4245
+"nome": "Parcellato",
+"count(*)": 23,
},
{#4246
+"nome": "Annullato",
+"count(*)": 24,
},
{#4247
+"nome": "Concluso",
+"count(*)": 19,
},
As you can see, only the last 2 columns of the select are ending up in the output ;(
The very weird thing is that if I concatenate the columns in the select statement, then it displays them , so they are actually being selected, but they aren’t ending up in the output for some reason.
For example if I run
SELECT CONCAT(users.nome, ' ', tipi_coinvolgimento.nome, ' ', stati_incarico.nome), count(*) from incarichi
inner join coinvolgimenti on coinvolgimenti.incarico_id = incarichi.id
inner join tipi_coinvolgimento on tipi_coinvolgimento.id = coinvolgimenti.tipo_coinvolgimento_id
inner join users on users.id = coinvolgimenti.user_id
inner join stati_incarico on stati_incarico.id=incarichi.stato_id
group by tipi_coinvolgimento.nome, users.nome, stati_incarico.nome
then I get this result:
{#4495
+"columns": "Sarita Inseritore Interlocutorio",
+"count(*)": 36,
},
{#4496
+"columns": "Sarita Inseritore Pagato",
+"count(*)": 26,
},
{#4497
+"columns": "Sarita Inseritore Parcellato",
+"count(*)": 32,
},
Which has all of the column values!
Why are the column values not ending up in the output unless I use concat?
2
Answers
That’s because you are fetching the results into an associative array. You have 3 columns with the exact same name, meaning they get overwritten. Change your query to use aliases:
SELECT tipi_coinvolgimento.nome as co_nome, users.nome as u_nome, ...
it is actually due to same names and it’s overwriting them. change the names and give them aliases as below: