user1157885 user1157885 - 3 months ago 11
MySQL Question

Laravel Eloquent query get users from another model

I have another table called tableb and it has a user relationship defined through the user_id field.

I want to run a query against tableb where a certain date is within a certain range but then I want to grab the user table associated with that row but I only want it to grab the user if it's not been grabbed yet. I'm trying to do this all in 1 DB query. I have most of it done, but I'm having trouble with the unique part of it.

Here's what I have right now:

$tableB = TableB::select('users.*')
->join('users', 'tableb.user_id', '=', 'users.id')
->where('tableb.start_date', '>', date('Y-m-d'))
->get();


So right now I have 3 entries in tableB from the same user, and ideally I'd like to only get 1 entry for that user.

How would I go about doing this?

Answer

Since you're selecting only users data, just add a groupBy clause in your query.

$tableB = TableB::select('users.*')
    ->join('users', 'tableb.user_id', '=', 'users.id')
    ->where('tableb.start_date', '>', date('Y-m-d'))
    ->groupBy('users.id')
    ->get();
Comments