CODEWITHSUNDEEP
mysqllaravellaravel-5
Denis Sheremet
Denis Sheremet

Reputation: 2583

Create an subquery alias to use it in Laravel Query Builder

I want to create something like an alias to SQL subquery so I can use it like a plain SQL field. For example, I've got 3 tables: user, user_comments and user_comment_answers. If I want to get count of all answers to comments of current user, I'll do something like this:

SELECT
  COUNT(*) as comment_answers_count
  FROM user_comment_answers
  WHERE user_comment_id IN (SELECT id FROM user_comments WHERE user_id = :id)

Now I want to make Laravel treat this as a plain field of user table, so I can simply do stuff like this:

echo Auth::user()->comment_answer_count

or like this

$popularUsers = User::where('comment_answer_count', '>=', 100)

How can I achieve this in Laravel 5.4?

Upvotes: 3

Views: 1080

Answers (2)

Denis Sheremet
Denis Sheremet

Reputation: 2583

Ended up with an SQL View creation like this:

CREATE VIEW user_with_comments AS
SELECT user.*, (
  SELECT
    COUNT(*)
    FROM user_comment_answers
    WHERE user_comment_id IN (SELECT id FROM user_comments WHERE user_id = user.id)
) as comment_answers_count
FROM user

I guess this is even possible to overload Eloquent methods to read from one table and write to another one, as this particular SQL View is read-only, but in my case read-only access is enough, so I've just set up another model UserWithComments and finally can use it like

$popularUsers = UserWithComments::where('comment_answer_count', '>=', 100)

Upvotes: 0

EddyTheDove
EddyTheDove

Reputation: 13259

Try 

$users = Auth::withCount('comments')->get();

Now all users have a comments_count property

foreach($users as $user) {
    echo $user->comments_count;
}

Assuming you defined the relationship in the User and Comment models.

Update

According to your first comment, what you want is an accessor

public function getCommentAnswerCountAttribute($value) {
     //define your query here 
}

More info here: https://laravel.com/docs/5.4/eloquent-mutators#defining-an-accessor

Hope this gives you a hint in finding your solution

Upvotes: 2

Related Questions