Query model fields with relationship

Question

I have two models, User and Enumerator. I want to search certain columns in enumerator model and it's relationship in the user model. Here is what i have;

Enumerator

• unique_id

User

• first_name

I want to write a query that'll fetch both unique_id and first_name in the same collection.

Here is what i have;

Enumerator::with(['user' => function($query) {
       $query->select('id', 'first_name', 'last_name', 'email');
}])->get(['first_name', 'unique_id']);

How can i go about it?

Answer 1

if you want to get multiple table columns in the same collection better to use join query here like this

$joinTableName = (new App\User())->getTable();
$fromTableName = (new App\Enumerator())->getTable();
$foreignKey = "enumerators_id"; //user table set foreign key
$localKey = "id";  //enumerators table column local key

$selectColumns = [
    "{$joinTableName}.first_name",
    "{$fromTableName}.unique_id",
];

$a = App\Enumerator::select($selectColumns)
    ->join(
        $joinTableName,
        "{$joinTableName}.{$foreignKey}",
        '=',
        "{$fromTableName}.{$localKey}"
)->get();

dd($a);