Code for sorting seems to fail in Laravel 5.4

[Quibi]

Hi,

I am running a Laravel 5.4 site and I have tried to sort by translations as documentation states. It has failed in my case and has returned an empty collection. No translations found although they really exist (I can retrieve them with all() and get() model methods).

I tried to run something similar to:

Country::join('country_translations as t', 't.country_id', '=', 'countries.id')
    ->where('locale', 'en')
    ->groupBy('countries.id')
    ->orderBy('t.name', 'desc')
    ->with('translations')
    ->get();

as you suggest in readme.

After some tests, my "solution" consists in using whereRaw instead of where.

Country::join('country_translations as t', 't.country_id', '=', 'countries.id')
    ->whereRaw('locale = "en"')
    ->groupBy('countries.id')
    ->orderBy('t.name', 'desc')
    ->with('translations')
    ->get();

Also I have to change 'strict' => true to 'strict' => false in config/database.php in order to avoid that Laravel raises an error.

With these changes, the expected results were returned.

Maybe I am doing something wrong —I am a newbie—, maybe something has changed in Laravel 5.4 (so many things have actually changed without backwards compatibility).

Just for your consideration in case it will be useful. Thanks.

[daviskoko]

Actually I don't use this package, but your query doesn't seem to me like it will return any thing. You're trying to mix query builder with eloquent. Rewrite your query as such using query builder:

$countries = DB::table('countries')
    - >join('country_translations', 'countries.id', '=', 'country_translations.country_id') 
    - >where('country_translations.locale', 'en') 
    - >orderBy('country_translations.name', 'desc')
    - >get();

[unitedworx]

Here is an example code I have working with sorting

$features = Feature::join('feature_translations as translations', function ($join) {
                                $join->on('features.id', '=', 'translations.feature_id')
                                     ->where('translations.locale', '=', 'en');
                            })        
                            ->orderBy('translations.name', 'asc')
                            ->select('features.*') 
                            ->with('translations')
                    ->get();

[unitedworx]

P.S. your initial code does not work since your where clause is bound to your country table intead of your join. Your where raw probably works since it’s right after the join, I wouldn’t keep it, I am surprised it even works :)

[Quibi]

@unitedworx Thanks a lot. Your code works fine. You have nailed it.

@daviskoko I have tried something similar to your proposal (use a query builder instead of the model) and it does not work at all. Maybe your code is right and I have made another mistake. Could be the reason why it does not work the one pointed out by @unitedworx? That is, the where is bound to the country table instead of join?

Thank you so.

My final question is this: since this code comes from the documentation of this package, is there a "bug" in the documentation (at least for Laravel 5.4) or is this simply my mistake?

[daviskoko]

@Quibi Regardless of the laravel version, query builder will work. Using eloquent is largely dependent on the relationships that the programmer set in the model. The reason I used query builder is because am not sure of what the relationship in the model looks like. Query builder will bypass the model relationship & focus on table relationship, also its closer to SQL commands than Eloquent. So if my code does not work, then there is something wrong in the table schema you're using.

[unitedworx]

yeah the code sample in the documentation is buggy :) not related to laravel version number

[Quibi]

@daviskoko I am really a newbie in Laravel and my knowledge about this framework is very little at this point.

I tried to follow your sample in the documentation as close as I can. But, of course, as you pointed out, my schema is probably wrong despite my efforts. I have tried really hard to find where I have made a mistake but I am just a newbie, you know.

Indeed, the only thing I can say for sure is that, in my case —just in my case—, the code proposed by @unitedworx works and I think that maybe it deserves a deep look. But I am not even sure about that.

Thanks.

[dimsav]

yeah the code sample in the documentation is buggy :) not related to laravel version number

@unitedworx Feel free to PR 👍