0

Here is what i'm trying to do:

I'm implementing a version_id in my vendors table. I'm trying to construct a query to get the index page working. So i'm looking to get all of the vendors from my database that have the latest version_id (max('version_id'). Can't seem to figure it out. Here is what I have so far:

$vendors = $this->account->vendors()->where('version_id', max(version_id))

        ->orderBy($this->orderBy, $this->sortBy)
        ->paginate($this->display_per_page);

I tried this before, but it also gives me errors:

$vendors = $this->account->vendors()->max('version_id')

            ->orderBy($this->orderBy, $this->sortBy)
            ->paginate($this->display_per_page);

It works if I just have this: 

$vendors = $this->account->vendors()
            ->orderBy($this->orderBy, $this->sortBy)
            ->paginate($this->display_per_page);

Been looking online but I only find suggestions for queries with joins and such, which is not what i'm looking for. Any help is appreciated!

max234435
  • 587
  • 5
  • 18

1 Answers1

2

This should work:

$vendors = $this->account->vendors()
    ->whereRaw('version_id = (SELECT MAX(version_id) from vendors)')
    ->orderBy($this->orderBy, $this->sortBy)
    ->paginate($this->display_per_page);

Assuming table name vendors

DevK
  • 9,597
  • 2
  • 26
  • 48
  • The only issue i'm having now is that is only return one record....how can I make it return highest version_id from that particular vendor. So many vendors only returning highest id in each group – max234435 May 06 '18 at 20:37
  • Are you sure you don't only have 1 vendor bound to this specific account with the max `version_id`? This shouldn't limit the number of results. – DevK May 06 '18 at 20:43
  • So when I add two separate records and both version_id on records show 1 the query gives me multiple results. As soo as I edit one of the records which puts version_id to 2, it only gives me version_id 2 record and shows 1 record – max234435 May 06 '18 at 20:47
  • I think i might need to add some kind of vendor_code field and group the result by a vendor_code so it gives me the max(version_id) from that vendor_code. Thoughts? – max234435 May 06 '18 at 20:49
  • Oh okay, I see what you need now. Yeah, I think you'll need some kind of grouping for that. I'll think about it a bit, don't have the solution right now – DevK May 06 '18 at 20:53
  • Yeah, you were right. You'll need some kind of identifier (vendor_code) and then perform a left join on the same table (on vendor_code). [This](https://stackoverflow.com/a/14841015/5405630) answer seems to tackle the issue. – DevK May 06 '18 at 21:03
  • So I added vendor_code to the database and implemented group by but it gives me a Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause – max234435 May 06 '18 at 22:53
  • The left join might be the better solution, judging by the answer I linked above. But group by should work as well. Not sure what your current code is, but maybe all you need is setting `strict` to `false` in your `config/database` file – DevK May 06 '18 at 23:17