Laravel using skip and take limiting query

Question

I'm trying to build a query that uses skip() and take() functions, for some reason it keeps adding offset 0 at the end instead it should be LIMIT 0,0

Code:

    $dbSearch = new SchoolInfo;
    $dbSearch = $dbSearch->where(function($query) use($search) {

        $query->where('school_name', 'LIKE', '%'.$search.'%')
            ->orWhere('address_1', 'LIKE', '%'.$search.'%')
            ->orWhere('address_2', 'LIKE', '%'.$search.'%')
            ->orWhere('address_3', 'LIKE', '%'.$search.'%')
            ->orWhere('address_4', 'LIKE', '%'.$search.'%')
            ->orWhere('county', 'LIKE', '%'.$search.'%')
            ->orWhere('postcode', 'LIKE', '%'.$search.'%')
            ->orWhere('head_teacher_email', 'LIKE', '%'.$search.'%')
            ->orWhere('head_teacher_first', 'LIKE', '%'.$search.'%')
            ->orWhere('head_teacher_last', 'LIKE', '%'.$search.'%');
    });
    $results = $dbSearch
        ->skip($startat)
        ->take($startat)
        ->orderBy('school_name', 'ASC')
        ->get();

And this is the error I get

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'offset 0' at line 1 (SQL: select * from `school_info` where (`school_name` LIKE %ok% or `address_1` LIKE %ok% or `address_2` LIKE %ok% or `address_3` LIKE %ok% or `address_4` LIKE %ok% or `county` LIKE %ok% or `postcode` LIKE %ok% or `head_teacher_email` LIKE %ok% or `head_teacher_first` LIKE %ok% or `head_teacher_last` LIKE %ok%) order by `school_name` asc offset 0)","file":"\/var\/www\/html\/globalrecruit\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php

Answer 1

When you do skip(n), it adds an offset n to the query, as you noticed. And when you do take(n), it adds a limit of n to the query. The limit then overrides the offset when the actual query is constructed.

But by take(0), you're saying you want zero results back. Eloquent thinks you don't want to apply a limit and so doesn't. Instead, only the offset is left, and doesn't in itself constitute a valid query.

Make sure the take() value is larger than zero, and you should be fine. :)

(Note: this is not completely true for unionised queries, but that's a different thing altogether.)