Laravel: Where selection for Eloquent Eager Loading relationship

Question 1

I got two DB tables:

Posts

$table->increments('id');
$table->integer('country_id')->unsigned();
$table->foreign('country_id')->references('id')->on('countries');

Countries

$table->increments('id');
$table->string('name', 70);

I use laravel as back-end. Now I want to implement filtering data for my front-end. So the user can select a country name and laravel should answer the request only with posts that have a country with the specified name.

How could I add this condition to my existing pagination query? I tried this:

$query = app(Post::class)->with('country')->newQuery(); 
// ...
if ($request->exists('country')) {
        $query->where('country.name', $request->country);
}
// ...

... resulting in the following error:

Column not found: 1054 Unknown column 'country.name' in 'where clause' (SQL: select count(*) as aggregate from `posts` where `country`.`name` = Albania)

Question 2

whereHas method accepts parameter as per Laravel code base,

 /**
 * Add a relationship count / exists condition to the query with where clauses.
 *
 * @param  string  $relation
 * @param  Closure|null  $callback
 * @param  string  $operator
 * @param  int     $count
 * @return IlluminateDatabaseEloquentBuilder|static
 */
public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
{
    return $this->has($relation, $operator, $count, 'and', $callback);
}

so Changing the code a little as,

$query = ""    

if ($request->has('country'){
$query = Post::with("country")->whereHas("country",function($q) use($request){
    $q->where("name","=",$request->country);
})->get()
}else{
    $query = Post::with("country")->get();
}

By the way above code can be a little simplified as follow;

$query = ""    

if ($request->has('country'){
  $query = Post::with(["country" => function($q) use($request){
  $q->where("name","=",$request->country);
}])->first()
}else{
  $query = Post::with("country")->get();

}

Question 3

It's mostly raw queries:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');

Try this:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
    ->selectRaw($sql)
    ->groupBy('item_number')
    ->get();

Question 4

You should not eager load directly on the relationship. You could eager load the user always on the GroupTextPost model.

GroupTextPost.php

<?php

namespace AppModels;

use IlluminateDatabaseEloquentModel;

class GroupTextPost extends Model
{
    protected $table = 'group_text_post';

    /**
     * The relations to eager load on every query.
     *
     * @var array
     */
    protected $with = ['user'];

    public function user()
    {
        return $this->belongsTo('AppModelsUser');
    }

    public function group()
    {
        return $this->belongsTo('AppModelsGroup');
    }
}

Or you could use Nested Eager Loading

$group = Group::with(['messages.user'])->find($groupID);
$group->messages[0]->user->firstname

Question 5

My guess is that this allows for eager loading multiple one to many relationships. Say, for instance, we also had a dogs table:

class User extends Eloquent {

    public function cats() {
        return $this->hasMany('Cat');
    }

    public function dogs() {
        return $this->hasMany('Dog');
    }
}

Now we want to eager load them both with the User:

$users = User::with('cats','dogs')->get();

There is no join that would work to combine these into a single query. However, doing a seperate query for each "with" element does work:

select * from `users`
select * from `cats` where `user`.`id` in ('1', '2', 'x')
select * from `dogs` where `user`.`id` in ('1', '2', 'x')

So, while this methodology may produce an extra query in some simple circumstances, it provides the ability to eager load more complex data where the join method will fail.

This is my guess as to why it is this way.

Question 6

This can be done in (at least) 2 ways.

Using pure Eloquent model logic:

class Buy extends Model
{
  public function getTotalPrice() {
    return $this->buyDetails->sum(function($buyDetail) {
      return $buyDetail->quantity * $buyDetail->price;
    });
  }
}

The only issue here is that it needs to fetch all buy details from the database but this is something you need to fetch anyway to display details in the view.

If you wanted to avoid fetching the relation from the database you could build the query manually:

class Buy extends Model
{
  public function getTotalPrice() {
    return $this->buyDetails()->sum(DB::raw('quantity * price'));
  }
}

Laravel: Where selection for Eloquent Eager Loading relationship

Answers

php,laravel,laravel-5,eloquent,eager-loading