return rows in laravel that are not empty

Question

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.

I am not sure how to write is not null I tried $row = \DB::table($table)->whereNotNull('lunch')->get(); but didn't work for me. — Johnny
– Johnny, Commented Apr 9, 2017 at 18:39
I see. I wasn't using null in database that's why it didn't work. — Johnny
– Johnny, Commented Apr 9, 2017 at 19:02

Basheer Kharoti · Accepted Answer · 2017-04-10 05:18:25Z

22

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

answered Apr 10, 2017 at 5:18

Basheer Kharoti

4,3025 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Johnny Over a year ago

very useful code. I have updated my first post. Could you please have a look. I believe it is explaining more now and you should able to understand why I cannot use DB::table('mytable')->whereRaw('field <> ""')->get(); in this case.

Lijesh Shakya Over a year ago

Elegant solution. The only simple solution that helped to check for empty values!

max Over a year ago

Just commenting in case anyone finds this useful. I was able to use this code snippet but had to make a small tweak for it to work with postgres. The error I got was Syntax error: 7 ERROR: zero-length delimited identifier at or near """". Switching to ->whereRaw("field <> ''") seemed to do the trick. I guess double quotes here was not a meaningful identifier for psql

SpinyMan · Accepted Answer · 2020-05-01 08:07:48Z

18

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

answered May 1, 2020 at 8:07

SpinyMan

4865 silver badges9 bronze badges

1 Comment

Anwarul Islam Over a year ago

This is the great answer

Kevin Whitchurch · Accepted Answer · 2021-09-25 10:52:56Z

2

->whereNotNull('<field>')

As an alternative, this works for me

answered Sep 25, 2021 at 10:52

Kevin Whitchurch

211 silver badge4 bronze badges

Collectives™ on Stack Overflow

return rows in laravel that are not empty

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related