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