How to correctly form a query to the database?

Question

MySQL

There are three tables:
users_app

contacts

messages

We get the user id as input to the script. From the database you need to get the contacts of this user, to these contacts from the users_app table, substitute information for each user (up to this point I did it using join),

$this->db->where('id_user', $id_user);
        $this->db->join('users_app', 'users_app.id = contacts.id_contact');
        $data = $this->db->get('contacts');

and then you need to substitute information from the messages table of the last message for each user.
I've been scratching my head for a few days now. Tell me please.
PS At the heart of codeigniter, so this is the syntax of queries to the database.

Answer 1

It's difficult without a dump, but try this request in PMA, instead of $id_user you just need to specify the real id

SELECT
    a.*,
    b.*,
    c.*
FROM
    `contacts` AS a

LEFT JOIN
    `users_app` AS b
ON
    b.id = a.id_contact

LEFT JOIN
    `messages` AS c
ON
    c.`id` = 
        (
        SELECT 
            MAX(d.id) 
        FROM 
            `messages` AS d 
        WHERE 
            (
            d.id_user = a.id_user
            AND
            d.id_from_user = a.id_contact
            )
            OR
            (
            d.id_user = a.id_contact
            AND
            d.id_from_user = a.id_user 
            )
        )
WHERE
    a.id_user = $id_user