Using multiple joins in a Laravel statement overrides values

Question

I am trying to create a Laravel statement which should return the correct values per user for amount of answers and amount of questions like so:

$users = User::join('user_answers', 'users.id', '=', 'users_answers.user_id')
        ->join('user_questions', 'users.id', '=', 'user_questions.user_id')
        ->select(
            'users.id',
            'users.username',
            DB::raw('COUNT(user_answers.user_id) AS answers'),
            DB::raw('COUNT(user_questions.user_id) AS questions')
        )
        ->groupBy('users.id');

NOTE: The groupBy is required for it to work because it is used in DataTables, otherwise it will show only one row.

When I attempted to:

dd($users->first());

What I got is this:

As you can see, the values are for some reason the same for questions and answers, and are incorrect. I imagine there is some kind of overlapping with the joins.

For instance, if I remove one of the joins, and leave one of the columns only, the numbers show correctly, like so:

I am honestly quite lost here, would appreciate some help.

Thank you!

The counts are multiplied (1412 * 5 = 7060), because you join unrelated questions and answers. — Paul Spiegel, Mar 16 '19 at 14:56
I think you should not use `DB::raw()` in select you can append where clause for that. — Vipertecpro, Mar 16 '19 at 14:56
Instead of join, db::raw() & select, I would utilize collection for this approach — senty, Mar 16 '19 at 14:57
@senty Fetching 1412 answers into a collection just to get the count? This will speed up global warming ;-) — Paul Spiegel, Mar 16 '19 at 15:00
Hi, I cannot use collections instead of select() and raw DB since then it would not be sortable (using DataTables). @PaulSpiegel could you elaborate please and how I could avoid this? — Michael Naidis, Mar 16 '19 at 15:00
if you're using datatables and you can separately write query for that particular column just `->editColumn('questionCounts',User $user){ //return counts}` — Vipertecpro, Mar 16 '19 at 15:03
@PaulSpiegel, well, no, just the count. `['id', username', 'answers', 'questions']`. `User::get()->each(function($colEl) { // Do count() queries here and push to collection }` — senty, Mar 16 '19 at 15:04
Fast and "dirty" fix would be `COUNT(DISTINCT user_answers.id) AS answers` — Paul Spiegel, Mar 16 '19 at 15:09
Is this what you mean @ViperTecPro? ..editColumn('answers', function ($query) { return $query->join('user_answers', 'users.id', '=', 'user_answers.user_id')->groupBy('users.id')->count(); }) — Michael Naidis, Mar 16 '19 at 15:11
@PaulSpiegel That actually worked, thanks! Could you explain why this would be a "dirty" fix though? :D — Michael Naidis, Mar 16 '19 at 15:13
@MichaelNaidis "dirty" because you let the DB do more work than needed. The propper SQL way would be to use correlated subqueries in SELECT instead of using JOINs. — Paul Spiegel, Mar 16 '19 at 15:20

score 3 · Accepted Answer · answered Mar 16 '19 at 15:29

I didn't use Eloquent for a long time, so I don't know if there is an "eloquent" way to do this with a single query. But in SQL I would use correlated subqueries in the SELECT clause:

$users = User::select(
            'users.id',
            'users.username',
            DB::raw('(
                SELECT COUNT(*)
                FROM user_answers
                WHERE users_answers.user_id = users.id
            ) AS answers'),
            DB::raw('(
                SELECT COUNT(*)
                FROM user_questions
                WHERE user_questions.user_id = users.id
            ) AS questions'),
        );

I'll look into an option if it is possible to construct this via Eloquent. Thank you! — Michael Naidis, Mar 16 '19 at 15:43

CommandZ · Answer 2 · 2019-03-16T16:12:20.040

3

I would use Eloquent's relational methods in order to construct this. There's even a withCount() method that you can use to construct your query.

The relational methods would be something like:

public function answers()
{
    return $this->hasMany(\App\Answer::class);
}

One thing to keep in mind with pivot table naming is that Laravel expects the tables to be named alphabetically. So for a many-to-many relationship it would expect the pivot to be named answer_user. However, you can define a custom pivot table name in the Model's relational method.

Once you set up the relational methods in your model, then you should be able to construct a query similar to the below:

User::withCount(['answers', 'questions'])->get();

Once you get that working you can constrain with select statements.

edited Mar 16 '19 at 16:12

answered Mar 16 '19 at 15:36

CommandZ

3,333
1
23
28

That's what I meant with "eloquent way" in my answer. It will probably generate the exactly same query using just a one-liner. – Paul Spiegel Mar 16 '19 at 15:43
I liked where you were going with yours... but ORMs :) – CommandZ Mar 16 '19 at 15:47
2

Thank you! I did not know about withCount(). – Michael Naidis Mar 16 '19 at 16:11
1

Pretty neat solution here! Thanks for the `withCount()` – senty Mar 16 '19 at 17:43

Using multiple joins in a Laravel statement overrides values

2 Answers2