Ordering an invoice from another table?

Question

I have a table called user_room_vists that stores visits to rooms that each user makes. Here is the structure of this table and how I created it in my own database.

 DROP TABLE IF EXISTS `user_room_visits`; CREATE TABLE `user_room_visits` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `room_id` int(10) unsigned NOT NULL, `entry_timestamp` double NOT NULL, `exit_timestamp` double NOT NULL, `hour` int(11) NOT NULL, `minute` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `entry_timestamp` (`entry_timestamp`), KEY `exit_timestamp` (`exit_timestamp`) ) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1; 

Then I have a table called users , which obviously contains users. I am trying to select (in order of highest) the number of visits the room has made by the user.

This means that I have to map the user_id column in user_room_visits to the id column in the users table, but since it becomes more difficult in a separate table.

I have no examples to show you what I tried because I am stuck, every time I talk about it on google, it has something that I cannot express in terms of my own problem, and the fact I just don’t know what I need to do to achieve this. I also have to do this within Laravel using its ORM Equalent, which makes the query a lot easier, so I need a little more than just MySQL.

Here are the model files

 class UserRoomVisit extends Model { protected $table = 'user_room_visits'; public $timestamps = true; protected $guarded = ['id']; } class User extends Authenticatable { protected $table = 'users'; public $timestamps = true; protected $guarded = ['id']; public function userStats() { return $this->belongsTo(UserStats::class, 'id'); } } 

Answer 1

It seems you do not want to use many-to-many relationships here. So, define the relationship userRoomVisits in the Room model:

 public function userRoomVisits() { return $this->hasMany(UserRoomVisit::class); } 

And use the withCount() method to get the rooms:

 Room::withCount(['userRoomVisits' => function($q) { $q->where('user_id', auth()->id()); }]) ->latest('user_room_visits_count') ->get(); 

Update

Code for another question asked in the comments:

 User::withCount('userRoomVisits')->latest('user_room_visits_count')->take(5)->get(); 

Define the relationship in the User model:

 public function userRoomVisits() { return $this->hasMany(UserRoomVisit::class); }