I have 3 tables:
tb_user
tb_addquestion
tb_answer
The following queries return count of questions per user (query 1
) and answers per user (query 2
). I need to combine the results to a single output.
How can I achieve this with the help of a single query?
Count of questions per user (query1)
SELECT tb_addquestion.userid,
COUNT(*) AS count
FROM tb_addquestion
LEFT JOIN tb_user
ON tb_user.userid = tb_addquestion.userid
GROUP BY tb_addquestion.userid
HAVING count > 0 AND
count < 15
Answers per user (query2)
SELECT tb_answer.userid,
COUNT(*) AS count
FROM tb_answer
LEFT JOIN tb_user
ON tb_user.userid = tb_answer.userid
GROUP BY tb_answer.userid
HAVING count > 0 AND
count < 15
My Answer:
(*) > 0_Count of answers per user (query2)_SELECT tb_answer.userid,_ COUNT(*) AS count_FROM tb_answer_LEFT JOIN tb_user_ ON tb_user.userid = tb_answer.userid_GROUP BY tb_answer.userid_HAVING count > 0__To combine these two queries in Laravel, you can use the `DB` facade to run raw SQL queries and then merge the results using the `merge` method. Here's an example of how you can achieve this:_
php$questions = DB::select("SELECT tb_addquestion.userid, COUNT(*) AS count FROM tb_addquestion LEFT JOIN tb_user ON tb_user.userid = tb_addquestion.userid GROUP BY tb_addquestion.userid HAVING count > 0");$answers = DB::select("SELECT tb_answer.userid, COUNT(*) AS count FROM tb_answer LEFT JOIN tb_user ON tb_user.userid = tb_answer.userid GROUP BY tb_answer.userid HAVING count > 0");$combined = collect($questions)->merge($answers);
__In this example, we first run the two queries using the `DB::select` method to fetch the count of questions and answers per user. Then, we use the `merge` method of the `collect` helper to combine the results into a single collection. You can then iterate over the `$combined` collection to access the combined results.
Rate this post
3 of 5 based on 4689 votesComments