Query with inner join in Eloquent

@sr57 In this case, is not the same because I need to count and group by,

Code in Eloquent

 $answers = Answer::join('questions','answers.question_id','=','questions.id')
        ->join('answer_application','answer_application.answer_id','=','answers.id')
        ->join('dimensions','dimensions.id','=','questions.dimension_id')
        ->join('domains','domains.id','=','dimensions.domain_id')
        ->join('categories','categories.id','=','domains.category_id')
        ->select('categories.id AS category_id,
            categories.name AS category_name,
            answers.description AS answer_name,
            answers.id AS answer_id,
            questions.id AS question_id,
            COUNT(*) AS total')
        ->groupBy('categories.name, categories.id')
        ->orderBy('categories.id')
        ->get()
        ;

I have this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories.name, categories.id' in 'group statement' (SQL: select `categories`.`id` as `category_id, categories.name` from `answers` inner join `questions` on `answers`.`question_id` = `questions`.`id` inner join `answer_application` on `answer_application`.`answer_id` = `answers`.`id` inner join `dimensions` on `dimensions`.`id` = `questions`.`dimension_id` inner join `domains` on `domains`.`id` = `dimensions`.`domain_id` inner join `categories` on `categories`.`id` = `domains`.`category_id` group by `categories`.`name, categories`.`id` order by `categories`.`id` asc) 

@Josadec

Yes, it's a little bit different but it's more educational to do some tries before asking assistance.

You did not answer to my 2nd question ... I gave your another example (faster to code and to run)

@sr57 Why need to convert to eloquent the query, because in Mysql it works without problems.

Tell me, what do you mean by that question?

@Josadec

my 2nd example with DB facade

https://laravel.com/docs/9.x/database#running-a-select-query

$my_select="SELECT ....";
DB::select($my_select);

@MohamedTammam I run this code

 $answers = DB::table('answers')
        ->select(DB::raw('categories.id AS category_id,
        categories.name AS category_name,
        answers.description AS answer_name,
        answers.id AS answer_id,
        questions.id AS question_id, 
        COUNT(*) AS total'))
        ->join('questions','answers.question_id','=','questions.id')
        ->join('answer_application','answer_application.answer_id','=','answers.id')
        ->join('dimensions','dimensions.id','=','questions.dimension_id')
        ->join('domains','domains.id','=','dimensions.domain_id')
        ->join('categories','categories.id','=','domains.category_id')
        ->groupBy('categories.name')
        ->orderBy('categories.id')
        ->get()
        ;

shoot this error

SQLSTATE[42000]: Syntax error or access violation: 1055 'norma035asi.categories.id' isn't in GROUP BY (SQL: select categories.id AS category_id, categories.name AS category_name, answers.description AS answer_name, answers.id AS answer_id, questions.id AS question_id, COUNT(*) AS total from `answers` inner join `questions` on `answers`.`question_id` = `questions`.`id` inner join `answer_application` on `answer_application`.`answer_id` = `answers`.`id` inner join `dimensions` on `dimensions`.`id` = `questions`.`dimension_id` inner join `domains` on `domains`.`id` = `dimensions`.`domain_id` inner join `categories` on `categories`.`id` = `domains`.`category_id` group by `categories`.`name` order by `categories`.`id` asc)

@Josadec The error message tells you what's wrong. You're grouping the results only by category.name but you're also selecting other columns.

Imagine a situation where two categories have the same name. They would have different associated questions and answers. Which values would category_id, answer_name, question_id etc. have in the result row if you're only grouping results by category.name?

You need to figure out what you're trying to count and then group the data by the correct columns.

@Josadec As @jussimannisto said. You need to use an aggregate function like (FIRST, LAST, MIN, MAX, COUNT, etc) with columns that aren't in the group by clause.

@JussiMannisto No, I need to include category_id, asnwer_name, and question_id because this is part of the other process

I need to remove white space that comes from the DB Example

$categories = [];
        
        foreach($answers as $answer){
            $categories[str_replace(' ','',$answer->answer_name)][$answer->category_id] = $answer->total;
        }

        $data['answers'] = $answers;
        $data['categories'] = $categories;

@Josadec As @jussimannisto

Imagine a situation where two categories have the same name. They would have different associated questions and answers. Which values would category_id, answer_name, question_id etc. have in the result row if you're only grouping results by category.name

And for other questions, please open new discussion with more details to be able to help you.

@Josadec What I'm trying to tell you is that your query doesn't work because it has a logical flaw. Ask yourself: what is the COUNT(*) selection supposed to measure in your query?

I'll try another approach. Let's look at the first query you posted on this thread:

SELECT categories.name, COUNT(*) AS total 
FROM answers 
INNER JOIN questions ON answers.question_id = questions.id 
INNER JOIN answer_application ON answer_application.answer_id = answers.id 
INNER JOIN dimensions ON dimensions.id = questions.dimension_id 
INNER JOIN domains ON domains.id = dimensions.domain_id 
INNER JOIN categories ON categories.id = domains.category_id 
GROUP BY categories.name
ORDER BY categories.id;

This query makes sense. You're counting how many answers there are for each category name. More commonly you'd group the results by category.id instead of category.name, but that's beside the point.

Now let's look at your last query:

 $answers = DB::table('answers')
        ->select(DB::raw('categories.id AS category_id,
        categories.name AS category_name,
        answers.description AS answer_name,
        answers.id AS answer_id,
        questions.id AS question_id, 
        COUNT(*) AS total'))
        ->join('questions','answers.question_id','=','questions.id')
        ->join('answer_application','answer_application.answer_id','=','answers.id')
        ->join('dimensions','dimensions.id','=','questions.dimension_id')
        ->join('domains','domains.id','=','dimensions.domain_id')
        ->join('categories','categories.id','=','domains.category_id')
        ->groupBy('categories.name')

This query doesn't make sense. Why? Because you're trying to count answers that are grouped by category.name but the rest of the column values would be ambiguous.

Assume you have a category named "Test" and you want to COUNT(*) the total number of answers for that category.name using this query. What would the answer_id value on that result row be? There might be hundreds of answers per category name. This is why your database correctly gives you an error message.

You need to first figure out what you're trying to count with COUNT(*), if anything. Then you have to group the results by the correct columns. If you're not sure how aggregate functions like COUNT and the GROUP BY clause work, there are bunch of SQL tutorials online.

@MohamedTammam I found the correct query with Eloquent but for some, reason, when I go to print the table, doesn't show anything.

Class Livewire

class CategoriesResults extends Component
{
   public function render()
   {
      
       $answers = Answer::join('questions','answers.question_id','=','questions.id')
       ->join('answer_application','answer_application.answer_id','=','answers.id')
       ->join('dimensions','dimensions.id','=','questions.dimension_id')
       ->join('domains','domains.id','=','dimensions.domain_id')
       ->join('categories','categories.id','=','domains.category_id')
       ->select('categories.name AS category_name, 
                 SUM(IF(answers.description = "Siempre",categories.id,0)) AS total_always,
                 SUM(IF(answers.description = "Casi siempre",categories.id,0)) AS total_usually,
                 SUM(IF(answers.description = "Algunas veces",categories.id,0)) AS total_sometimes,
                 SUM(IF(answers.description = "Casi nunca",categories.id,0)) AS total_almostnever,
                 SUM(IF(answers.description = "Nunca",categories.id,0)) AS total_never
       ')
       ->groupBy('categories.name')
       ->orderBy('categories.id')
       ->get()
       ;

       return view('livewire.categoriesresults.categories-results',compact('answers'));
   }
}

Results of the dd($answers);

Illuminate\Database\Eloquent\Collection {#1714 ▼
  #items: array:5 [▼
    0 => App\Models\Answer {#1713 ▼
      #connection: "mysql"
      #table: "answers"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      +preventsLazyLoading: false
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #escapeWhenCastingToString: false
      #attributes: array:1 [▼
        "category_name,                   SUM(IF(answers.description = "Siempre",categories.id,0))" => "Ambiente de trabajo"
      ]
      #original: array:1 [▶]
      #changes: []
      #casts: []
      #classCastCache: []
      #attributeCastCache: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #fillable: []
      #guarded: []
    }
    1 => App\Models\Answer {#1712 ▶}
    2 => App\Models\Answer {#1711 ▶}
    3 => App\Models\Answer {#1710 ▶}
    4 => App\Models\Answer {#1709 ▶}

View component livewire

<tbody>
			@php
				$id =1;
			@endphp
              @foreach($answers as $answer)
                  <tr class="text-center">
                        <td>{{ $id++; }}</td>
                        @dd($answer)
                        <td>{{ $answer->category_name }}</td>
                        <td>{{ $answer->total_always }}</td>
                        <td>{{ $answer->total_usually }}</td>
                        <td>{{ $answer->total_sometimes }}</td>
                        <td>{{ $answer->total_allmostnever }}</td>
                        <td>{{ $answer->total_never}}</td>
                  </tr>
              @endforeach
            </tbody>

@Josadec Your question was about the query, if you solved that, please close the discussion and open a new one for other issues with more details.