DEV Community

zeeshan mehdi
zeeshan mehdi

Posted on • Updated on

Node js Left Join with limit and offset Mysql


I was having a scenario where i was required to add pagination to website without using any plugin so i started, i knew how to use limit and offset but with joins it was a problem so after searching around i found the solution here it is .

let query = `select audio.title as title, audio.artist ,audio.fileName, audio.date,album.title as albumtitle from audio   left join album on audio.album = album.id order by audio.id LIMIT 10 offset ${offset}`;
    console.log(query);
    connection.query(query, function(error, result, fields) {

        if (error) {
            console.log(`error ${error}`);
            callback(error, null);
        }else {
            console.log(result);
            callback('success', result);
        }
    });

Enter fullscreen mode Exit fullscreen mode

result on console

select audio.title as title, audio.artist ,audio.fileName, audio.date,album.title as albumtitle from audio   left join album on audio.album = album.id order by audio.id LIMIT 10 offset 0
[
  RowDataPacket {
    title: 'Audio Title',
    artist: null,
    fileName: 'audio-1600246003244ha.mp3',
    date: 2020-09-16T08:46:43.000Z,
    albumtitle: 'Jal Pari'
  },
  RowDataPacket {
    title: 'Another audio',
    artist: null,
    fileName: 'audio-1600246030100gs.mp3',
    date: 2020-09-16T08:47:10.000Z,
    albumtitle: 'Jal Pari'
  },
  RowDataPacket {
    title: 'Some title',
    artist: null,
    fileName: 'audio-1600246129892gs.mp3',
    date: 2020-09-16T08:48:50.000Z,
    albumtitle: 'Jal Pari'
  },
  RowDataPacket {
    title: 'Upload Another',
    artist: null,
    fileName: 'audio-1600246160918la.mp3',
    date: 2020-09-16T08:49:21.000Z,
    albumtitle: null
  },
  RowDataPacket {
    title: 'Tere sang yaara',
    artist: 'Atif Aslam',
    fileName: 'audio-1600349287336an.mp3',
    date: 2020-09-17T13:28:07.000Z,
    albumtitle: null
  }
]

Enter fullscreen mode Exit fullscreen mode

If it helped you make sure to give thumbs up. thanks

Top comments (0)