Not repeating table name in join queries

Hey people,

does anyone know a good way to keep table names really dry and only specifying them in the Eloquent model when making join queries e.g. in a controller?

Is there a way to specify the model class when doing joins? I could not find a solution and the "best" way I can think of is to make the table name public and use it in the join query.

Any suggestion for a clean way would be very much appreciated, thanks!

LaryAI

3 years ago

Level 58

One way to keep table names dry and only specify them in the Eloquent model when making join queries is by using the getTable() method in the model. This method returns the table name associated with the model.

Here's an example of how you can use it in a join query:

$users = User::join('roles', function ($join) {
    $join->on('users.role_id', '=', 'roles.id')
         ->where('roles.name', '=', 'admin');
})
->get();

In this example, the User model is joined with the roles table. Instead of explicitly specifying the table name, we use the getTable() method to get the table name associated with the User model.

$users = User::join(User::getTable(), function ($join) {
    $join->on('users.role_id', '=', 'roles.id')
         ->where('roles.name', '=', 'admin');
})
->get();

By using User::getTable(), we ensure that the table name is only specified in the model and not repeated in the join query.

I hope this helps! Let me know if you have any further questions.

Snapey

3 years ago

Level 122

ha ha Lary, you crack me up ...

its-einkauf

3 years ago

Level 1

getTable is not a static method, which means I have to create objects and then use it, which does not feel very clean to me. I would rather create a static getTableName method, but I don't like that very much either. I thought there may be a more laravelish way.

Snapey

3 years ago

Level 122

@its-einkauf probably too complex to standardise because it depends on the relationship types in each model, and the keys used for the join, and then handle the complexity of duplicate column names impacting where statements.

possibly create named joins in the model?

$admins = User::join(...User::rolejoin())->admins()->get();

User model

public static function roleJoin()
{
    return ['users.role_id', '=','roles.id'];
}

public function scopeAdmins(Builder $q)
{
    return $q->where('roles.name', 'admin');
}

but it all becomes a bit pointless if you only do it once

its-einkauf

3 years ago

Level 1

Thanks for your reply!

Looks good. But what I really wanted to achieve is to have the table name only in the model definition on nowhere else. So in this case the User model knows the table name of the Role Model. That' what I'd like to prevent. I testen it with a static getTableName method on the model now. I'm not 100% satisfied, but it is okay for now.

Please or to participate in this conversation.