CODEWITHSUNDEEP
laravellaravel-5
user1647708
user1647708

Reputation: 457

laravel one-to-many get data without inner join

I am trying to get a feel around the laravel ORM and I have the following models.

I have a: user table with- id, firstname, lastname city table with - id, name usercity table with - user_id, city_id

The usercity table tracks the cities the user has visited.

I added the following in city model:

    public function usercity()
    {
            return $this->hasMany('App\UserCity');
    }

And another function in user model

    public function usercity()
    {
            return $this->hasMany('App\UserCity');
    }

I also added a model for UserCity and added following function there.

    public function city()
    {
            return $this->belongsTo('App\City');
    }
    public function user()
    {
            return $this->belongsTo('App\User');
    }

Now, the goal is to retrieve all the cities a user has visited. I used the following function. 

   $usercities = User::where('id','=',1)->first()->usercity()->get();

This works in the sense that it retrieves the user_id and city_id. What would i need to do to get all the fields in the city table also? Current response:

[[{"user_id":"1","city_id":"1"},{"user_id":"1","city_id":"2"},{"user_id":"1","city_id":"3"},{"user_id":"1","city_id":"4"}]]

I might be able to use inner join but I wanted to see if there was another way to retrieve the data which safely populates the data for me.

Upvotes: 0

Views: 787

Answers (1)

patricus
patricus

Reputation: 62248

What you really have is a many-to-many relationship between users and cities, with the usercity table being the pivot table. Laravel uses the BelongsToMany relationship to implement this. You'll need to make a few changes to get this to work.

In your city model:

public function users() {
    return $this->belongsToMany('App\User', 'usercity');
}

In your user model:

public function cities() {
    return $this->belongsToMany('App\City', 'usercity');
}

You can get rid of the UserCity model. There is usually no reason to need a model for the pivot table.

The usercity table may need to be updated to add an id field as the primary key. I've not tried it without one, however, so it may work as you have it. Also, if you wanted, you could rename the table to city_user to conform to Laravel conventions, and then you wouldn't need to specify the table name in the relationship definitions.

Once your relationships are setup correctly, you can access a user's cities via the cities relationship on the user, and you can access a city's users via the users relationship on the city. For example:

// all of the cities visited by user 1
$user = User::find(1);
$usercities = $user->cities;

// all of the users that have visited city 1
$city = City::find(1);
$cityusers = $city->users;

You can find more information about the relationships in the documentation here.

Upvotes: 0

Related Questions