Laravel 5: DB::table() and relationship in Eloquent

Question

I have a hasMany relationship between Community and LangCommunity and I am trying to sort by name field without losing the relationship in the view.

Model Community

protected $fillable = ['province_id', 'active', 'address', 'phone', 'google_map'];

public function langs()
{
    return $this->hasMany('App\Models\LangCommunity');
}

Model LangCommunity

protected $fillable = ['community_id', 'lang_id', 'name', 'text'];

public function community()
{
    return $this->belongsTo('App\Models\Community');
}

In controller, I have:

$data['communities'] = DB::table('communities')
        ->join('lang_communities', 'communities.id', '=', 'lang_communities.community_id')
        ->select('communities.*', 'lang_communities.name')
        ->where('lang_communities.lang_id', '1')
        ->orderBy('lang_communities.name', 'asc')
        ->paginate($num);

This work, but I can't use the relationship in the view. I have tried to do it in the following way:

$data['communities'] = Community::with(['langs' => function($query)
    {
        $query
            ->where('lang_id', '1')
            ->join('communities', 'communities.id', '=', 'lang_communities.community_id')
            ->orderBy('lang_communities.name', 'asc');
    }])->paginate($num);

But this does not sort by name. Any idea?

Hey I know this isn't really related, but I am really desperate for help. Could you please check "https://stackoverflow.com/questions/75013155/laravel-get-related-columns-with-custom-query" for me? Maybe you have a clue. Thank you! — Moemen Hussein, Jan 05 '23 at 02:33

score 3 · Accepted Answer · answered Nov 20 '17 at 19:24

Ok. I have managed to solve it ;)

$data['communities'] = Community::join('lang_communities', 'communities.id', '=', 'lang_communities.community_id')
            ->select('communities.*', 'lang_communities.name')
            ->where('lang_communities.lang_id', '1')
            ->orderBy('lang_communities.name', 'asc')
            ->paginate($num);

Vincent Decaux · Answer 2 · 2017-11-20T17:40:39.320

0

Did you try :

$data['communities'] = Community::with(['langs' => function ($q) {
        $q->orderBy('name', 'asc');
    }])
    ->whereHas('langs', function ($query) {
        $query->where('lang_id', 1);
    })->get();

edited Nov 20 '17 at 17:40

answered Nov 20 '17 at 17:12

Vincent Decaux

9,857
6
56
84

Yes, but this give me the error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lang_communities.lang_id' in 'where clause' (SQL: select count(*) as aggregate from `communities` where `lang_communities`.`lang_id` = 1) – nature Nov 20 '17 at 17:24
Well, I don't have errors but this does not short by name. This is the result of the two query: `select * from `communities` where exists (select * from `lang_communities` where `communities`.`id` = `lang_communities`.`community_id` and `lang_id` = '1')` 'select * from `lang_communities` where `lang_communities`.`community_id` in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40') order by `name` asc` – nature Nov 20 '17 at 17:49
Hmm ok :( In your 1st solution, you can't use relation but you can access fields of the table lang_communities like : `$data['communities']->name`. Maybe the easiest way. – Vincent Decaux Nov 20 '17 at 18:05
Yes, I can. The problem is that I have to implement this in several views and partials and I would have to refactor all the code; but it's fine, if it's not possible, I'll have to take a few hours ;) Thanks anyway. – nature Nov 20 '17 at 18:12

Laravel 5: DB::table() and relationship in Eloquent

2 Answers2

Linked