Set Query Builder conditions to Model in Laravel 5.1

- 1 answer

Ad

First I have to say that I tried to find solution, and i didn't.

Basic question:

$Br = new BrandTop;
dd( $Br->limit(10)->get() ); // Will return 10 rows

and

$Br = new BrandTop;
$Br->limit(10);
dd( $Br->get() ); // Will return all rows.

So, the basic question - why? How can I set some limit for Model, but still work with it, for example set (or not set) some where or order depends on other variables.

Advanced question:

I want to use Model like this:

class BrandTop extends Model
{
    public function withBrand() {
        return $this->leftJoin('brand', 'brand.id' , '=', 'brandtop.brand_id');
    }
    public function forType($type) // there is much more conditions for type
    {
        return $this->where(['type' => $type]);
    }

    // main function
    public function forSunglasses($limit = 0, $logo = false)
    {
        if ($logo)
            $this->where(['menu_logo' => 1])->orderBy('total_sales', 'desc');
        if ($limit)
            $this->limit($limit);

        return $this->forType('sunglasses')->withBrand();
        // But there goes Error, because forType() return Builder object, and it has no withBrand() method
    }
}

So, there is much more conditions, and it's much easier to set all conditions in separate methods. But how?

Ad

Answer

Ad

Model vs Builder

The thing to understand here is the difference between the Model object and the underlying Builder (query builder) object.

The statement $Br = new BrandTop; will create a new instance of a Model, and assign it to the $Br variable. Next, the $Br->limit(10) statement will create a new instance of a Builder object for the brand_tops table, with a limit of 10 applied.

In your first example, by doing $Br->limit(10)->get(), you're calling get() on the Builder that has your limit applied.

In your second example, your individual $Br->limit(10) creates the new Builder instance, but never uses it for anything. The next statement, $Br->get(), creates another new Builder instance without any constraints, so it retrieves all the records.

To be able to build up your query, you need to assign your Builder instance to a variable, and continue to modify that instance before finally calling get(). For example, to get your second example to work:

$query = BrandTop::query();
$query->limit(10);
$query->where(/*conditions*/);
dd($query->get());

Query Scopes

In relation to the second part of your question, you probably want to look into query scopes.

class BrandTop extends Model
{
    // renamed to "JoinBrand" instead of "WithBrand", as "with" would imply
    // an eager loaded relationship vs a joined table
    public function scopeJoinBrand($query)
    {
        return $query->leftJoin('brand', 'brand.id' , '=', 'brandtop.brand_id');
    }

    // got rid of "for" prefix
    public function scopeType($query, $type)
    {
        return $query->where('type', $type);
    }

    // got rid of "for" prefix
    public function scopeSunglasses($query, $limit = 0, $logo = false)
    {
        if ($logo)
            $query->where(['menu_logo' => 1])->orderBy('total_sales', 'desc');
        if ($limit)
            $query->limit($limit);

        return $query->type('sunglasses')->joinBrand();
    }
}

With the above model, your code would look something like:

dd(BrandTop::sunglasses()->get());

// or, more verbosely:

$query = BrandTop::query();
$query->sunglasses(); // $query already an object, no need to reassign it to itself
dd($query->get());
Ad
source: stackoverflow.com
Ad