Set Result Even If Record Does Not Exist

Asked

Viewed 120 times

0

I’m trying to make a query where the table shows the column with the total 0 even if there are no records in that given status.

For example:

SELECT
    STATUS, COALESCE(COUNT(ID), 0) AS TOTAL
FROM 
    TABELA
WHERE
    ID_CHAVE = 1
AND
    STATUS IN (1, 2, 3, 4)
GROUP BY
    STATUS

In this case, you only have status one and status two records. But I’d like it to appear in the result like this:

 STATUS    TOTAL
   1         3
   2         2
   3         0
   4         0

I want 0 to appear when there are no records with a certain status.

I will do this query in Laravel. But first I need to know how to do in SQL, then convert in ORM.

My table records at the moment:

ID      ID_USER     TITULO                      STATUS
1          1        Espetacular                    1    
1          1        Mais ou menos                  1    
1          1        Muito bom. Recomendo!          3

See above for existing records. It only has Status 1 and 3. No records with the other status, no exist!

With UNION I can do it quietly, but I didn’t want it that way.

SELECT COALESCE(STATUS, 1) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 1
UNION ALL 
SELECT COALESCE(STATUS, 2) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 2
UNION ALL
SELECT COALESCE(STATUS, 3) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 3
UNION ALL
SELECT COALESCE(STATUS, 4) AS STATUS, COUNT(ID) AS TOTAL FROM TABELA WHERE ID = 1 AND STATUS = 4
mysql laravel
  • 1

    http://stackoverflow.com/questions/16636433/mysql-count-to-return-0-if-no-records-found

  • No Join in my query.

  • There is more than one way to do it, it depends on what will determine how many status they are. There are always 4?

  • Are your status always fixed? (1, 2, 3, 4)

  • Yes, there will always be four.

  • Depending on the case, until UNION of 4 selects resolves. Now, if you’re using some server-side language, or some client program, it’s easier to store in an associative array and show with a for loop. You really need to do this in SQL?

  • It’s Laravel. But if I have the SQL query I convert it to Eloquent of Laravel.

  • @Bacco, so... I thought about doing it like this, but I didn’t want to use 4 selects... I don’t know if it interferes with the performance.

  • @Zoom In ddl, you set the default to 0 instead of null. The query will bring non-null values, that is, the records with value 0 will also be searched. If not, put in your query: and fieldname != null if unsolved, and fieldName =0

  • I’ll try, but what is DDL ?

  • The point is I want to bring a value that doesn’t even exist...

  • @Zoom is the time of creating the table. Or you may not even need to change the structure of the database. Test insert and fieldname = null to bring nulls

  • I’ll edit the question.

  • André, why not put an answer ? I didn’t understand anything you wrote.

  • The easiest is for you to enter the Status you don’t have in the table with a ID_CHAVE = GUID unique to this, and make a case in select and Where.

Show 10 more comments

1 answer

0


I solved. I created a table to save the status and made a leftJoin.

$totalRating = DealerStatusRating::leftJoin('dealer_ratings', function($query) use ($idDealer){
      $query->on('dealer_ratings.id_status', '=', 'dealer_status_ratings.id')
            ->where('id_concessionaria', '=', $idDealer);
})->groupBy('dealer_status_ratings.status')
  ->orderBy('dealer_status_ratings.id')
  ->select('dealer_status_ratings.status', 'slug', DB::raw('count(dealer_ratings.id) as total'))
  ->get();

Browser other questions tagged mysql laravel

You are not signed in. Login or sign up in order to post.