How to 'order_by' on second table when using eloquent one-to-many

Question

Of course I can use order_by with columns in my first table but not with columns on second table because results are partial.

If I use 'join' everything works perfect but I need to achieve this in eloquent. Am I doing something wrong?

This is an example:

//with join
$data = DB::table('odt')
    ->join('hdt', 'odt.id', '=', 'hdt.odt_id')
    ->order_by('hdt.servicio')
    ->get(array('odt.odt as odt','hdt.servicio as servicio'));
foreach($data as $v){
    echo $v->odt.' - '.$v->servicio.'<br>'; 
}
echo '<br><br>';
//with eloquent
$data = Odt::get();
foreach($data as $odt){
    foreach($odt->hdt()->order_by('servicio')->get() as $hdt){
        echo $odt->odt.' - '.$hdt->servicio.'<br>';
    }
}

this package may help you https://github.com/fico7489/laravel-eloquent-join — fico7489, Dec 17 '17 at 18:11

score 1 · Answer 1 · answered Mar 06 '13 at 17:31

1

In your model you will need to explicitly tell the relation to sort by that field.

So in your odt model add this:

public function hdt() {
   return $this->has_many('hdt')->order_by('servicio', 'ASC');
}

This will allow the second table to be sorted when using this relation, and you wont need the order_by line in your Fluent join statement.

answered Mar 06 '13 at 17:31

codivist

505
1
5
14

it's the same because it orders all items find per row, independently. ( considering you mean function campos() ) – ebelendez Mar 06 '13 at 21:11

score 0 · Answer 2 · answered Mar 07 '13 at 18:59

I would advise against including the order by in the relational method as codivist suggested. The method you had laid is functionally identical to codivist suggestion.

The difference between the two solutions is that in the first, you are ordering odt ( all results ) by hdt.servicio. In the second you are retrieving odt in it's natural order, then ordering each odt's contained hdt by servico.

The second solution is also much less efficient because you are making one query to pull all odt, then an additional query for each odt to pull it's hdts. Check the profiler. Considering your initial query and that you are only retrieving one column, would something like this work?

HDT::where( 'odt_id', '>', 0 )->order_by( 'servico' )->get('servico');

Maybe I should have include other column in my example but the goal is to be able to get contents of columns in both tables. — ebelendez, Mar 08 '13 at 00:57

score 0 · Accepted Answer · answered Mar 08 '13 at 01:30

Now I see it was something simple! I have to do the query on the second table and get contents of the first table using the function odt() witch establish the relation "belongs_to"

//solution
$data = Hdt::order_by('servicio')->get();
foreach($data as $hdt){
    echo $hdt->odt->odt.' - '.$hdt->servicio.'<br>';
}

vFragosop · Answer 4 · 2013-03-08T05:23:12.673

The simple answer is:

$data = Odt::join('hdt', 'odt.id', '=', 'hdt.odt_id')
    ->order_by('hdt.servicio')
    ->get(array('odt.odt as odt','hdt.servicio as servicio'));

Anything you can do with Fluent you can also do with Eloquent. If your goal is to retrieve hdts with their odts tho, I would recommend the inverse query for improved readability:

$data = Hdt::join('odt', 'odt.id', '=', 'hdt.odt_id')
    ->order_by('hdt.servicio')
    ->get(array('hdt.servicio as servicio', 'odt.odt as odt'));

Both of these do exactly the same.

To explain why this works:

Whenever you call static methods like Posts::where(...), Eloquent will return a Fluent query for you, exactly the same as DB::table('posts')->where(...). This gives you flexibility to build whichever queries you like. Here's an example:

// Retrieves last 10 posts by Johnny within Laravel category
$posts = Posts::join('authors', 'authors.id', '=', 'posts.author_id')
    ->join('categories', 'categories.id', '=', 'posts.category_id')
    ->where('authors.username', '=', 'johnny')
    ->where('categories.name', '=', 'laravel')
    ->order_by('posts.created_at', 'DESC')
    ->take(10)
    ->get('posts.*');

How to 'order_by' on second table when using eloquent one-to-many

4 Answers4

Linked