从2个不同的表中检索值，并将它们放在Laravel - foreach的视图中

Question

I'm using the framework Laravel.

I have 2 tables (Users and Persons). I want to show person_firstname, person_surname, user_username, user_email, user_created in a table.

Table Users

CREATE TABLE IF NOT EXISTS `festival_aid`.`users` (
  `user_id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_username` VARCHAR(45) NOT NULL,
  `user_email` VARCHAR(45) NOT NULL,
  `user_password` CHAR(32) NOT NULL,
  `user_salt` CHAR(32) NOT NULL,
  `user_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_modified` TIMESTAMP NULL,
  `user_deleted` TIMESTAMP NULL,
  `user_lastlogin` TIMESTAMP NULL,
  `user_locked` TIMESTAMP NULL,
  `user_token` VARCHAR(128) NULL,
  `user_confirmed` TIMESTAMP NULL,
  PRIMARY KEY (`user_id`, `person_id`),
  UNIQUE INDEX `user_email_UNIQUE` (`user_email` ASC),
  INDEX `fk_users_persons1_idx` (`person_id` ASC),
  CONSTRAINT `fk_users_persons1`
    FOREIGN KEY (`person_id`)
    REFERENCES `festival_aid`.`persons` (`person_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Table Persons

CREATE TABLE IF NOT EXISTS `festival_aid`.`persons` (
  `person_id` BIGINT NOT NULL AUTO_INCREMENT,
  `person_firstname` VARCHAR(45) NULL,
  `person_surname` VARCHAR(45) NULL,
  `person_created` TIMESTAMP NOT NULL,
  `person_modified` TIMESTAMP NULL,
  `person_deleted` TIMESTAMP NULL,
  PRIMARY KEY (`person_id`))
ENGINE = InnoDB;

The index action

public function index()
    {
        $person = Person::all();
        $user = User::all();
        //$user = User::where($person_id == $user_id);

        return View::make('users.index')
            ->with('user', $user)
            ->with('person', $person);

        return View::make('users.index');
    }

User migration

Schema::table('users', function(Blueprint $table)
        {
        $table->increments('user_id');
            $table->string('user_email');
            $table->timestamp('user_created');
            $table->timestamp('user_modified');
            $table->timestamp('user_deleted');
            $table->timestamp('user_lastlogin');
            $table->timestamp('user_locked');

            $table->foreign('person_id')
                ->references('id')->on('persons')
                ->onDelete('cascade');
        });

Person migration

public function up()
    {
        Schema::table('persons', function(Blueprint $table)
        {
            $table->increments('person_id');

            $table->string('person_firstname');
            $table->string('person_surname');
        });
    }

Model User

class User extends Eloquent  {

    protected $primaryKey = 'user_id';

    public function persons()
    {
        return $this->hasOne('Person', 'foreign_key');
    }
}

Model Person

class Person extends Eloquent {

    protected $table = 'persons';

    protected $primaryKey = 'person_id';

    public function users()
    {
        return $this->belongsTo('User', 'local_key');
    }

    public $timestamps = false;
}

The Users View

@foreach($user as $user=> $value)
   <tr>
      <td>{{ $value->user_id }}</td>
      <td>{{ $value->person_firstname }}</td>
      <td>{{ $value->person_surname }}</td>
      <td>{{ $value->user_username }}</td>
      <td>{{ $value->user_email }}</td>
      <td>{{ $value->user_created }}</td>
   </tr>
@endforeach

Gives me user_id, user_username, user_email, user_created.

@foreach($person as $person=> $value)
    <tr>
       <td>{{ $value->user_id }}</td>
       <td>{{ $value->person_firstname }}</td>
       <td>{{ $value->person_surname }}</td>
       <td>{{ $value->user_username }}</td>
       <td>{{ $value->user_email }}</td>
       <td>{{ $value->user_created }}</td>
    </tr>
 @endforeach

Gives me user_firstname, user_surname.

I want to combine these two with PHP.

I tried a foreach in a foreach but for each user_username, user_email, user_created it gave me all the person_firstname's, person_surname's from the table.

Does someone know how to do this with the framework laravel?

Answer 1

It looks like you're using Eloquent ORM. You may want to look at it's documentation on defining relationships and querying them.

First, you'll have to adjust your model classes a bit to define this one-to-one relationship correctly, as you defined the relationship the other way around and failed to specify the correct key columns. Your model classes should look like this:

Model class User

class User extends Eloquent  {
    protected $primaryKey = 'user_id';

    public function person() {
        return $this->belongsTo('Person', 'person_id', 'person_id');
    }
}

Model class Person

class Person extends Eloquent {
    protected $table = 'persons';
    protected $primaryKey = 'person_id';
    public $timestamps = false;

    public function user() {
        return $this->hasOne('User', 'person_id', 'person_id');
    }
}

And then you should be able to access properties from the Person like this:

$user = User::find(1);
$user->person()->person_firstname;