MySQL – Select Rows Based on Latest Date with Multiple Joins

group byMySQL

I have this query (SQLFiddle):

SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.rtime,
u.user_name,
s.status_name

FROM company c

LEFT JOIN action a ON a.company_id=c.id
LEFT JOIN user u ON u.id=a.user_id
LEFT JOIN status s ON s.id=a.status_id



WHERE u.user_name='Morgan'

-- WHERE c.name='Fiddle'

GROUP BY c.id

HAVING a.rtime IS NULL OR a.rtime = (
 SELECT max(rtime)
 FROM action a2
 WHERE deleted IS NULL
 AND a2.company_id = c.id
 )

Problem 1

I want to list all the companies, and show the user and status where they last made an action on the company. At the same time show the companies where no actions have been made.

Problem 2

I also need to be able to search for the user by name, thereby selecting all companies were this user had the last activity. The query is made from a form, so I can inject variables.

I am not able to change the database SCHEMA at the moment, but advices for a future migration is much appretiated.

I've tried binding it together with INNER JOIN ( SELECT.. ) t ON but I can't get my head around it.

I've also tried methods from
here, here, and here but I can't get the person with the latest activity right.

MySQL version: 5.5.16.
The company table has about 1mill rows, and the action table is at 70K, growing. Performance is important to me here.

How can this be solved?

Best Answer

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

Related Solutions

Mysql – Will this Database design logic work and what should be used

I really have to encourage you to look at the way DB schemas are handled by existing CRM systems and consider whether (1) you wouldn't be further off customizing one of these systems and (2) you're not biting off more than you can chew.

Most CRM systems I've seen have extensive customization capabilities built into them. Typically, this includes the ability to extend and/or modify the definitions of the built-in entities and the ability to add new entity types and relationships. Many of these CRM systems also have the ability to set up some pretty funky security setups, and some might be able to do something similar to what you're suggesting.

I'd also encourage you to try to pry a business scenario out of this client, as well. What's got him convinced that sales reps are going to share information? If they're working together on a team, then existing CRM systems have this covered already. If not, then I'd be a little suspicious about the premise of information sharing. Understand that sales reps are going to follow the money with respect to their behavior. If they're paid to share information, then it'll happen; otherwise, you're going to have to beat them with a stick to share anything.

Group Activities in Activity Feed by Users and Products

You need to group users by activity and product in the first step, and then group products by the groups of users.
I use ORDER BY with GROUP_CONCAT(), so that the aggregated usernames are concatenated in the same order.

SELECT x.usernames
      ,x.activity
      ,GROUP_CONCAT(p.product_name ORDER BY p.product_name) AS product_names
FROM   products p
JOIN  (
   SELECT a.activity
         ,a.productId
         ,GROUP_CONCAT(u.name ORDER BY u.name) AS usernames
   FROM   activities a
   LEFT   JOIN users u ON u.id = a.userId
   GROUP  BY 1, 2
   ) x ON x.productId = p.id
GROUP  BY 1, 2
ORDER  BY 1, 2;

Returns:

USERNAMES  | ACTIVITY   | PRODUCT_NAMES
-----------+------------+------------------------------
John       | Bought     | The Dark Knight Rises
John,Peter | Bought     | Batman Begins,The Dark Knight

Related Question