php - Laravel message system wuery all tables -

i'm creating message system in laravel4 , i'm confused how retrive data mysql in way.

for noy have 3 tables in db:

conversations:     id                int        a_i primary     subject           varchar    128     created_at        datetime     updated_at        datetime  conversations_messages:     id                int        a_i primary     conversation_id   int     user_id           int     message           text     created_at        datetime     updated_at        datetime  conversations_members:     conversation_id   int     user_id           int     last_view         int     deleted           int     created_at        datetime     updated_at        datetime 

in models:

conversation.php

<?php class conversation extends eloquent { protected $table = 'conversations';  public function messages(){     return $this->hasmany('conversationmessage', 'conversation_id'); } public function members(){     return $this->hasmany('conversationmember','conversation_id'); }    } ?> 

conversationmessage.php

<?php  class conversationmessage extends eloquent {     protected $table = 'conversations_messages';  public function conversation(){     return $this->belongsto('conversation','conversation_id'); } public function sender(){     return $this->belongsto('user','user_id'); } }  ?> 

conversationmember.php

<?php  class conversationmembers extends eloquent { protected $table = 'conversations_members';  public function conversation(){     return $this->belongsto('conversation','conversation_id'); } public function member(){     return $this->belongsto('user','user_id'); } }  ?> 

so in controller need pass conversation variable view can loop thorugh , display messages belongs logged in user.

how can query this:

select "conversations"."id", "conversations"."subject", max("conversations_messages"."created_at") "last_reply" "conversations" left join "conversations_messages" on "conversation"."id" = "conversations_messages"."conversation_id" inner join "conversations_members" on "conversations"."id" = "conversations_members"."conversation_id" "conversations_members" = {auth::user()->id} , "conversations_members"."deleted" = 0 gropu "conversations"."id" order "last_reply" desc 

how in world should in controller? or if there better way please help.

return view::make('messages.inbox')->with('conversation', ??????); 

for trying query builder don't know how.

$con    = db::table('conversations')             ->select('id','subject')             ->max('conversations_messages.created_at')->as('last_reply')             ->from('conversations')             ->leftjoin('conversations_messages',function($leftjoin){                 $leftjoin->on('conversations.id','=','conversations_messages.conversation_id');             })             ->join('conversations_members', function($join){                 $join->on('conversations.id','=','conversations_members.conversation_id');             })             ->where('conversations_members','=',auth::user()->id)             ->where('conversations_members.deleted','=',0)             ->groupby('cpnversation.id')             ->orderby('last_reply','dsc'); 

i pulled code supplied because looks need work out issues database structure , sql queries before move forward.

your sql query excluding deleted conversation members, not conversation messages. if want able exclude deleted messages, should have field on conversation messages table denotes deleted message.

also, think can without conversation member table entirely. store user id on message, know taking part in conversations. table unnecessary here.