Hi, I'm working on creating a simple private messaging plugin and I would like to use messages thread, same as in Facebook. Everything seems fine, but I would like to use ActiveRecord for the complicated query:

private function getInboxMessages(){
    //return Message::where('reciever_id', $this->user_id)->get();
    $results = Db::select("
          SELECT  
                m.*
              , u.nick AS sender_nick
              , u2.nick AS reciever_nick
          FROM    `max_simplemsg_messages` AS m
          LEFT JOIN users AS u ON (m.sender_id = u.id)
          LEFT JOIN users AS u2 ON (m.reciever_id = u2.id)

          WHERE   (LEAST(m.sender_id, m.reciever_id), GREATEST(m.sender_id, m.reciever_id), m.created_at)
                  IN
                  (
                      SELECT  LEAST(mi.sender_id, mi.reciever_id) x, 
                              GREATEST(mi.sender_id, mi.reciever_id) y,
                              MAX(mi.created_at) max_date
                      FROM    max_simplemsg_messages AS mi
                      GROUP   BY x, y
                  )
                  AND ? IN (m.sender_id, m.reciever_id)", [$this->user_id]);
    //var_dump($results);
    return $results;
}

Whats your model for user and max_simplemsg_messages? what is the relation between them?

I'm using RainLab.User plugin, so it's their model.

Message model is very simple with relation:

Schema::create('max_simplemsg_messages', function($table)
    {

        $table->engine = 'InnoDB';
        $table->increments('id');
        $table->integer('parent_id')->unsigned()->nullable()->index();

        $table->integer('sender_id')->unsigned()->nullable()->index();
        $table->integer('reciever_id')->unsigned()->nullable()->index();
        $table->string('rewrite_id')->nullable()->index();

        $table->text('msg')->nullable();

        $table->boolean('is_read')->default(false)->index();
        $table->boolean('is_hidden')->default(false)->index();

        $table->boolean('is_del_by_sender')->default(false)->index();
        $table->boolean('is_del_by_reciever')->default(false)->index();

        $table->timestamps();

    });

public $belongsTo = [
    'sender' => ['Rainlab\User\Models\User'],
    'reciever' => ['Rainlab\User\Models\User'],
];

By the way I'm using a rewrite_id key to get all rewrites (conversation) between 2 users, it's a nice trick to get DB perform faster, but model Message contains:

public function beforeCreate()
{
    if ($this->sender_id > $this->reciever_id){
        $rewriteHash = $this->reciever_id."-".$this->sender_id;
    }else{
        $rewriteHash = $this->sender_id."-".$this->reciever_id;
    }
    $this->rewrite_id = $rewriteHash;
}

Maybe this could help in grouping conversation thread as well...

Last updated 8 years ago

hey Max. How is your plugin work going? I'm willing to help/collaborate since I've met the same task to create personal messages for a website. How can I reach you?

slowpokefarm said:

hey Max. How is your plugin work going? I'm willing to help/collaborate since I've met the same task to create personal messages for a website. How can I reach you?

Hey, you can email me: my_nick_name_here(at)most common email provider from google (dot) com

Hey #maxDubovsky I'm also working on similar messaging for schools. let me know about ur work.

iocare said:

Hey #maxDubovsky I'm also working on similar messaging for schools. let me know about ur work.

I made it long time back, just did not publish it as it is a bit tricky and not perfect. It will fail if there will be 1 gb table in db, so it require manual tuning. The idea is here in this post. You will need one component for listing private messages, maybe one for posting new one's. One controller with listing. Model is almost there.

It'd be awesome if either of you guys could publish your plugin, as I'm sure a ton of people (myself included) would love to use it :)

I'm really sorry Kane, I just have no time to polish and publish it now, maybe later this year. But here I posted the full idea and it's just a matter of 1-2 days to put it together.

Here is another thought: You may create several views from a table messages like: View: 1 month messages View2: 3 months messages View3: 1 Year messages Table messages: All messages

Get all fresh conversations from a model (table: view 1 month messages) for current chat messages and keep the rest in a history (two other views)...

@maxDubrovsky I tried to reach you, but since you didn't respond I made my own messaging plugin, which has many conceptual differences from yours, as I can see. Still I'd be glad to hear from you anyway.

Hi slowpokefarm, Cool, I guess many people will be interested in using that. I'm very buisy these days and just cannot afford to spend any time. I'm very sorry for not beeing contacting you. Take care, All the best, Max

Hi @slowpokefarm , it will be very nice if you could share your plugin on github, so that we all could learn from it. Also community contribution could make it even better. I'm ready contribute. The Only thing everyone request to publish is because that reduces a lot of efforts and others can concentrate on further improvements.

hey there guys, mine is very project specific at the moment, I guess I shall take time and write one reusable from sctratch, maybe as a weekend project later. But I see no problem releasing it to github right after.