laravel eloquent related relationship only first record

Question

I have a table with products, and another table with movements of products (addition, substraction) and table of authors of these movements. I want to get all products with the last movement in Eloquent ORM I have tried...

        $products = Product::with( array
        (
            'movs' => function ( $query )
            {
                $query->orderBy( 'id', 'dsc' )->with('author')->first();
            }
        ) )->get();

But only get a zeroed movs array.

But when I do

        $products = Product::with( array
        (
            'movs' => function ( $query )
            {
                $query->orderBy( 'id', 'dsc' )->with('author');
            }
        ) )->get();

I get all the according related models (movs and authors)... Is there any way to get this in Eloquent or I have to do with query builder... if so... how?

Thanks you in advance.

read this http://softonsofa.com/tweaking-eloquent-relations-how-to-get-latest-related-model/ — Jarek Tkaczyk, Oct 06 '14 at 13:35
Duplicate to: http://stackoverflow.com/questions/24343738/getting-just-the-latest-value-on-a-joined-table-with-eloquent — Yasen Slavov, Oct 06 '14 at 14:03
Hi Jarek... thanks for the info but is not exactly what I want... I dont want to load the model with a function that only is needed in one point. And the latest method not gives the last record exactly... because I dont trust in the created_at time.. I trust in sorting by id. Its not duplicate Yasen because Im asking if is some way to build ONLY as eloquent sentence (not model function) or db query builder... Thank you very much anyway — David, Oct 06 '14 at 16:20

score 1 · Answer 1 · answered Aug 23 '16 at 22:48

1

You must create a new relationship that is hasOne instead of hasMany.

This is the solution: http://alexkaye.co.uk/2015/01/04/eloquent-get-first-last-related-row/

answered Aug 23 '16 at 22:48

Alorse

377
3
10

1

although, the link you provided is not opening but your answer solved my issue. – Amit Shah Aug 26 '19 at 05:42

score 0 · Answer 2 · answered Oct 08 '14 at 11:03

Well... impossible to build with Eloquent... Impossible to build with query builder (error when using db raw) so... at the end build with mysql and DB::select

The sentence says select the products with last movement and author of the movement. (Null if not movements)

Relying in last movement on the last id not the date

        $mq =  "select *
            from products
            left join
            (
                    select concat (users.first_name,' ', users.last_name) as author,
                    from users
                    join (
                                select *
                                from movs
                                join (
                                        select max(movs.id) as id
                                        from movs
                                        group by product_id
                                        ) as movsids
                                on (movsids.id = movs.id)
                    )
                    as movuser
                    on movuser.author_id = users.id
            )
            as last_movs
            on products.id = last_movs.product_id";

        return DB::select($mq);

laravel eloquent related relationship only first record

2 Answers2