How do I take the last so many entries (skip) with Eloquent Query Builder in Laravel

Question

I am trying to write a query in Laravel using Eloquent, but only want the last 5-ongoing fields in it. This is the query:

    public static function past_profile_fan_likes($id) {
        $latest_profile_fan_likes = DB::table('fanartists')
                    ->join('artists', 'fanartists.artist_id', '=', 'artists.id')
                    ->orderBy('fanartists.created_at', 'DESC')
                    ->skip(4)
                    ->where('fanartists.fan_id', '=', $id)
                    ->select('artists.id', 'artists.fbid', 'artists.stage_name', 'artists.city', 'artists.state', 'artists.image_path', 'artists.description')
                    ->get();


        return $latest_profile_fan_likes;

    }

When I call this, I am getting this error:

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 4' at line 1 (SQL: select `artists`.`id`, `artists`.`fbid`, `artists`.`stage_name`, `artists`.`city`, `artists`.`state`, `artists`.`image_path`, `artists`.`description` from `fanartists` inner join `artists` on `fanartists`.`artist_id` = `artists`.`id` where `fanartists`.`fan_id` = ? order by `fanartists`.`created_at` desc offset 4) (Bindings: array ( 0 => '1', ))

Is there something I am doing wrong here? Maybe something wrong with the skip usage? Thanks for your help.

score 3 · Accepted Answer · answered Feb 07 '14 at 23:44

You need to add a take query in order for it to add a LIMIT query and turn it into the correct syntax;

DB::table('fanartists')
->join('artists', 'fanartists.artist_id', '=', 'artists.id')
->orderBy('fanartists.created_at', 'DESC')
->skip(4)
->take(100)
->where('fanartists.fan_id', '=', $id)
->select('artists.id', 'artists.fbid', 'artists.stage_name', 'artists.city', 'artists.state', 'artists.image_path', 'artists.description')
->get();

You will need to suppliy a limit if using an offset, even if you don't want to limit. See here: http://dev.mysql.com/doc/refman/5.0/en/select.html#id4651990

Is there a way to supply a limit big enough that it can handle any number of entries? — user1072337, Feb 07 '14 at 23:45

score 0 · Answer 2 · answered Feb 07 '14 at 23:03

0

This looks more like fluent which I'm not quite as familiar with. but if you have your models and relationships defined, using Eloquent, you could reverse the order and take(5) if you're just looking to get the last 5 records:
like this...

$latest_profile_fan_likes = fanartists::with('artists')->where('fan_id', '=', $id)->orderBy('created_at', 'ASC')->take(5)->get('id', 'fbid' .......);

answered Feb 07 '14 at 23:03

c-griffin

2,510
1
16
25

I don't want just the last 5 records, I want all records after the first 4. So record 5 through however many. – user1072337 Feb 07 '14 at 23:18
Ah, ok. Your skip() is indicated in your logic before your where(). By the error you're getting (offset), id move it after your where clause. – c-griffin Feb 08 '14 at 01:15

How do I take the last so many entries (skip) with Eloquent Query Builder in Laravel

2 Answers2