return rows in laravel that are not empty
Solution 1
The most appropriate way would be to use whereRaw
operator rather then where
.
e.x The following query will fetch all data except empty("")
values in the field list.
DB::table('mytable')->whereRaw('field <> ""')->get();
Solution 2
You can use macro, it checks field for not null and not empty string. Just add these lines of code into your AppServiceProvider
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
public function boot()
{
QueryBuilder::macro(
'whereNotEmpty',
function (string $column) {
return $this->whereNotNull($column)
->where($column, '<>', '');
}
);
EloquentBuilder::macro(
'whereNotEmpty',
function (string $column) {
return $this->getQuery()
->whereNotEmpty($column);
}
);
}
And now you allow to use this like that:
$posts = Post::whereNotEmpty('field')->get();
Solution 3
->whereNotNull('<field>')
As an alternative, this works for me
Johnny
Updated on July 10, 2022Comments
-
Johnny almost 2 years
I have the following output that I am getting from the laravel and I am inserting them in to selectbox as an option. But for the
lunch option
my last options are empty. How can I return rows in laravel that are not empty?{"id":18,"guest_origin":"Bulgaria","heard_where":"","staying_at":"","lunch_option":"Cheese"},{"id":19,"guest_origin":"Chech Republic","heard_where":"","staying_at":"","lunch_option":"Chicken"},{"id":20,"guest_origin":"China","heard_where":"","staying_at":"","lunch_option":"Ham"},{"id":21,"guest_origin":"Denmark","heard_where":"","staying_at":"","lunch_option":""},{"id":22,"guest_origin":"Finland","heard_where":"","staying_at":"","lunch_option":""},{"id":23,"guest_origin":"Israel","heard_where":"","staying_at":"","lunch_option":""},{"id":24,"guest_origin":"Malaysia","heard_where":"","staying_at":"","lunch_option":""},{"id":25,"guest_origin":"Norway","heard_where":"","staying_at":"","lunch_option":""},
controller.php
function getComboselect( Request $request) { if($request->ajax() == true && \Auth::check() == true) { $param = explode(':',$request->input('filter')); $parent = (!is_null($request->input('parent')) ? $request->input('parent') : null); $limit = (!is_null($request->input('limit')) ? $request->input('limit') : null); $rows = $this->model->getComboselect($param,$limit,$parent); $items = array(); $fields = explode("|",$param[2]); foreach($rows as $row) { $value = ""; foreach($fields as $item=>$val) { if($val != "") $value .= $row->{$val}." "; } $items[] = array($row->{$param['1']} , $value); } return json_encode($items); }
Model.php
static function getComboselect( $params , $limit =null, $parent = null) { $limit = explode(':',$limit); $parent = explode(':',$parent); if(count($limit) >=3) { $table = $params[0]; $condition = $limit[0]." `".$limit[1]."` ".$limit[2]." ".$limit[3]." "; if(count($parent)>=2 ) { $row = \DB::table($table)->where($parent[0],$parent[1])->get(); $row = \DB::select( "SELECT * FROM ".$table." ".$condition ." AND ".$parent[0]." = '".$parent[1]."'"); } else { $row = \DB::select( "SELECT * FROM ".$table." ".$condition); } }else{ $table = $params[0]; if(count($parent)>=2 ) { $row = \DB::table($table)->where($parent[0],$parent[1])->get(); } else { $row = \DB::table($table)->get(); } } return $row; }
This code is using http://carlosdeoliveira.net/jcombo/?lang=en. If you look up to the example on the project link you will see that it is using parent (state) to list the child (cities) for listings. I am not using the parent so nothing is assinged to variables $parent[0] and $parent[ 1 ], thus nothing to worry about but for the rest, I will try to post each result below so, you would have a better idea. My understanding is that the model.php is passing the data to controllers.php using
$row = \DB::table($table)->get();
If you look to the screenshot, you will see that I have more then 1 column to list the options. I cannot write a single column name there if I write$row = \DB::table($table)->whereRaw('lunch <> ""')->get();
this brings the options until the Id 4. In this case Holland is not in the option list for guest origin.Once the model.php passes
$row
to controllers.php, It' returning the following results for each variable.print_r($row);
stdClass Object ( [id] => 48 [guest_origin] => Other [heard_where] => [staying_at] => [lunch_option] => )
print_r($rows);
Illuminate\Support\Collection Object ( [items:protected] => Array ( [0] => stdClass Object ( [id] => 1 [guest_origin] => Western Australia [heard_where] => Wildsights Office [staying_at] => Wildsights Villas [lunch_option] => Chicken ) 1 => stdClass Object ( [id] => 2 [guest_origin] => Rest of Australia [heard_where] => Brochure [staying_at] => Wildsights Beach Units [lunch_option] => Cheese ) [2] => stdClass Object ( [id] => 3 [guest_origin] => Germany & Austria [heard_where] => Sign [staying_at] => Bay Lodge Backpackers [lunch_option] => Ham ) [3] => stdClass Object ( [id] => 4 [guest_origin] => UK & Eire [heard_where] => Word of Mouth [staying_at] => Blue Dolphin Caravan Park [lunch_option] => Tuna )
print_r($fields);
Array ( [0] => staying_at )
print_r($value);
prints nothing
print_r($items);
[8] => Array ( [0] => Shark Bay Holiday Cottages 1 => Shark Bay Holiday Cottages ) [9] => Array ( [0] => Shark Bay Hotel 1 => Shark Bay Hotel )
Hope it is clear and you can help me to filter the empty rows before it goes into loop.