Laravel: ORM retrieving data ordered by related table -
i creating simple messenger laravel 4, fuctionality pretty same facebook messenger. have problem making eloquent query list latest messages threads logged user.
consider have following db tables messenger (there users table):
+-----------+ +--------------+ +----------------+ +----------------+ +-----+ |threads | |thread_users | |messages | |messages_status | |users| +-----------+ +--------------+ +----------------+ +----------------+ +-----+ |id | |id | |id | |id | |id | |title | |thread_id | |thread_id | |user_id | |name | |created_at | |user_id | |sender_id (user)| |message_id | +-----+ |deleted_at | |created_at | |content | |deleted_at | +-----------+ |deleted_at | |created_at | +----------------+ +--------------+ +----------------+ db explanation:
when user writes first message creates thread. users added thread listed in thread_users table. message submitted thread stored in messages table, linked messages_status table i'm storing info if user deleted particular message thread account.
laravel models (i have removed softdeletingtrait make clear):
class threads extends \eloquent { protected $table = 'threads'; public function participants() { return $this->hasmany('messengerthreadusers', 'thread_id', 'id'); } public function userparticipation() { return $this->hasone('threadusers', 'thread_id', 'id')->where('user_id', '=', auth::user()->id); } public function messages() { return $this->hasmany('message', 'thread_id', 'id'); } public function lastmessage() { return $this->hasone('message', 'thread_id', 'id')->latest(); } } class threadusers extends \eloquent { protected $table = 'thread_users'; } class message extends \eloquent { protected $table = 'messages'; public function statuses() { return $this->hasmany('messagestatus', 'message_id', 'id'); } public function thread() { return $this->hasone('threads', 'id', 'thread_id'); } } class messagestatus extends \eloquent { protected $table = 'messages_status'; public function message() { return $this->hasone('message', 'id', 'message_id'); } public function thread() { return $this->hasone('threads', 'id', 'thread_id'); } } now want make query return 5 recent messages (ordered message creation date) threads, logged user, message_status.deleted_at null.
so far i've got this:
$this->threads->select(\db::raw('threads.*')) ->with('lastmessage') ->has('userparticipation') ->join('messages', 'messages.thread_id', '=', 'threads.id') ->orderby('messages.created_at', 'desc') ->paginate(5); but keeps returning me dupicate threads. in db have 2 threads:
thread #1 have 2 messages in it
thread #2 have 1 message in it
output below:
0 => array (size=5) 'id' => int 10 'title' => string 'thread 1' (length=8) 'deleted_at' => null 'created_at' => string '2015-07-06 14:20:03' (length=19) 'last_message' => array (size=7) 'id' => int 11 'sender_id' => int 12 'thread_id' => int 10 'content' => string 'msg 1 in thread 1' (length=129) 'created_at' => string '2015-07-07 19:36:52' (length=19) 1 => array (size=5) 'id' => int 10 'title' => string 'thread 1' (length=8) 'deleted_at' => null 'created_at' => string '2015-07-06 14:20:03' (length=19) 'last_message' => array (size=7) 'id' => int 11 'sender_id' => int 12 'thread_id' => int 10 'content' => string 'msg 1 in thread 1' (length=129) 'created_at' => string '2015-07-07 19:36:52' (length=19) 2 => array (size=5) 'id' => int 10 'title' => string 'thread 2' (length=8) 'deleted_at' => null 'created_at' => string '2015-07-06 14:25:13' (length=19) 'last_message' => array (size=7) 'id' => int 11 'sender_id' => int 12 'thread_id' => int 10 'content' => string 'msg 1 in thread 2' (length=129) 'created_at' => string '2015-07-07 19:56:22' (length=19)
Comments
Post a Comment