Simple SQL to Eloquent Query (Laravel)

Question

I have two tables: users (Users) and groups (Groups).

    Users
    -----------------
    id | username | group
     1 | Bob      | 2

    Groups
    -----------------
    id | name
     1 | firstgroup
     2 | secondgroup

I would like to display: users.ID, users.username, group.name (1, Bob, secondgroup) An SQL statement like so would work:

SELECT Users.id, Users.username, Groups.name
FROM Users
INNER JOIN 
Groups ON Groups.id = Users.group

However, I'm struggling to write this in Eloquent, since there is no "FROM". At the moment I'm going for something along the lines of the below, using JOINS (http://laravel.com/docs/queries#joins)

$users = Users::select('id','username', 'Groups.name')->joins('Groups.id', '=', 'id')->get();

Now this isn't working - I think the joins has to come before the select but I just can't work it out :(

Nils Werner · Accepted Answer · 2013-06-21 18:00:42Z

1

I think you're confusing a few things here...

You're mixing Eloquent with the lower-level DB::table('foo')->select() syntax. When you want to use Eloquent I suggest you take a look at the docs about relationships in Eloquent.

You should define your models like so:

class User extends Eloquent {
    public function group()
    {
        return $this->belongsTo('Group', 'group');
        // second parameter is necessary because you didnt
        // name the column "group_id" but simply "group"
    }
}

class Group extends Eloquent {
    public function users()
    {
        return $this->hasMany('User', 'group');
    }
}

This sets up all the joins you might be needing later. You can then simply use User::with('group')->all(); and have the query built and run for you.

edited Jun 21, 2013 at 18:00

answered Jun 21, 2013 at 15:32

Nils Werner

37.2k7 gold badges85 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Alias Over a year ago

Are you sure the group() function is correct? I seem to be getting the data back, but if my user ID is 1, it gives me the data back for the group with the ID of one as well!

Nils Werner Over a year ago

According to your question, user 1 is in group 1.

Alias Over a year ago

Ah no sorry, the user can be in any group (changed the table to reflect what the actual scenario is). So I'd want to grab all of my users, and then say what group they are in. Currently, it seems to be matching my user ID, to the group ID, rather than my users 'group' to the group ID.

Nils Werner Over a year ago

in that case you should use all() instead of find(id). I've updated my answer. What does the solution currently give you? Always assigned to the group with the same ID as the user?

Nils Werner Over a year ago

You could try removing the custom key 'group' in the belongsTo() declaration, I am not sure if it has to be there.

|

Calixto · Accepted Answer · 2019-10-04 18:32:41Z

Database: Query Builder(DB) is not a Eloquent(ORM):

Database query builder you have to inform the table names and the fields, like it says on in your related link of laravel docs: "...provides a convenient, fluent interface to creating and running database queries." like these query below:

$users = DB::table('users')
        ->join('contacts', 'users.id', '=', 'contacts.user_id')
        ->join('orders', 'users.id', '=', 'orders.user_id')
        ->select('users.*', 'contacts.phone', 'orders.price')
        ->get();

Eloquent is a ORM - Object related Mapping, it means that your class User is related to the table users (look at you files Migrations) and this class extends the Model Class, thus you can access the methods like these bellow:

class User extends Models
{
    public static function usersWithGroups(){
        return User::select('id', 'name', 'email')->with('groups')->get();
    }
}

Observe that method is into the class User, so you can access that in a static way "User::", using Eloquent you'll have many hidden static methods that will improve you time codding, because you are inheriting de Model methods, to more details visit the Eloquent Docs at: Eloquent Docs

Collectives™ on Stack Overflow

Simple SQL to Eloquent Query (Laravel)

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related