Ad

Select Query With Empty "where". Octobercms

- 1 answer

I use code for filtering via $_GET

    $this['painting'] = Painting::
    where('p_price',$p_price)->
    where('p_created',$p_created)   ->
    where('type_id',$type_id)   ->
    whereHas('artist', function($q)
        {
             $q->where('artist_slug', '=', $this->param('slug'));
        })->get();

url is mysite/page.php?type_id=3&p_created=1996 All ok.

But if I have empty some parameter I have no result. For example url is mysite/page.php?type_id=&p_created=1996 or url is mysite/page.php?type_id=3&p_created=return empty string

Now i use something like it

if (!$p_created and !$type_id){
    $this['painting'] = Painting::
    whereHas('artist', function($q)
        {
             $q->where('artist_slug', '=', $this->param('slug'));
        })->get();
}
elseif (!$p_created and $type_id){
        $this['painting'] = Painting::
        where('type_id',$type_id)   ->
        whereHas('artist', function($q)
        {
             $q->where('artist_slug', '=', $this->param('slug'));
        })->get();
}
elseif ($p_created and !$type_id){
        $this['painting'] = Painting::
        where('p_created',$p_created)   ->
        whereHas('artist', function($q)
        {
             $q->where('artist_slug', '=', $this->param('slug'));
        })->get();
}
    elseif ($p_created and $type_id){
        $this['painting'] = Painting::
    where('p_created',$p_created)   ->
    where('type_id',$type_id)   ->
    whereHas('artist', function($q)
        {
             $q->where('artist_slug', '=', $this->param('slug'));
        })->get();

}

How can I fix it?

Ad

Answer

I don't know OctoberCMS, and I don't see an obvious way to get a basic query object to work with, but you could simulate that by doing a query which will always return true. Something like this:

// Assuming type_id is always a positive integer, no nulls allowed
$this['painting'] = Painting::where('type_id', '>', -1);

Then you add each condition, if it exists:

if ($this->param('slug')){
    $this['painting'] = $this['painting']->
        whereHas('artist', function($q){
             $q->where('artist_slug', '=', $this->param('slug'));
        });
}
if ($p_created){
    $this['painting'] = $this['painting']->
        where('p_created',$p_created);
}
if ($type_id){
    $this['painting'] = $this['painting']->
        where('type_id',$type_id);
}

Finally, get the results:

$this['painting'] = $this['painting']->get();

You many not need to always do $this['painting'] = $this['painting']->where, simply $this['painting']->where may suffice, depending on how that object works internally.

Ad
source: stackoverflow.com
Ad