create a queue of rows via mysql and laravel

Question

Suppose I have a experts table like this :

+-----------+----------+----------+--------+
| expert_id |   name   |  family  | active |
+-----------+----------+----------+--------+
|         1 | ali      | ahadi    |      1 |
|         2 | reza     | hosseini |      1 |
|         3 | hossein  | rezaei   |      0 |
|         4 | mohammad | gholami  |      1 |
+-----------+----------+----------+--------+

And in the other hand there is a questions table like this :

+-------------+-------+-----------+
| question_id | title | expert_id |
+-------------+-------+-----------+
| 1           | A     | 1         |
| 2           | B     | 2         |
| 3           | C     | 4         |
| 4           | D     | 1         |
| 5           | E     | 2         |
| 6           | F     | 4         |
+-------------+-------+-----------+

I'm working on a Question-Answer App. when a user asks a new Question I want to select an expert(of course activated expert, means an expert that has active field equal to 1) that can answer to question .(expert_id field holds the selected Expert ID in the questions table).

But I do not want this to be a random selection. Instead, I want to be sequentially as you can see in the expert_id of questions table.

In fact ,since that daily many questions may be asked I would like divide questions between the experts equally.

I want to do it in laravel But I do not know how I could implement this logic.

Can anyone help me to do that or suggest better ways?

From the looks of this, your problem would need some kind of row number functionality which MySQL does not have inherently (read: really ugly query). Could you handle the assignments from your PHP code? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 16, 2016 at 9:41
It was only a scheme of what I want to implement. I do not want to do all of them via mysql. but I do not know how can I do that via laravel — Ahmad Badpey
– Ahmad Badpey, Commented Nov 16, 2016 at 9:48

Styphon · Accepted Answer · 2016-11-16 09:44:24Z

I would suggest you keep a running total of the number of questions each expert has, that way you can assign it to the person with the lowest number of questions. You could even make this the total number of unanswered questions each expert has, which gets lowered when an expert answers the question so those experts that answer questions more frequently get more questions and those with less time to answer questions, or difficult questions that take a long time to answer, get less questions.

The way to do this would be to add another field onto the experts table, num_questions. When selecting experts you could do something like $expert = Expert::where('active', '=', 1)->orderBy('num_questions')->first(); then just assign the question to that user and increment the num_questions field by one for that user. You'd then just need to decrement that number when the user answers a question (if you want unanswered questions over total questions).

Collectives™ on Stack Overflow

create a queue of rows via mysql and laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related