How to use custom SELECT with JOINs and GROUP BY in Laravel model?

Question

I want to use sophisticated SELECT query with JOINs and GROUP BY in Laravel model.

Сoncretely I want to make a messager in my application. Here is table "messages" with all messages. Now I want to create model called "Dialog". Keep in mind here is no table "dialogs", a dialog is a result of joining and grouping.

Example of query:

SELECT 
    cl.name                               AS client_name,
    COUNT(m.id)                           AS messages_count, 
    MAX(m.created_at)                     AS last_message,
    COUNT(m.id) > SUM(m.viewed_by_client) AS has_new_for_client,    
    COUNT(m.id) > SUM(m.viewed_by_user)   AS has_new_for_user

FROM messages AS m
INNER JOIN clients AS c ON m.client_id = c.id
GROUP BY c.id

Of cource I can use raw SQL queries. But I want to use Eloquent relations later with all its benefits. For example:

$dialog->client->full_name
$dialog->client->order->ordered_items

I had an idea to create a VIEW in database from my query and to use this view as a fake table in the model. But it seems to me not ideal solution.

So, how can I use JOINs and GROUP BY in Eloquent when I do not have a real table for model entities? Or may be some different solutions for my task?

Answer 1

I am trying to detail example about how to get User Model's Accessor in another model with using relationship

Suppose, we have User table & Comment Table...

Now, Suppose I appends User's Profile Full URL in User model using "getProfilePhotoUrlAttribute" Method. when I call User model eloquent then it's appends User Profile Image automatically.

but Now I wants to get that user's profile Full URL in with Comments then we can't access Accessor using Join because with join we can join only out DataBase's Table Columns. If we have profile_photo_path column & doesn't have profile_photo_url named column as we define accessor function name then we can't access using jjoin. in this case we wants to use Relationship method

For example:-

Case :- 1 You wants to Get the user's comments with User details

In this case, User have one or more than one comments So we need to use One TO Many Relation

App/Models/User.php file

/**
 * The accessors to append to the model's array form.
 *
 * @var array
 */
protected $appends = [
    'profile_photo_url',
];

/**
 * Get the URL to the user's profile photo.
 *
 * @return string
 */
public function getProfilePhotoUrlAttribute()
{
    ... here return full profile URL (concat profile_path with storage/public location path)... 
}

/**
 * Get the user's comments with User details.
 * 
 * One To Many relation
 */
public function comments()
{
    return $this->hasMany(Comment::class);
}

Now then, use Model eloquent Query like below

$user = User::with('comments')->where('id', '=', '2')->get();
echo '<pre>';
print_r($user->toarray());

Case :- 2 You wants to Get the user details of the all comments. In this case, we need to use Many TO One Relation

App/Models/Comment.php file

 /**
 * Get the user details of the comments.
 * 
 * One To Many (Inverse) / Belongs To
 */
public function user()
{
    return $this->belongsTo(User::class);
}

then use Model eloquent Query like below

$comments = Comment::where('deal_id', '=', '45')->get();

print_r($comments->toarray());

foreach ($comments as $comment) {
    print_r($comment->user->toarray());
    echo $comment->user->profile_photo_url;
    echo "<br/>";
}

NOTE:- I used Latest version - it is Laravel 8, So Syntax may vary as per your Laravel Version

For More Detail with Output Data check here my answer on another question

& you can check it in Laravel Official Documentation

Answer 2

You can have a database table without an Eloquent model but not the other way around. That said, there's no rule against making more than 1 model per table. Not really standard practice though.

I experimented with making a model that would inherit from another model but the boot method didn't work as expected so I dropped it.

I think you could get all the information you take from that query with accessors in your Client model. Since your query has no where clause, a scope is not really necessary but it could also be done with that.

OPTION 1: Accessors

# App\Client
class Client extends Model
{
    // Standard Eloquent relationship
    public function messages()
    {
        return $this->hasMany(App\Message::class);
    }
    // Accessor $client->client_name
    public function getClientNameAttribute()
    {
        return $this->name;
    }
    // Accessor $client->last_message
    public function getLastMessageAttribute()
    {
        // Load relationship only if it hasn't been loaded yet
        if(!$this->relationshipLoaded('messages'))
            $this->load('messages');
        // use max() method from collection to get the results
        return $this->messages->max('created_at');
    }

    // Accessor $client->has_new_for_client
    public function getHasNewForClientAttribute()
    {
        // Load relationship only if it hasn't been loaded yet
        if(!$this->relationshipLoaded('messages'))
            $this->load('messages');

        return $this->messages->count() > $this->messages->sum('viewed_by_client');
    }

    // Accessor $client->has_new_for_user
    public function getHasNewForUserAttribute()
    {
        // Load relationship only if it hasn't been loaded yet
        if(!$this->relationshipLoaded('messages'))
            $this->load('messages');

        return $this->messages->count() > $this->messages->sum('viewed_by_user');
    }
}

And then you can access all the properties dynamically

$dialog = Client::withCount('messages')->find($id);
$dialog->client_name;
$dialog->messages_count;
$dialog->has_new_for_client;
$dialog->has_new_for_user;
$dialog->last_message;

However if you're converting $dialog to an array or json format, accessors will be lost unless you append them. In the same way, you can hide the attributes you don't want to show.

This can be done globally for the model

protected $appends = ['client_name', 'has_new_for_client', 'has_new_for_user', 'last_message'];
protected $hidden = ['name'];

or locally for the query

$dialog->setHidden(['name']);
$dialog->setAppends(['client_name', 'has_new_for_client', 'has_new_for_user', 'last_message'];

OPTION 2: Query scopes

# App\Client
class Client extends Model
{
    public function scopeDialog($query)
    {
        $query->select('name as client_name')
              ->withCount('messages') // the default name will be messages_count
              ->selectRaw('max(m.created_at) as last_message')
              ->selectRaw('count(m.id) > sum(m.viewed_by_client) as has_new_for_client')
              ->selectRaw('count(m.id) > sum(m.viewed_by_user) as has_new_for_user')
              ->join('messages as m', 'm.client_id', 'clients.id')
              ->groupBy('clients.id');
    }
}

And then just call it like you would any scope Client::dialog()->...

OPTION 3: Just use whatever methods are already available instead of writing more logic

$dialog = Client::with('messages')->find($id);
// client_name
$dialog->name
// messages_count
$dialog->messages->count()
// last_message
$dialog->messages->max('created_at')
// has_new_for_client
($dialog->messages->count('id') > $dialog->messages->count('viewed_by_client'))
// has_new_for_user
($dialog->messages->count('id') > $dialog->messages->count('viewed_by_user'))

Answer 3

Create dialogs table and put 'dialog_id' column into the messages table. Each message has a dialog and a client. Create relationships in each model. So you can access attributes over models as you want. By doing this, this code works;

$dialog->client->full_name
$dialog->client->order->ordered_items