Member Avatar for turt2live

Hello,

What I am making is a block of code where it "balances" the load for support tickets on a website.

I know that my setup may (most likely will) not be the best. I'm looking for working, not a lecture on how to do everything and have it all setup.

I have the following:

ID (Auto)
partOf (The original ID number, used for threads)
rep (The service rep handling it)
-- Everything Else (like subject, from, message, etc) --

I would like my query to simply select everything (I need all fields) where the rep = whoever (it runs a loop of all reps, then figures out how many tickets they have open and from there I can handle it) and where partOf is distinct (or never seen again in that column).

After the query I count the number of rows found and determine the person with the lowest and assign the ticket to them.

I've done a little research, but most of what I read doesn't work, or I don't understand. (I really only know create, select, update, delete/drop/empty, and insert)

If you need a sample table, here you go:

+-------------------+
| ID | partOf | rep |
+-------------------+
| 01 |   01   |  1  |
+-------------------+
| 02 |   01   |  1  |
+-------------------+
| 03 |   02   |  2  |
+-------------------+
| 04 |   03   |  3  |
+-------------------+

mysql_num_rows for rep 1 would be 1, rep 2 would be 1, rep 3 would be 1 as well. (because partOf should only be counted in the query if it is unique)
  • 1 Contributor
  • 1 Reply
  • 190 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by turt2live

Dani AI

Generated

Nice find by — deduplicating by thread ID (partOf) gives one row per thread instead of one per message. Below are a few practical refinements that make counting and fetching thread-level rows more reliable and efficient.

To get counts for every rep in a single query (avoids looping and repeated mysql_num_rows calls), aggregate with COUNT(DISTINCT partOf):

SELECT rep, COUNT(DISTINCT partOf) AS open_threads
FROM tickets
WHERE status = 'open'
GROUP BY rep
ORDER BY open_threads ASC;

When a full ticket row is needed for each thread (for display or to decide which thread to assign), picking a single row per partOf is better done by selecting the representative ID per thread and joining back to the table. Example (latest message per thread for rep = 1):

SELECT t.*
FROM tickets t
JOIN (
  SELECT partOf, MAX(ID) AS last_id
  FROM tickets
  WHERE rep = 1 AND status = 'open'
  GROUP BY partOf
) x ON t.partOf = x.partOf AND t.ID = x.last_id
WHERE t.rep = 1;

Notes and cautions: modern MySQL can enforce ONLY_FULL_GROUP_BY, so avoid relying on non-aggregated columns in GROUP BY. Indexing (for example, an index on (rep, partOf) and on (partOf, ID)) speeds both COUNT(DISTINCT) and the join. For robust load-balancing, a separate threads table with one row per thread and an assigned_rep column is recommended; updates to assignment should be made atomically (transaction / SELECT ... FOR UPDATE or an UPDATE ... LIMIT 1 pattern) to avoid race conditions when multiple workers assign simultaneously.

Member Avatar for turt2live

Nevermind, found a fix.

Guess and check worked :P

Solution:

SELECT DISTINCT partOf, rep FROM tickets
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.