Laravel DB::select is returning only the last 2 columns listed in the SELECT statement

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?

Answer

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, ...

Advertisement

Answer