My English is not so good, but I'll try to explain the problem.

I have three tables: One called Accounts, one called Accounts_Profiles and another called Accounts_Messages. My goal is to show the last users that the logged in user ($login_user_id) sent a message to.
My tables basically consists of this:

Accounts

id, name, email, nivel, verification

Accounts_Profiles

id, user_id, status

Accounts_Messagens

id, sender (int), receiver (int)

My query is currently like this: SELECT DISTINCT Accounts.id, Accounts.email, Accounts.name, Accounts.username, Accounts.avatar, Accounts.nivel, Accounts.verification FROM Accounts JOIN Accounts_Messages ON Accounts.id = Accounts_Messages.receiver ORDER BY Accounts_Messages.id DESC;

In CodeIgniter/PHP, it looks like this:

/*
    With the $this->db->distinct() I make sure that the users shown will not be repeated - It works!
*/
$this->db->distinct();
$this->db->select(
    array(
        "Accounts.id id",
        "Accounts.email email",
        "Accounts.name name",
        "Accounts.username username",
        "Accounts.avatar avatar",
        "Accounts.nivel nivel",
        "Accounts.verification verification",
        "Accounts_Profiles.status online",
        "(SELECT IF(COUNT(ACM.id) > 0, COUNT(ACM.id), null) FROM Accounts_Messages ACM WHERE ACM.receiver = '$login_user_id' AND ACM.sender = 'Accounts.id' AND ACM.is_read = '0') unread"
    )
);

/*
    Here I exclude the $login_user_id from the list, the admin users and also those who have not verified the account - It works!
*/
$this->db->join(
    "Accounts_Profiles",
    "Accounts_Profiles.user_id = Accounts.id", 
    "left"
);
$this->db->where(
    array(
        "Accounts.id !=" => $login_user_id,
        "Accounts.nivel !=" => 'administrator',
        "Accounts.verification !=" => 'false'
    )
);

/*
    Here, I sort messages by last sent. I check who the logged in user sent the last messages to and list those users - This only works on localhost.
*/
$this->db->join(
    "Accounts_Messages", 
    "Accounts.id = Accounts_Messages.receiver", 
    "left"
);
$this->db->order_by(
    "Accounts_Messages.id", 'DESC'
);

/*
    Returning the result
*/
return $this->db->limit($filters['limit'])->offset($filters['offset'])->get("Accounts")->result();

The problem: This works on localhost, but it doesn't work in production. On localhost I use MySQL. On the production server I use MariaDB. In theory it shouldn't make any difference, since the clauses I used are compatible in both cases.

Am I forgetting to do something or some configuration on the production server?

Thank you!

I tried to add this for conscience sake, but it didn't work:
$this->db->group_by( "Accounts.id" );

Edit

As you can see, in localhost the ordering of users is given by the last message sent to them. That's what I expect in production.

On the production server the ordering is happening by conversation creation date (I don't know how). It's wrong, as the first users I chat with always come last even if I send them a new message.

CodePudding user response：

Solution

I managed to solve the problem by doing the following adjustment in the query:

SELECT Accounts.id, Accounts.email, Accounts.name, Accounts.username, Accounts.avatar, Accounts.nivel, Accounts.verification, Accounts_Messages.id
FROM Accounts 
JOIN (
    SELECT m_to, MAX(dt_updated) as max_updated
    FROM Accounts_Messages
    GROUP BY m_to
) last_message
ON Accounts.id = last_message.m_to
JOIN Accounts_Messages 
ON Accounts.id = Accounts_Messages.m_to AND Accounts_Messages.dt_updated = last_message.max_updated
GROUP BY Accounts.id, Accounts_Messages.id
ORDER BY last_message.max_updated DESC;

It looks like this in CodeIgniter:

<?php
    /*
        With the $this->db->distinct() I make sure that the users shown will not be repeated
    */
    $this->db->distinct();
    $this->db->select(
        array(
            "Accounts.id id",
            "Accounts.email email",
            "Accounts.name name",
            "Accounts.username username",
            "Accounts.avatar avatar",
            "Accounts.nivel nivel",
            "Accounts.verification verification",
            "Accounts_Messages.id acmid",
            "Accounts_Profiles.status online",
            "(SELECT IF(COUNT(ACM.id) > 0, COUNT(ACM.id), null) FROM Accounts_Messages ACM WHERE ACM.m_to = '$login_user_id' AND ACM.m_from = 'Accounts.id' AND ACM.is_read = '0') unread"
        )
    );

    /*
        Here I exclude the $login_user_id from the list, the admin users and also those who have not verified the account
    */
    $this->db->join(
        "Accounts_Profiles",
        "Accounts_Profiles.user_id = Accounts.id", 
        "left"
    );
    $this->db->where(
        array(
            "Accounts.id !=" => $login_user_id,
            "Accounts.nivel !=" => 'administrator',
            "Accounts.verification !=" => 'false'
        )
    );

    /*
        Here, I sort messages by last sent. I check who the logged in user sent the last messages to and list those users
    */
    $this->db->join(
        "(SELECT m_to, MAX(dt_updated) as max_updated FROM Accounts_Messages GROUP BY m_to) last_message", 
        "Accounts.id = last_message.m_to", 
        'inner'
    );
    $this->db->join(
        "Accounts_Messages", 
        "Accounts.id = Accounts_Messages.m_to AND Accounts_Messages.dt_updated = last_message.max_updated", 
        'inner'
    );
    $this->db->group_by(
        "Accounts.id, Accounts_Messages.id"
    );
    $this->db->order_by(
        "last_message.max_updated", 
        "DESC"
    );
?>