How to find only last row from second table, along with its first table row

If this

SELECT J.journal_name,V.id,V.journal_id FROM journals AS J 
INNER JOIN (SELECT *
FROM volumes
WHERE id IN (
SELECT MAX(id)
FROM volumes
GROUP BY journal_id
)) AS V ON J.id = V.journal_id

Works use that.

You can still convert it to a query builder or eloquent query something like:

$quy = Powner::select('powners.ownerid', 'powners.oname')->distinct()
                ->selectRaw('count(pets.petid) as CountOfpetid')
                ->leftJoin('pets', 'powners.ownerid', '=', 'pets.ownerid')
                ->groupby('powners.ownerid')
                ->orderby('powners.oname')
                ->get();

Just example, and see this for db facade:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

or

https://laracasts.com/discuss/channels/guides/getpdo-usage

You have to realize, some queries take trial and error to figure out.

As example see this post and the time it took to work out:

https://laracasts.com/discuss/channels/eloquent/orderby-computed-related-attribute

@JLRDW - I tried, but not working.

$resortData = DB::table('journals')
        ->select('journals.id','journals.journal_name', 'volumes.journal_id', 'volumes.volume_name', DB::raw("SELECT MAX(id)
        FROM volumes
        GROUP BY journal_id)"))
        ->Join('volumes', 'journals.id', '=', 'volumes.journal_id')
       ->get();

My query was just example use your query.

The the query Builder or eloquent, eloquent has all of query building methods. Look again at the examples in query Builder.

Your query that is working above can be written ln either eloquent or query Builder is what I am trying to say.

I don't get this stuff right the first time I am saying it takes some trial-and-error to work out these queries.

You can always use DB facade that will work 100% see the link and the example.

These eloquent relations and queries, convert to normal SQL at runtime anyway.

This link https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

See Cronix example in that link.

What makes 'volume5' the last row? Please don't tell me its just the one with the biggest number....

@SNAPEY - volume5 is the last row of volume_id 2.

volume3 is the last row of volume_id1

And I want only both row. Not all rows.

There you go again... I'm trying to get you to describe, in terms that the sql server will understand, what you mean by the 'last' row

And you don't want to use eloquent?

@SNAPEY - I tried by eloquent. But not succeed.

@SNAPEY - last row means max(id) of related journal_id.

@JLRDW - I tried with normal SQL. And it is working.

But If I use normal SQL query in laravel then what will be the use of laravel query method!!

Means I want it by eloquent or by query builder. Not directly by normal SQL method.

$journals = Journal::with(['volumes' => function($query) {
                 $q->orderBy('id','DESC')->take(1);
             }])
             ->get();

orderBy('id', 'desc') could also be latest('id') as a shortcut (produces same sql)

But If I use normal SQL query in laravel then what will be the use of laravel query method!!

As I said that can be done, sometimes it takes a little trial and error. It could take several tries.

My sample ORM query avove, it took me about 4 try's to get it, I finally found out I needed the selectRaw for the count.

To show how easy it was (your query) I adjusted mine to be similar to yours.

I said show me the maxid of the pet associated with an owner,

$quy = DB::connection('mysqlv2')->table('dc_powners')
                ->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
                ->selectRaw('max(dc_pets.petid) as maxid')
                ->where('dc_powners.ownerid', '<', 3)
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

The output:

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [ownerid] => 2
                    [oname] => DIANE
                    [maxid] => 77
                )

            [1] => stdClass Object
                (
                    [ownerid] => 1
                    [oname] => JIMMIE
                    [maxid] => 146
                )

        )

)

I restricted to two owners since my "testing" database has a lot of records.

So:

• Diane has as pet the max id of 77
• Jimmie has as pet max id of 146

There are others, but you wanted max id, just the one result in each case.

Also tested with ORM

$quy = Powner::select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('max(dc_pets.petid) as maxid')
                ->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->where('dc_powners.ownerid', '<', 3)
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

For some reason selects and joins in eloquent orm work in a different order than query builder, something you have to get use to.

Figured another way, was just playing around with it:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
                ->selectRaw('max(dc_pets.petid) as maxid')
                ->where('dc_powners.ownerid', '<', 3)
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

These all work

The post here is describes how to solve this of problem using sub-queries.