hasMany relationship, count/sum/max/min fields

[coryrose1]

Currently only belongsTo and hasOne work out of the box.

The query builder can be modified to support hasMany relationships and return a count, sum, min, or max column/field.

The challenge is building this into the existing query builder.

Here is an example of how the end query might be built:

$query->select('users.id', 'name', 'addresses.city', DB::raw('COUNT(posts.id) as posts_count'))
            ->leftJoin('addresses', 'users.id', '=', 'addresses.user_id')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->groupBy('users.id', 'addresses.city')
            ->paginate(15);

Effectively, the DB::raw call must be built into the select, and the groupBy() clause must be added.

One unfortunate thing I ran into is MySQL is in STRICT mode by default and thus every other "left join" relationship field must be included into the groupBy clause.

We might allow someone to specify these fields like so:

 public $fields = [
        [
            'title' => 'ID',
            'name' => 'id',
            'header_class' => '',
            'cell_class' => '',
            'sortable' => true,
            'searchable' => true,
        ],
        ...
        [
            'title' => 'Post Count',
            'name' => 'posts.count',
            'header_class' => '',
            'cell_class' => '',
            'sortable' => true,
            'searchable' => true,
        ],
    ];

where .count, .sum, .max, .min etc are reserved keywords that the query builder will parse and understand how to build the query.

[kdion4891]

For count you can just call withCount on the model instance. Then in the table you can pass relationshipname_count. You can even add a new withCount method that can be used in component classes...