Laravel get count of column rows with condition-CodePudding

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.

CodePudding user response：

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();

CodePudding user response：

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 coluumns or empty or not. It any column is empty do nothing. but if any coulumn having 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);

CodePudding user response：

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