Laravel get count of column rows with condition

user_11667606 0
Viewed 2059

Suppose if I have a table of

ID      Col1     Col2       Col3       Col4     Col5

1       Hello    ""         Hi          ""       ""
2       two      ""         Hi          here     ""

I want to get the count of Col(X) without including the empty column. So for record 1 I must have a count of 2 and for record 2 I have 3

How can I do it in MySQL or in Laravel's Eloquent or Query Builder? Thanks in advance.

laravel-query-builder eloquent laravel mysql php
3 Answers

What you want, is to sum the not empty fields. In MySQL it looks like:

SELECT *, SUM((Col1!='') + (Col2!='') + (Col3!='') + (Col4!='') + (Col5!='')) as NotEmptyFields 
FROM table 
GROUP BY ID;

In Laravel it looks like:

Model::select(DB::raw('*, SUM((Col1!="") + (Col2!="") + (Col3!="") + (Col4!="") + (Col5!="")) as NotEmptyFields'))
    ->groupBy('ID')
    ->get();
user_12932440

Try this solution:

$data = DB::table('MyTable')->get();

Get all data from table using query like this in laravel. It return array of objects. So iterate your array using for each loop and check the columns or empty or not.

If any column is empty do nothing.

But if any column has a value count+1 add in your result array.

$result = [];

foreach($data as $object)
{
    $result[$object->id] = 0;

    foreach($object as $key => $value) 
    {        
        if(!empty($value))
        {
            if($key != 'id')
            {
                $result[$object->id] = $result[$object->id] + 1;
            }
        }
    }
}

var_dump($result);
user_79026450

Muhammad answer could become real slow on big datasets.

Try like this.

DB::table('model')
    ->select(DB::raw('
        (is_null(Col1) OR Col1 = '') +
        (is_null(Col2) OR Col2 = '') +
        (is_null(Col3) OR Col3 = '') +
        (is_null(Col4) OR Col4 = '') +
        (is_null(Col5) OR Col5 = '') 
            as NumberofNulls'
    ))

This should now count columns with either NULL or empty string in them

user_42896490
Related